Home » Server Options » Spatial » Create JSON in new column by using sdo2geojson3d (Oracle 11g Locator)
Create JSON in new column by using sdo2geojson3d [message #654425] Tue, 02 August 2016 10:47 Go to next message
moehre
Messages: 43
Registered: June 2016
Member
Hi,

I have 3D-data in my database and based on performance I will convert the SDO_GEOMETRY into json.
I am using the function provided by Barabara Boehmer ("SDO2GEOJSON3D") to calculate the json.
Next I created a new column with datatype VARCHAR2().

UPDATE building a 
Set c.json = (SELECT sdo2geojson3d(c.Geometry)

              FROM   building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c
              WHERE a.id = b.BUILDING_ID
              AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID
              AND c.GEOMETRY IS NOT NULL);

My problem is that there are too many values. It will return more than one row per a.id

Inside my database I have buildings and each building has some polygons.


My SURFACE_GEOMETRY table looks like this:

ID--------------------------GEOMETRY
27----------------------[MDSYS.SDO_GEOMETRY]
28----------------------[MDSYS.SDO_GEOMETRY]

I need to add a column with the converted json values based on GEOMETRY:

ID-----------------------------GEOMETRY---------------------------------------------------------------------JSON
27--------------------------[MDSYS.SDO_GEOMETRY]-------------------------{"type":"Polygon","coordinates": ,[3481984,51,5384091,11,626,54][3482009,33,5384095,9,626,46],[3482007,21,5384106,12,622,34],[3481982,51,5384100,92,622,59][3481982,51 ,5384100,92,622,59],[3481984,51,5384091,11,626,54][3482009,33,5384095,9,626,46],[3482007,21,5384106,12,622,34],[3481982,51,5384100,92 ,622,59]}
28--------------------------[MDSYS.SDO_GEOMETRY]------------------------{"type":"Polygon","coordinates": ,[3481984,51,5384091,11,626,54],[3482009,33,5384095,9,626,46],[3482007,21,5384106,12,622,34][3481982,51,5384100,92,622,59][3481982,51 ,5384100,92,622,59],[3481984,51,5384091,11,626,54],[3482009,33,5384095,9,626,46],[3482007,21,5384106,12,622,34][3481982,51,5384100,92 ,622,59]}

Is there an easy way to iterate through column Geometry till the end and add inside JSON column the converted value?
Re: Create JSON in new column by using sdo2geojson3d [message #654430 is a reply to message #654425] Tue, 02 August 2016 18:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
This is another case where it would be easier with 12c spatial instead of 11g locator. I have provided a demonstration of both below, so that you can compare and see that you get the same results. The json column is too long to display properly in one line unless I make it very long and has no spaces, so it is wrapped around in the middle of numbers in the display below.

-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT id, json FROM building
  2  /

        ID JSON
---------- ---------------------------------------------------------------------
         1

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT building_id, lod2_multi_surface_id FROM thematic_surface
  2  /

BUILDING_ID LOD2_MULTI_SURFACE_ID
----------- ---------------------
          1                    27
          1                    28

2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT root_id, geometry FROM surface_geometry
  2  /

   ROOT_ID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        27
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482967.35, 5376954.8, 521.988, 3482965.61, 5376958.37, 521.988, 3482966.11, 5376958.61, 521.988, 3482963.9, 5376963
.24, 521.988, 3482968.12, 5376965.28, 521.988, 3482973.5, 5376954.25, 521.988, 3482970.35, 5376952.78, 521.988, 3482967.35, 5376954.8, 521.988))

        28
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482973.73, 5376953.75, 521.847, 3482973.5, 5376954.25, 521.847, 3482968.12, 5376965.28, 521.847, 3482973.01, 537696
7.63, 521.847, 3482975.48, 5376962.49, 521.847, 3482976.79, 5376963.12, 521.847, 3482979.9, 5376956.67, 521.847, 3482977.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 3482975, 5376953.32,
521.847, 3482973.73, 5376953.75, 521.847))


2 rows selected.

