Oracle – Coordinate extraction from vertices…

For a given feature (line or area) and for all of its members I need to extract the coordinates of (1) all the vertices, (2) the first vertex and (3) the last vertex (3 separate queries to create 3 different sets of results)

I’m using Oracle spatial.

I’ve tested this sql code for the table ARAMAL (it’s a 3d line entity; Primary key column: IPID; geometry column: GEOMETRY) and it works well.

(1) – List all the vertices

SELECT A.IPID, t.X, t.Y, t.Z, t.id FROM ARAMAL A, TABLE(SDO_UTIL.GETVERTICES(A.GEOMETRY)) t ORDER BY A.IPID, t.id;

Result (example for IPID=1479723):

IPID X Y Z id

1479723 -99340.38408 -102364.3603 10 1

1479723 -99341.21035 -102366.2701 11 2

1479723 -99342.03375 -102368.1783 12 3

1479723 -99342.86238 -102370.0875 13 4

… … …. … …

(2) – List the first vertex

SELECT A.IPID, t.X, t.Y, t.Z, t.id FROM ARAMAL A, TABLE(SDO_UTIL.GETVERTICES(A.GEOMETRY)) t where t.id=1 ORDER BY A.IPID;

Result (example for IPID=1479723)

IPID X Y Z id

1479723 -99340.38408 -102364.3603 10 1

(3) How can I obtain the last vertex purely with sql (no additional functions)?

(Expected) Result (example for IPID=1479723)

IPID X Y Z id

1479723 -99342.86238 -102370.0875 13 4

I guess this process could run faster if I use specific functions – I would also like to be able to use them.

I’ve come across a great site (Simon Greener) with some functions that I guess could do the trick

http://spatialdbadvisor.com/oracle_spatial_tips_tricks/322/st_vertexn-extracting-a-specific-point-from-any-geometry

The functions are:

ST_StartPoint

CREATE OR REPLACE
FUNCTION ST_StartPoint(p_geom IN mdsys.sdo_geometry)
    RETURN mdsys.sdo_geometry
  IS
  BEGIN
    RETURN ST_PointN(p_geom,1);
  END ST_StartPoint;
/

ST_EndPoint

CREATE OR REPLACE
  FUNCTION ST_EndPoint(p_geom IN mdsys.sdo_geometry)
    RETURN mdsys.sdo_geometry
  IS
  BEGIN
    RETURN ST_PointN(p_geom,-1);
  END ST_EndPoint;
/

I’m a newbie to this world and I don’t really get the syntax of these functions…

For the table ARAMAL that I’ve used before how should I use/apply them to get the results (and in the format) I need?

IPID X Y Z id

1479723 -99340.38408 -102364.3603 10 1

….

Thanks in advance, Best regards, Pedro

Answer

It doesn’t matter if you are working with spatial data, you are interested in row which has maximum id for given ipid, so you can run it like here:

select * 
  from (
    select a.ipid, t.x, t.y, t.z, t.id,
           max(t.id) over (partition by a.ipid) mx_id
      from aramal a, table(sdo_util.getvertices(a.geometry)) t)
  where id = mx_id;

^demo

There are several ways to get last row, you can use row_number(), subquery, like in many top-n questions on this site.

Oracle – Coordinate extraction from vertices (first, last and all vertices)

Advertisement

Answer