# ST_PointN equivalent in SDO_GEOMETRY?

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?

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.

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;
``````
• Just checking, is the `where t.id=<>` syntax correct? Sep 19 at 21:57
• You just need to pass the index number instead of <>. For example, if you need 2nd vertex of the geometry, then you need to pass t.id=2 Sep 20 at 4:53
• I think I get it now. It's kind of like this: i.stack.imgur.com/pO4y5.png ... Source: Cross join on a numbers table to get line vertices Sep 22 at 23:16
• What ever I shared should give correct result based on your original question. Please let me know if you faced any issue with it. May be you can write a procedure or function if you want to reuse it at many places or in loop. Sep 23 at 5:32