-- Oracle 12c spatial update and results:
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.json =
  3  	    (SELECT sdo2geojson3d (SDO_AGGR_UNION (SDOAGGRTYPE (c.geometry, 0.005)))
  4  	     FROM   thematic_surface b, surface_geometry c
  5  	     WHERE  a.id = b.building_id
  6  	     AND    b.lod2_multi_surface_id = c.root_id
  7  	     AND    c.geometry IS NOT NULL)
  8  /

1 row updated.

SCOTT@orcl_12.1.0.2.0> SELECT id, json FROM building
  2  /

        ID JSON
---------- ---------------------------------------------------------------------
         1 {"type":"Polygon","coordinates":[[[3482963.9,5376963.24,521.99],[3482
           966.11,5376958.61,521.99],[3482965.61,5376958.37,521.99],[3482967.35,
           5376954.8,521.99],[3482970.35,5376952.78,521.99],[3482973.5,5376954.2
           5,521.85],[3482973.73,5376953.75,521.85],[3482975,5376953.32,521.85],
           [3482976.84,5376954.15,521.85],[3482977.21,5376955.37,521.85],[348297
           9.9,5376956.67,521.85],[3482976.79,5376963.12,521.85],[3482975.48,537
           6962.49,521.85],[3482973.01,5376967.63,521.85],[3482968.12,5376965.28
           ,521.85],[3482963.9,5376963.24,521.99]]]}


1 row selected.

-- rollback between demonstration of updates:
SCOTT@orcl_12.1.0.2.0> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_12.1.0.2.0> SELECT id, json FROM building
  2  /

        ID JSON
---------- ---------------------------------------------------------------------
         1

1 row selected.

-- update that should work with Oracle 11g locator and results:
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.json =
  3  	    (SELECT REPLACE
  4  		      (LISTAGG (sdo2geojson3d (c.geometry), '') WITHIN GROUP (ORDER BY c.root_id),
  5  		       ']]}{"type":"Polygon","coordinates":[[',
  6  		       ',')
  7  	     FROM   thematic_surface b, surface_geometry c
  8  	     WHERE  a.id = b.building_id
  9  	     AND    b.lod2_multi_surface_id = c.root_id
 10  	     AND    c.geometry IS NOT NULL)
 11  /

1 row updated.

SCOTT@orcl_12.1.0.2.0> SELECT id, json FROM building
  2  /

        ID JSON
---------- ---------------------------------------------------------------------
         1 {"type":"Polygon","coordinates":[[[3482967.35,5376954.8,521.99],[3482
           965.61,5376958.37,521.99],[3482966.11,5376958.61,521.99],[3482963.9,5
           376963.24,521.99],[3482968.12,5376965.28,521.99],[3482973.5,5376954.2
           5,521.99],[3482970.35,5376952.78,521.99],[3482967.35,5376954.8,521.99
           ],[3482973.73,5376953.75,521.85],[3482973.5,5376954.25,521.85],[34829
           68.12,5376965.28,521.85],[3482973.01,5376967.63,521.85],[3482975.48,5
           376962.49,521.85],[3482976.79,5376963.12,521.85],[3482979.9,5376956.6
           7,521.85],[3482977.21,5376955.37,521.85],[3482976.84,5376954.15,521.8
           5],[3482975,5376953.32,521.85],[3482973.73,5376953.75,521.85]]]}


