# 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?

Related:

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

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;
``````
• 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, 2021 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, 2021 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, 2021 at 5:32
• FYI - It turns out the `Table()` keyword is no longer necessary in queries like that. We can cross join directly to the `GetVertices()` function to get the vertices as rows. Cross-joining with a table object propagates rows (without needing Table() function). Jun 18, 2022 at 1:13