ST_PointN equivalent in SDO_GEOMETRY?

Question

In SDE.ST_GEOMETRY, we have a function called ST_PointN:

ST_PointN takes an ST_LineString and an integer index and returns a point that is the nth vertex in the ST_LineString's path.

sde.st_pointn (line1 sde.st_linestring, index integer)

Example:

SELECT gid, sde.st_astext (sde.st_pointn (ln1, 2)) The_2ndvertex
 FROM POINTN_TEST;

GID   The_2ndvertex
----  -----------------------------
1     POINT (23.73 21.92)
2     POINT ZM (23.73 21.92 6.5 7.1)

Question:

Does Oracle's native datatype SDO_GEOMETRY have an equivalent function?

Related:

• Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in SQL?
• Get X & Y coordinates from GEOM_SEGMENT_START_PT()
• Why does GEOM_SEGMENT_START_PT() return SDO_ORDINATE_ARRAY, not an SDO_POINT?
• Idea: Function to get a 2D line's startpoint (as SDO_POINT)
• Support extracting collection elements by index in SQL

Answer 1

In addition to my previous "do-it-yourself" answer, there is actually a function that does this ... Only it’s called differently. And it’s not documented ...

The function is: SDO_UTIL.GET_COORDINATE(geometry, point_number)

Where point_number is the number of the point to return (1 is the first point, -1 means: return the last point). The function works with all geometry dimensions: 2D, 3D, or LRS.

For example:

with lines as (
  select SDO_GEOMETRY(
    2002, 4326, NULL, 
    SDO_ELEM_INFO_ARRAY(1, 2, 1), 
    SDO_ORDINATE_ARRAY(
      -85.293434, 35.018185, 
      -85.295677, 35.01944, 
      -85.297104, 35.02063, 
      -85.298569, 35.022507,
      -85.300278, 35.02351, 
      -85.303017, 35.025379, 
      -85.304947, 35.02655, 
      -85.306267, 35.027103, 
      -85.306419, 35.027397, 
      -85.307411, 35.02795
    )
  ) as geom
  from dual
)
select 
  sdo_util.get_coordinate (geom, 1) as first_point,
  sdo_util.get_coordinate (geom, -1) as last_point,
  sdo_util.get_coordinate (geom, 5) as point#5
from lines;

which returns the following:

FIRST_POINT                                                                       LAST_POINT                                                                       POINT#5
--------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-85.293434, 35.018185, NULL), NULL, NULL) SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-85.307411, 35.02795, NULL), NULL, NULL) SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-85.300278, 35.02351, NULL), NULL, NULL)
 
1 row selected.

Answer 2

There is no equivalent out of the box. But implementing such a function is easy with a bit of PL/SQL:

create or replace function st_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;
/

This function needs improving: make it more generic for the dimensionality of the input, check the validity of the point number ... That is left as an exercise to the reader.

Answer 3

You can do below to get vertex at an index from a linestring in Oracle SDO_GEOMETRY

SELECT 
    l.lid, 
    t.X, 
    t.Y, 
    t.id
FROM 
    tblLines l,
    TABLE(SDO_UTIL.GETVERTICES(l.geometry)) t 
where 
    t.id= <<Index of the vertex>>
ORDER BY 
    l.lId, 
    t.id;