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?

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.

0
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
  • Just checking, is the where t.id=<> syntax correct?
    – User1973
    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
    – User1973
    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

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.