1

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:

3 Answers 3

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

1
1

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;
4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.