Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Select JSON text of SDO_GEOMETRY using SQL

User_1871May 1 2022 — edited May 17 2022

I have an multi-part SDO_GEOMETRY polyline.
How can I get the JSON text representation of that geometry?
This post has been answered by User_1871 on May 1 2022
Jump to Answer

Comments

User_1871
Answer

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

Marked as Answer by User_1871 · Jun 3 2022
User_1871

A Follow-up question:
Is there a way to get the pretty formatting -- without using a subquery?

Bkazar-Oracle

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

1 - 3

Post Details

Added on May 1 2022
#database-key-features, #spatial
3 comments
358 views