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

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
``````
• Commented Oct 11, 2021 at 6:52
• Commented Oct 11, 2021 at 6:53

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
``````