-1

Oracle 18c:

It's possible to get SDO_GEOMETRY line vertex ordinates as rows using the sdo_util.getvertices() function:

with cte as (
select 100 as asset_id, sdo_geometry('linestring (10 20, 30 40)')                       shape from dual union all
select 200 as asset_id, sdo_geometry('linestring (50 60, 70 80, 90 100)')               shape from dual union all
select 300 as asset_id, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') shape from dual)
  
select 
    cte.asset_id,
    id as vertex_id,
    v.x,
    v.y
from 
    cte, sdo_util.getvertices(shape) v

  ASSET_ID  VERTEX_ID          X          Y
---------- ---------- ---------- ----------
       100          1         10         20
       100          2         30         40

       200          1         50         60
       200          2         70         80
       200          3         90        100

       300          1        110        120
       300          2        130        140
       300          3        150        160
       300          4        170        180

The resulting rows have columns with ordinates as numbers.

I want to do something similar, but I want to get point geometries as rows for each vertex in the lines, instead of numbers.

The result would look like this:

  ASSET_ID  VERTEX_ID            SHAPE 
---------- ---------- ---------------- 
       100          1   [SDO_GEOMETRY] 
       100          2   [SDO_GEOMETRY] 

       200          1   [SDO_GEOMETRY] 
       200          2   [SDO_GEOMETRY] 
       200          3   [SDO_GEOMETRY] 

       300          1   [SDO_GEOMETRY] 
       300          2   [SDO_GEOMETRY] 
       300          3   [SDO_GEOMETRY] 
       300          4   [SDO_GEOMETRY]

Idea:

There is an undocumented function called SDO_UTIL.GET_COORDINATE(geometry, point_number).

(The name of that function seems misleading: it returns a point geometry, not a coordinate.)

select 
    cte.asset_id,
    sdo_util.get_coordinate(shape,1) as first_point
from 
    cte

  ASSET_ID           FIRST_POINT
---------- --------------------- 
       100  [MDSYS.SDO_GEOMETRY]
       200  [MDSYS.SDO_GEOMETRY]
       300  [MDSYS.SDO_GEOMETRY]

That function could be useful for getting vertices as point geometries.

Question:

Is there a way to get point geometries as rows for each vertex in the SDO_GEOMETRY lines?

User1974
  • 276
  • 1
  • 17
  • 63

3 Answers3

2

If you want the output as an MDSYS.ST_POINT data type then convert the MDSYS.SDO_GEOMETRY type to an MDSYS.ST_LINESTRING type and use the ST_NumPoints() and ST_PointN(index) member functions (from the MDSYS.ST_CURVE super-type) in a LATERAL joined hierarchical sub-query:

with cte (asset_id, shape) as (
  select 100, sdo_geometry('linestring (10 20, 30 40)')                       from dual union all
  select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)')               from dual union all
  select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
select c.asset_id,
       p.point
from   cte c
       CROSS JOIN LATERAL (
         SELECT ST_LINESTRING(c.shape).ST_PointN(LEVEL) AS point
         FROM   DUAL
         CONNECT BY LEVEL <= ST_LINESTRING(c.shape).ST_NumPoints()
       ) p;

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thanks! I was actually intending to return `SDO_GEOMETRY` values, not `MDSYS.ST_POINT` values. But your solution is easily adaptable to `SDO_GEOMETRY` too. I posted it in an answer here: https://stackoverflow.com/a/72694488/5576771. Cheers. – User1974 Jun 21 '22 at 02:00
1

Try...

with cte as (
select 100 as asset_id, sdo_geometry('linestring (10 20, 30 40)')                       shape from dual union all
select 200 as asset_id, sdo_geometry('linestring (50 60, 70 80, 90 100)')               shape from dual union all
select 300 as asset_id, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') shape from dual
)
select 
    c.asset_id,
    id as vertex_id,
    sdo_geometry(c.shape.sdo_gtype/10 * 10+1,
                 c.shape.sdo_srid,
                 sdo_point_type(v.x, v.y, v.z),
                 null,null) as point
from 
    cte c, sdo_util.getvertices(shape) v
User1974
  • 276
  • 1
  • 17
  • 63
Simon Greener
  • 425
  • 2
  • 5
  • Thanks. For my notes, the way that query works is: it takes advantage of the `GetVertices()` function by getting the vertices as rows and the ordinates as number columns (X, Y, Z, etc.)...just like we always do...but then it constructs point geometries from those ordinate rows using the `SDO_GEOMETRY()` function. That's a clever trick that I hadn't thought of. – User1974 Jun 21 '22 at 07:11
0

I came up with a cross join and connect by level solution that seems to work.

Although, there might be more succinct ways of doing it.

with 
data as (
    select 100 as asset_id, sdo_geometry('linestring (10 20, 30 40)')                       shape from dual union all
    select 200 as asset_id, sdo_geometry('linestring (50 60, 70 80, 90 100)')               shape from dual union all
    select 300 as asset_id, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') shape from dual),
vertices as (
    select level as vertex_index from dual connect by level <= (select max(sdo_util.getnumvertices(shape)) from data))
  
select 
    d.asset_id,
    v.vertex_index,
    sdo_util.get_coordinate(d.shape,v.vertex_index) as sdo_geom_point, --the ordinates are stored in the SDO_GEOMETRY's SDO_POINT attribute. Example: MDSYS.SDO_POINT_TYPE(10, 20, NULL)
    sdo_util.get_coordinate(d.shape,v.vertex_index).sdo_point.x as x,
    sdo_util.get_coordinate(d.shape,v.vertex_index).sdo_point.y as y
from 
    data d
cross join
    vertices v
where
    v.vertex_index <= sdo_util.getnumvertices(shape)
order by
    asset_id,
    vertex_index

Result:

  ASSET_ID VERTEX_INDEX SDO_GEOM_POINT                X          Y
---------- ------------ -------------------- ---------- ----------
       100            1 [MDSYS.SDO_GEOMETRY]         10         20
       100            2 [MDSYS.SDO_GEOMETRY]         30         40

       200            1 [MDSYS.SDO_GEOMETRY]         50         60
       200            2 [MDSYS.SDO_GEOMETRY]         70         80
       200            3 [MDSYS.SDO_GEOMETRY]         90        100

       300            1 [MDSYS.SDO_GEOMETRY]        110        120
       300            2 [MDSYS.SDO_GEOMETRY]        130        140
       300            3 [MDSYS.SDO_GEOMETRY]        150        160
       300            4 [MDSYS.SDO_GEOMETRY]        170        180

I added the X & Y columns to the query to show what the [MDSYS.SDO_GEOMETRY] values represent. I don't actually need the X&Y columns in my query.

Edit:

I borrowed @MT0's cross join lateral technique and adapted it for SDO_GEOMETRY instead of MDSYS.ST_POINT.

It's cleaner than my original cross join / connect by level approach.

with cte (asset_id, shape) as (
  select 100, sdo_geometry('linestring (10 20, 30 40)')                       from dual union all
  select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)')               from dual union all
  select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
select c.asset_id,
       vertex_index,
       p.point,
       sdo_util.get_coordinate(c.shape,p.vertex_index).sdo_point.x as x,
       sdo_util.get_coordinate(c.shape,p.vertex_index).sdo_point.y as y
from   cte c
       cross join lateral (
         select sdo_util.get_coordinate(c.shape,level) as point, level as vertex_index
         from   dual
         connect by level <= sdo_util.getnumvertices(c.shape)
       ) p;

The result is the same:

  ASSET_ID VERTEX_INDEX SDO_GEOM_POINT                X          Y
---------- ------------ -------------------- ---------- ----------
       100            1 [MDSYS.SDO_GEOMETRY]         10         20
       100            2 [MDSYS.SDO_GEOMETRY]         30         40

       200            1 [MDSYS.SDO_GEOMETRY]         50         60
       200            2 [MDSYS.SDO_GEOMETRY]         70         80
       200            3 [MDSYS.SDO_GEOMETRY]         90        100

       300            1 [MDSYS.SDO_GEOMETRY]        110        120
       300            2 [MDSYS.SDO_GEOMETRY]        130        140
       300            3 [MDSYS.SDO_GEOMETRY]        150        160
       300            4 [MDSYS.SDO_GEOMETRY]        170        180
User1974
  • 276
  • 1
  • 17
  • 63