1 row selected.
Re: Create JSON in new column by using sdo2geojson3d [message #654462 is a reply to message #654430] Wed, 03 August 2016 04:41 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Ok but now it is written value too large for column json.

If I increase the column size VARCHAR (300) to VARCHAR (4000) another error occurs: 01489. 00000 - "result of string concatenation is too long"

How can I solve this problem?
Re: Create JSON in new column by using sdo2geojson3d [message #654468 is a reply to message #654462] Wed, 03 August 2016 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use a CLOB.

Re: Create JSON in new column by using sdo2geojson3d [message #654470 is a reply to message #654468] Wed, 03 August 2016 05:34 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Ok I did update my type for the json column to CLOB.
But I get another error:

22835. 00000 - "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause: An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
the LOB size was bigger than the buffer limit for CHAR and RAW
types.
Note that widths are reported in characters if character length
semantics are in effect for the column, otherwise widths are
reported in bytes.
*Action: Do one of the following
1. Make the LOB smaller before performing the conversion,
for example, by using SUBSTR on CLOB
2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

[Updated on: Wed, 03 August 2016 05:44]

Report message to a moderator

Re: Create JSON in new column by using sdo2geojson3d [message #654484 is a reply to message #654470] Wed, 03 August 2016 14:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please try the following instead and see if it works for you.

-- code:
CREATE OR REPLACE FUNCTION concat_json
  (p_id IN building.id%TYPE)
  RETURN   CLOB
AS
  v_count  NUMBER := 1;
  v_json   CLOB;
BEGIN
  FOR r IN
    (SELECT sdo2geojson3d (c.geometry) json
     FROM   thematic_surface b, surface_geometry c
     WHERE  p_id = b.building_id
     AND    b.lod2_multi_surface_id = c.root_id
     AND    c.geometry IS NOT NULL
     ORDER  BY c.root_id)
  LOOP
    IF v_count = 1 THEN
      v_json := SUBSTR (r.json, 1, LENGTH (r.json) - 3);
      v_count := v_count +1;
    ELSE
      DBMS_LOB.WRITEAPPEND (v_json, 1, ','); 
      DBMS_LOB.WRITEAPPEND 
        (v_json, 
         LENGTH (r.json) - 37, 
         SUBSTR (r.json, 35, LENGTH (r.json) - 37));
    END IF;
  END LOOP;
  DBMS_LOB.WRITEAPPEND (v_json, 3, ']]}');
  RETURN v_json;
END concat_json;
/
SHOW ERRORS
UPDATE building a
SET    a.json = concat_json (a.id)
/
SELECT id, json FROM building
/

-- execution of above code:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION concat_json
  2    (p_id IN building.id%TYPE)
  3    RETURN	CLOB
  4  AS
  5    v_count	NUMBER := 1;
  6    v_json	CLOB;
  7  BEGIN
  8    FOR r IN
  9  	 (SELECT sdo2geojson3d (c.geometry) json
 10  	  FROM	 thematic_surface b, surface_geometry c
 11  	  WHERE  p_id = b.building_id
 12  	  AND	 b.lod2_multi_surface_id = c.root_id
 13  	  AND	 c.geometry IS NOT NULL
 14  	  ORDER  BY c.root_id)
 15    LOOP
 16  	 IF v_count = 1 THEN
 17  	   v_json := SUBSTR (r.json, 1, LENGTH (r.json) - 3);
 18  	   v_count := v_count +1;
 19  	 ELSE
 20  	   DBMS_LOB.WRITEAPPEND (v_json, 1, ',');
 21  	   DBMS_LOB.WRITEAPPEND
 22  	     (v_json,
 23  	      LENGTH (r.json) - 37,
 24  	      SUBSTR (r.json, 35, LENGTH (r.json) - 37));
 25  	 END IF;
 26    END LOOP;
 27    DBMS_LOB.WRITEAPPEND (v_json, 3, ']]}');
 28    RETURN v_json;
 29  END concat_json;
 30  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> UPDATE building a
  2  SET    a.json = concat_json (a.id)
  3  /

1 row updated.

SCOTT@orcl_12.1.0.2.0> SELECT id, json FROM building
  2  /

        ID JSON
---------- ---------------------------------------------------------------------
         1 {"type":"Polygon","coordinates":[[[3482967.35,5376954.8,521.99],[3482
           965.61,5376958.37,521.99],[3482966.11,5376958.61,521.99],[3482963.9,5
           376963.24,521.99],[3482968.12,5376965.28,521.99],[3482973.5,5376954.2
           5,521.99],[3482970.35,5376952.78,521.99],[3482967.35,5376954.8,521.99
           ],[3482973.73,5376953.75,521.85],[3482973.5,5376954.25,521.85],[34829
           68.12,5376965.28,521.85],[3482973.01,5376967.63,521.85],[3482975.48,5
           376962.49,521.85],[3482976.79,5376963.12,521.85],[3482979.9,5376956.6
           7,521.85],[3482977.21,5376955.37,521.85],[3482976.84,5376954.15,521.8
           5],[3482975,5376953.32,521.85],[3482973.73,5376953.75,521.85]]]}


1 row selected.
Re: Create JSON in new column by using sdo2geojson3d [message #654634 is a reply to message #654484] Mon, 08 August 2016 03:36 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
ok if I execute this query:
UPDATE BUILDING a
SET    a.json = concat_json (a.id);

I get the following error:
06502. 00000 - "PL/SQL: numeric or value error%s"

What does it mean?
Re: Create JSON in new column by using sdo2geojson3d [message #654653 is a reply to message #654634] Mon, 08 August 2016 14:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please post a copy and paste of a run of compilation of the function and update statement from SQL*Plus, complete with line numbers, as I did.
The error message should include the line number that generates the error.

Re: Create JSON in new column by using sdo2geojson3d [message #654825 is a reply to message #654653] Mon, 15 August 2016 03:48 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
code:

CREATE OR REPLACE FUNCTION concat_json
  (p_id IN building.id%TYPE)
  RETURN   CLOB
AS
  v_count  NUMBER := 1;
  v_json   CLOB;
BEGIN
  FOR r IN
   (SELECT sdo2geojson3d (c.geometry) json
     FROM   thematic_surface b, surface_geometry c
     WHERE  p_id = b.building_id
     AND    b.lod2_multi_surface_id = c.root_id
     AND    c.geometry IS NOT NULL
     ORDER  BY c.root_id)
  LOOP
    IF v_count = 1 THEN
      v_json := SUBSTR (r.json, 1, LENGTH (r.json) - 3);
      v_count := v_count +1;
    ELSE
      DBMS_LOB.WRITEAPPEND (v_json, 1, ','); 
      DBMS_LOB.WRITEAPPEND 
        (v_json, 
         LENGTH (r.json) - 37, 
         SUBSTR (r.json, 35, LENGTH (r.json) - 37));
    END IF;
  END LOOP;
  DBMS_LOB.WRITEAPPEND (v_json, 3, ']]}');
  RETURN v_json;
END concat_json;
/


execution of the code

1 CREATE OR REPLACE FUNCTION concat_json
2  (p_id IN building.id%TYPE)
3  RETURN   CLOB
4 AS
5  v_count  NUMBER := 1;
6  v_json   CLOB;
7 BEGIN
8  FOR r IN
9     (SELECT sdo2geojson3d (c.geometry) json
10     FROM   thematic_surface b, surface_geometry c
11     WHERE  p_id = b.building_id
12     AND    b.lod2_multi_surface_id = c.root_id
13     AND    c.geometry IS NOT NULL
14     ORDER  BY c.root_id)
15  LOOP
16    IF v_count = 1 THEN
17      v_json := SUBSTR (r.json, 1, LENGTH (r.json) - 3);
18      v_count := v_count +1;
19    ELSE
20      DBMS_LOB.WRITEAPPEND (v_json, 1, ','); 
21      DBMS_LOB.WRITEAPPEND 
22        (v_json, 
23         LENGTH (r.json) - 37, 
24         SUBSTR (r.json, 35, LENGTH (r.json) - 37));
25    END IF;
26  END LOOP;
27  DBMS_LOB.WRITEAPPEND (v_json, 3, ']]}');
28  RETURN v_json;
29 END concat_json;
30 /
31 SHOW ERRORS
32 UPDATE building a
33 SET    a.json = concat_json (a.id)
34 /
35 SELECT id, json FROM building
36 /

> SHOW ERRORS
No errors.

> UPDATE building a
SET a.json = concat_json (a.id)

Fehler beim Start in Zeile : 1 in Befehl -
UPDATE building a
SET a.json = concat_json (a.id)
Fehlerbericht -
SQL-Fehler: ORA-06502: PL/SQL: numerischer oder Wertefehler: invalid LOB locator specified: ORA-22275
ORA-06512: in "SYS.DBMS_LOB", Zeile 1146
ORA-06512: in "TEST_3D.CONCAT_JSON", Zeile 28
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:


I need the coordinates maybe I can only create a column with the vertives inside the sdo_geometry.
Re: Create JSON in new column by using sdo2geojson3d [message #654841 is a reply to message #654825] Mon, 15 August 2016 16:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The error is due to a difference in versions.

In 12c, Oracle automatically does a lot of things that were previously required to be specified, such as creating temporary lob space.

It says that there is an invalid lob locator on line 28, which is shown below, where v_json is the lob.

28 RETURN v_json;

I don't have an older version to test on anymore, but as I recall, you should just need to add the following after BEGIN:

DBMS_LOB.CREATETEMPORARY (v_json, TRUE, DBMS_LOB.CALL);

That tells it to create a temporary lob for v_json, read it into the buffer cache, and free it after the function call.

In procedures, you can use dbms_lob.freetemporary, but in functions you can't use that before the return and nothing is executed after the return.

So, the revised function would be as below. Please test and see if this works on your system.

CREATE OR REPLACE FUNCTION concat_json
  (p_id IN building.id%TYPE)
  RETURN   CLOB
AS
  v_count  NUMBER := 1;
  v_json   CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY (v_json, TRUE, DBMS_LOB.CALL);
  FOR r IN
    (SELECT sdo2geojson3d (c.geometry) json
     FROM   thematic_surface b, surface_geometry c
     WHERE  p_id = b.building_id
     AND    b.lod2_multi_surface_id = c.root_id
     AND    c.geometry IS NOT NULL
     ORDER  BY c.root_id)
  LOOP
    IF v_count = 1 THEN
      v_json := SUBSTR (r.json, 1, LENGTH (r.json) - 3);
      v_count := v_count +1;
    ELSE
      DBMS_LOB.WRITEAPPEND (v_json, 1, ','); 
      DBMS_LOB.WRITEAPPEND 
        (v_json, 
         LENGTH (r.json) - 37, 
         SUBSTR (r.json, 35, LENGTH (r.json) - 37));
    END IF;
  END LOOP;
  DBMS_LOB.WRITEAPPEND (v_json, 3, ']]}');
  RETURN v_json;
END concat_json;
/
Re: Create JSON in new column by using sdo2geojson3d [message #654871 is a reply to message #654841] Tue, 16 August 2016 05:58 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Now it is working perfect Smile
thanks a lot!

It works for the building table.
I think I have to change this. I need the JSONs for the surface_geometry table.
In this table the polygons like walls, roofs witht he regarding geometry .. are available

So I need to update the query in this way:
UPDATE surface_geometry c
SET    c.json = concat_json (c.id);

How do I have to change the function because now it is written

04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
Re: Create JSON in new column by using sdo2geojson3d [message #654874 is a reply to message #654871] Tue, 16 August 2016 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do what the action says.

[Updated on: Tue, 16 August 2016 06:05]

Report message to a moderator

Re: Create JSON in new column by using sdo2geojson3d [message #654907 is a reply to message #654871] Tue, 16 August 2016 14:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
moehre wrote on Tue, 16 August 2016 03:58
Now it is working perfect :)
thanks a lot!

It works for the building table.
I think I have to change this. I need the JSONs for the surface_geometry table.
In this table the polygons like walls, roofs witht he regarding geometry .. are available

So I need to update the query in this way:
UPDATE surface_geometry c
SET    c.json = concat_json (c.id);

How do I have to change the function because now it is written

04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
You seem to be confused.

The update statement that you say is now working, updates the json column of the building table with the concatenation of the json
column values from the surface_geometry table for that building. It does this by passing the building_id to the function, so that
it can join to the other tables.

The new update statement that you are trying to run to concatenate one value would amount to updating it with the existing value,
which does nothing but cause the mutating error.

Use the update that is working. It isn't broken, so don't try to fix it.
Re: Create JSON in new column by using sdo2geojson3d [message #655597 is a reply to message #654907] Mon, 05 September 2016 07:20 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
I tried to update the function to construct jsons for each polygon inside the surface_geometry:

CREATE OR REPLACE FUNCTION concat_json
  (p_geometry IN sdo_geometry,
  p_root_id IN  surface_geometry.id%TYPE)--Number)
  RETURN   CLOB 
AS
  v_count  NUMBER := 1;
  v_json   CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY (v_json, TRUE, DBMS_LOB.CALL);
  FOR r IN
    (SELECT sdo2geojson3d (p_geometry) json
     --FROM   thematic_surface b, surface_geometry c, objectclass d
     FROM   building a, thematic_surface b, objectclass d --, surface_geometry c
     WHERE  a.id = b.building_id
     AND    b.lod2_multi_surface_id = p_root_id
     AND    p_geometry IS NOT NULL
     AND    b.OBJECTCLASS_ID = d.ID
     ORDER  BY p_root_id)
  LOOP
    IF v_count = 1 THEN
      v_json := SUBSTR (r.json, 1, LENGTH (r.json) - 3);
      v_count := v_count +1;
    ELSE
      DBMS_LOB.WRITEAPPEND (v_json, 1, ','); 
      DBMS_LOB.WRITEAPPEND 
        (v_json, 
         LENGTH (r.json) - 37, 
         SUBSTR (r.json, 35, LENGTH (r.json) - 37));
    END IF;
  END LOOP;
  DBMS_LOB.WRITEAPPEND (v_json, 3, ']]}');
  RETURN v_json;
END concat_json;
/

Update surface_geometry c
Set c.json = concat_json(c.geometry, c.root_id);

So it seems to me that this is working!
Re: Create JSON in new column by using sdo2geojson3d [message #655606 is a reply to message #655597] Mon, 05 September 2016 15:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since you are passing only one sdo_geometry in as a parameter and selecting only that sdo_geometry in your loop,
all that your function is doing is concatenating the same value to itself for however many rows there are in your
loop.

You have been given code that works, but don't seem to understand that, so you keep writing code that you think
works, but does not.

[Updated on: Mon, 05 September 2016 15:49]

Report message to a moderator

Re: Create JSON in new column by using sdo2geojson3d [message #655613 is a reply to message #655606] Tue, 06 September 2016 03:19 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Ok I understand within this loop it always take the same parameter. So at the end I have always the the same polygon. But the coordinates are not the same.
How can I do this. Because I cannot use the table surface_geometry inside the query. Then the error occurs:
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
But I have to use the table surface_geometry inside my query. It is just to understand how this can be solved.

I cannot use the surfacce_gemetry.geometry inside the parameter because then I have only one. So I have to use it inside the loop but then occurs the error above.
Re: Create JSON in new column by using sdo2geojson3d [message #655633 is a reply to message #655613] Tue, 06 September 2016 15:11 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
What is it that you are trying to do and why?

You already have a function and update statement that updates the json column of the building table
with the concatenation of all of the polygons for that building in json format.

You seem to want to update the json column of the surface_geometry table with the same information.
Why would you want each json value in each row of the surface_geometry table to have the same
concatenation of all of the polygons for the building? Using sdo2json3d you already have the concatenated
json for all of the polygons for that row in the surface_geometry.

It appears that you already have what you need, but don't realize it and continue to try to write code
to do the wrong things in the wrong ways.

Since you are not using Oracle 12c, you will not be able to use the json queries, so converting your data to
json is probably not even the right thing to do. You would be better off storing your data in columns of
data type mdsys.sdo_geometry, making appropriate entries into the user_sdo_geom_metadata view, and creating
a spatial index, which would enable you to query the data using Oracle spatial features.
Previous Topic: Using SDO_UTIL.GETVERTICES() to fetch all points
Next Topic: Cannot read tile layer config log from database
Goto Forum:
  


Current Time: Thu Mar 28 12:05:00 CDT 2024