Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 535.7K On-Premises Infrastructure
- 138.1K Analytics Software
- 38.6K Application Development Software
- 5.6K Cloud Platform
- 109.3K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Select JSON text of SDO_GEOMETRY using SQL

I have an multi-part SDO_GEOMETRY polyline.
How can I get the JSON text representation of that geometry?
Answers
-
It looks like I can use the JSON_OBJECT() function in 21c: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_OBJECT.html
Unformatted JSON:
select json_object(sdo_util.from_wktgeometry( 'MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))')) as sdo_geom from dual Output: SDO_GEOM -------- {"SDO_GTYPE":2006,"SDO_SRID":null,"SDO_POINT":{},"SDO_ELEM_INFO":[1,2,1,7,2,1],"SDO_ORDINATES":[0,5,10,10,30,0,50,10,60,10]}
https://dbfiddle.uk/?rdbms=oracle_21&fiddle=5e9b41a3f88877aee361e0dd6cc0fb64
Pretty JSON:
select json_object(* PRETTY) from ( select sdo_util.from_wktgeometry( 'MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))') as sdo_geom from dual ) Output: JSON_OBJECT(*PRETTY) ----------------------------- { "SDO_GEOM" : { "SDO_GTYPE" : 2006, "SDO_SRID" : null, "SDO_POINT" : { }, "SDO_ELEM_INFO" : [ 1, 2, 1, 7, 2, 1 ], "SDO_ORDINATES" : [ 0, 5, 10, 10, 30, 0, 50, 10, 60, 10 ] } }
https://dbfiddle.uk/?rdbms=oracle_21&fiddle=9cbba5ff5675f1337cd6e6b1bcd4fcb9
-
A Follow-up question:
Is there a way to get the pretty formatting -- without using a subquery?
-
Would You like to try the following?
SQL> select sdo_util.to_json( SDO_GEOMETRY(2006, null, null, SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1), SDO_ORDINATE_ARRAY(0.0, 5.0, 10.0, 10.0, 30.0 , 0.0, 50.0, 10.0, 60.0, 10.0))) json from dual;
JSON
--------------------------------------------------------------------------------
{"geometrycollection": {"geometries": [{"line": {"datapoints": [[0.0, 5.0], [10.
0, 10.0], [30.0, 0.0]]}}, {"line": {"datapoints": [[50.0, 10.0], [60.0, 10.0]]}}
]}}
Best regards