Use JSON_OBJECT() with SDE.ST_GEOMETRY

540
1
04-30-2022 11:28 PM
Bud
by
Notable Contributor

I'm wondering if there's anyone out there who has Oracle 21c and SDE.ST_GEOMETRY who could help me with a quick test:

If you were to run this query in Oracle 21c, what would it do?

select
    json_object(sde.st_geometry('MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))', 26917)) as sdo_geom
from
    dual

 

Background:

I suspect that some new functionality has been added to the JSON_OBJECT() function in Oracle 21c. I have 18c, not 21c, so I'm not able to test it myself. 

For example, the following SDO_GEOMETRY query works in 21c in an online environment called db<>fiddle. Of course, that environment doesn't have the SDE.ST_GEOMETRY datatype, so I can't do the ST_GEOMETRY test there.

select
    json_object(sdo_geometry('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_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]}

Related: Select JSON text of SDO_GEOMETRY using SQL


I'm curious what would happen if we did something similar in Oracle 21c, but with SDE.ST_GEOMETRY, instead of SDO_GEOMETRY.

Thanks!

0 Kudos
1 Reply
Bud
by
Notable Contributor

I’m aware that ArcGIS Server can return JSON in a feature service from a feature class. I still want to experiment with JSON and SQL.

0 Kudos