Get XY coordinates from GEOM_SEGMENT_START_PT() output

Question

Oracle 18c:

How can I get the X & Y coordinates from GEOM_SEGMENT_START_PT()'s resulting LRS point?

select
  sdo_lrs.geom_segment_start_pt(sdo_geometry('linestring(1 2, 3 4, 5 6)'))
from
  dual

SDO_LRS.GEOM_SEGMENT_START_PT(SDO_GEOMETRY('LINESTRING(12,34,56)')) 
---------------------------------------------------
SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(1,2))

Note: The coordinates are stored in the SDO_ORDINATE_ARRAY( ), not the SDO_POINT.

Desired output:

 X  Y
-- --
 1  2

Normally, I'd use something like a.shape.sdo_point.x. But I don't think that applies here since the coordinates aren't stored in the SDO_POINT.

Related: Oracle Spatial Community - Get X & Y coordinates from GEOM_SEGMENT_START_PT()

Answer 1

Use the below SQL for getting X and Y coordinates

    select 
      t.X, 
      t.Y 
    from 
      TABLE(
        SDO_UTIL.GETVERTICES(
          sdo_lrs.geom_segment_start_pt(
            sdo_geometry('linestring(1 2, 3 4, 5 6)')
          )
        )
      ) t;

Answer 2

Building on @Psaraj12's answer — using multiple rows as an example and removing geom_segment_start_pt():

with cte as (
select sdo_geometry ('linestring (1 2, 3 4)', 26917) shape from dual union all
select sdo_geometry ('linestring (5 6, 7 8)', 26917) shape from dual union all
select sdo_geometry ('linestring (9 10, 11 12)', 26917) shape from dual)

select 
    * 
from 
    cte
cross join
    sdo_util.getvertices(cte.shape)
where
    id = 1

And I suppose I could get the endpoint by doing the same thing, but use this WHERE clause instead:

ID = SDO_UTIL.GETNUMVERTICES(SHAPE)

---

Related:

• Cross-joining with a table object propagates rows (without needing Table() function)
• At what version did the TABLE() keyword for table collection expressions become optional?]
• Cross join to varray without using Table() expression

Answer 3

Here's another option:

with data as (select sdo_geometry('linestring(10 20, 30 40, 50 60)') as shape from dual)

select 
    json_value((shape).Get_GeoJson(),'$.coordinates[0][0]') as startpoint_x
from 
    data

Source:

Idea: Add a SDO_ORDINATES member function (for extracting collection elements by index)