Top 5 Recent Articles

ARTICLES CATEGORIES

Using Oracle’s ST_GEOMETRY type hierarchy with SDO_GEOMETRY: ST_PointN and ST_NumPoints

Oracle Spatial has, for many versions, made available an implementation of the OpenGIS SFS type hierarchy.

Today I will show how to use this type hierarchy do extract points from an SDO_GEOMETRY.

The first example extracts the first and last point from a linestring constructed from Well Known Text.

 WITH DATA AS (
   SELECT TREAT(ST_Geometry.FROM_WKT('LINESTRING(0 0,1 1)',28355) AS ST_Curve) AS line
     FROM dual
 )
 SELECT a.line.ST_GeometryType() AS geometry_type,
        a.line.ST_PointN(1).GET_WKT() AS first_point,
        a.line.ST_PointN(a.line.ST_NumPoints()).GET_WKT() AS last_point
   FROM DATA A;
 GEOMETRY_TYPE FIRST_POINT     LAST_POINT
 ------------- --------------- ---------------
 ST_LINESTRING POINT (0.0 0.0) POINT (1.0 1.0)

The second example finds the first and last point from an SDO_GEOMETRYconstructed from that type.

 WITH DATA AS (
   SELECT mdsys.ST_CURVE(
                  SDO_GEOMETRY(2002,28355,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(0,0,1,1,2,2))
          ) AS line
     FROM dual
 )
 SELECT a.line.ST_GeometryType() AS geometry_type,
        a.line.ST_PointN(1).GET_WKT() AS first_point,
        a.line.ST_PointN(a.line.ST_NumPoints()).GET_WKT() AS last_point
   FROM DATA a;
 GEOMETRY_TYPE FIRST_POINT     LAST_POINT
 ------------- --------------- ---------------
 ST_LINESTRING POINT (0.0 0.0) POINT (2.0 2.0)

The last example shows how to extract the first and last point from the exterior ring of a polygon.

 WITH DATA AS (
   SELECT TREAT(
            mdsys.ST_Polygon.FROM_WKT(
                   'POLYGON((0 0,1 0,1 1,0 1,0 0))',28355
            )
            AS ST_POLYGON) AS poly
     FROM dual
 )
 SELECT a.poly.ST_GeometryType() AS geometry_type,
        treat(a.poly.ST_ExteriorRing() AS ST_Curve).ST_PointN(1).GET_WKT() AS first_point,
        treat(a.poly.ST_ExteriorRing() AS ST_Curve).ST_PointN(a.poly.ST_ExteriorRing().ST_NumPoints()).GET_WKT() AS last_point
   FROM DATA A;
 GEOMETRY_TYPE FIRST_POINT     LAST_POINT
 ------------- --------------- ---------------
 ST_POLYGON    POINT (0.0 0.0) POINT (0.0 0.0)

Oracle’s ST_GEOMETRY is a true type hierarchy. TREAT is an Oracle function that casts an object to another related type. One cannot cast/TREAT an ST_LINESTRING to a ST_POLYGON as ST_LINESTRING does not inherit from ST_POLYGON.

I hope this is of use to someone.