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?

Answer 1

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 2

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;