Forum Stats

  • 3,837,640 Users
  • 2,262,276 Discussions
  • 7,900,337 Comments

Discussions

How does SQL Developer select SDO_GEOMETRY properties as text?

User_1871
User_1871 Member Posts: 244 Red Ribbon
edited May 4, 2022 2:36PM in SQL Developer

I have a Oracle 18c query that outputs an SDO_GEOMETRY object:

select
  sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom
from
  dual

That works as expected in SQL Developer:

CTRL+F5 Output: [MDSYS.SDO_GEOMETRY]

F5 Output: SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(1, 2, 3, 4))

Question: How does SQL Developer convert the SDO_GEOMETRY object properties to text? What code or functions does it use?

The reason I ask:

I have a use case where I want to output the SDO_GEOMETRY properties...the same way that SQL Developer does (when I hit F5).

But I haven't found a clean way to do that using SQL. There doesn't seem to be an OOTB function for it.

I cobbled together a query that more-or-less works:

select
    'MDSYS.SDO_GEOMETRY('                         ||
    a.shape.sdo_gtype                       || ', ' ||
    nvl(to_char(a.shape.sdo_srid), 'NULL')  || ', ' ||
  
    NVL2(
        a.shape.sdo_point,
        'MDSYS.SDO_POINT_TYPE ('
        || COALESCE(TO_CHAR(a.shape.sdo_point.X), 'NULL') || ', '
        || COALESCE(TO_CHAR(a.shape.sdo_point.Y), 'NULL') || ', '
        || COALESCE(TO_CHAR(a.shape.sdo_point.Z), 'NULL')
        || ')',
        'NULL'
    ) || ', ' ||  
  
     'MDSYS.SDO_ELEM_INFO_ARRAY(' || (select listagg(column_value,', ') from table(a.shape.sdo_elem_info)) || '), ' ||
     'MDSYS.SDO_ORDINATE_ARRAY(' || (select listagg(column_value,', ') from table(a.shape.sdo_ordinates)) || '))'
    as sdo_geom_properties
from
    (
    select
        SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(1, 2, 3, 4)) as shape
    from
        dual
    union all
    select
        SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(-79, 37, NULL), NULL, NULL) as shape
    from
        dual
  ) a  

But that's not as simple or robust as I'd like it to be.

How does SQL Developer do it? Could I do something similar to what SQL Developer does, but in a query?

I'm aware that we can convert SDO_GEOEMTRY to other formats like WKT or JSON. But in this case, I would prefer to output the raw SDO_GEOMETRY properties, if possible.

Tagged:

Answers