Query for ST_Geometry line vertices via a recursive SQL WITH clause?

Question

I'm exploring ways to get ST_Geometry line vertices as individual rows in a query resultset (via Oracle SQL).

The reason being, ST_Geometry doesn't have a GetVertices function (see ESRI idea here).

---

There seem to be a few alternative SQL options:

1. Use a cross join.

   • Makes use of a NUMBERS table (or the CONNECT BY clause):

   •  select   
          a.line,
          b.numbers as vertex_id,
          a.sde.st_x(sde.st_pointn(a.shape, b.numbers)) as x,
          a.sde.st_y(sde.st_pointn(a.shape, b.numbers)) as y
      from
          lines a
      cross 
         join numbers b
      where    
          b.numbers <= sde.st_numpoints(a.shape)
     

2. Or, create a custom GetVertices() function and a custom datatype:

   • This example is for SDO_GEOMETRY, but it could be converted to ST_Geometry: Extract line vertices into a nested table column (in a query resultset)

3. Or, use iterations in Oracle's MODEL clause.

   • I haven't figured out how to do this yet, but it might be an option.

---

Question:

As a fourth option, is it possible to get ST_Geometry line vertices as rows in a resultset -- by using recursion in the WITH clause? (Oracle)

The input data would look like this:

   LINE_ID    WKT                                                             
--------------------------------------------------------------------------------
         1    LINESTRING (671539.685273438 4863324.18143614, 671595.050070336 4863343.16655619, 671614.013553706 4863350.34348304, 671622.204415338 4863353.52539613)         
                                                                                
         2    LINESTRING (71534.5567096211 4863119.99180975, 671640.738468866 4863157.13274525, 671684.86211504 4863172.02299559)                                             
                                                                                
         3    LINESTRING (671622.204415338 4863353.52539613, 671633.326716411 4863357.84622911, 671904.061407769 4863451.28616675)                                            
                                                                                
         4    LINESTRING (671684.862052112 4863172.02299559, 671892.149614432 4863244.14144007, 671951.21565712 4863264.82431039, 671957.447146119 4863266.84761768, 671966.824385692 4863269.14663266) 

                                                  

And the output would look like this:

   LINE_ID  VERTEX_ID          X          Y
---------- ---------- ---------- ----------
         1          1 671539.685 4863324.18
         1          2 671595.050 4863343.17
         1          3 671614.014 4863350.34
         1          4 671622.204 4863353.53

         2          1 71534.5567 4863119.99
         2          2 671640.738 4863157.13
         2          3 671684.862 4863172.02

         3          1 671622.204 4863353.53
         3          2 671633.327 4863357.85
         3          3 671904.061 4863451.29

         4          1 671684.862 4863172.02
         4          2 671892.150 4863244.14
         4          3 671951.216 4863264.82
         4          4 671957.447 4863266.85
         4          5 671966.824 4863269.15

Answer 1

I came up with an SDO_GEOMETRY solution. I think it could be adapted to work with SDE.ST_GEOMETRY — but I haven’t done that yet.

Adapting it to SDE.ST_GEOMETRY would involve:

• Replace cust_pointn with st_pointn.
• Replace sdo_util.getnumvertices with st_numpoints.
• Replace .sdo_point.x with st_x.

---

SDO_GEOMETRY solution:

--Custom PointN fucntion: https://gis.stackexchange.com/a/412038/135445
create or replace function cust_pointn (g sdo_geometry, n number)
return sdo_geometry
as
begin
  return (
    sdo_geometry (
      2001,
      g.sdo_srid,
      sdo_point_type (
        g.sdo_ordinates((n-1) * 2 + 1),
        g.sdo_ordinates((n-1) * 2 + 2),
        null
      ),
      null,
      null
    )
  );
end;
/

--Generate rows for each vertex using recursive WITH: https://stackoverflow.com/a/65993625/10936066
with  rcte (line_id, vertex_id, num_vertices) as (
      select line_id, 1 as vertex_id, sdo_util.getnumvertices(shape) as num_vertices
      from a_sdo_geometry_tbl
      where sdo_util.getnumvertices(shape) > 0
      union all
      select line_id, vertex_id + 1, num_vertices
      from rcte
      where vertex_id < num_vertices
     )

--1. Select from the recursive WITH; has rows for each vertex
--2. Join to the original table to get the shape/line column
--3. Use the custom function to get the vertex shape/point (since I couldn't figure out how to include the shape column in the recursive WITH query)
--4. get the x and y from the vertex shape/point
select 
    a.line_id, 
    a.vertex_id,
    cust_pointn(shape,a.vertex_id) as vertex_point_shape,
    cust_pointn(shape,a.vertex_id).sdo_point.x as x,
    cust_pointn(shape,a.vertex_id).sdo_point.y as y
from 
    rcte a
left join
    a_sdo_geometry_tbl b
    on a.line_id = b.line_id
order by
    a.line_id,
    a.vertex_id