1

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:

  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
2
0

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:


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.