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  |
 |
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  |
 |
Barbara Boehmer
Messages: 9100
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 #654470 is a reply to message #654468] |
Wed, 03 August 2016 05:34 |
|
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 |
|
Barbara Boehmer
Messages: 9100
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 #654825 is a reply to message #654653] |
Mon, 15 August 2016 03:48 |
|
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 |
|
Barbara Boehmer
Messages: 9100
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 #655597 is a reply to message #654907] |
Mon, 05 September 2016 07:20 |
|
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 |
|
Barbara Boehmer
Messages: 9100
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 #655633 is a reply to message #655613] |
Tue, 06 September 2016 15:11 |
|
Barbara Boehmer
Messages: 9100
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.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 02:18:52 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
