Implementing Oracle’s GetVertices function in PostGIS – ST_DumpPoints

The GetVertices function in the MDSYS.SDO_UTIL package in Oracle Spatial is an exceedingly useful function.

From the Oracle documentation of this function:

This function returns an object of VERTEX_SET_TYPE, which consists of a table of objects of VERTEX_TYPE. Oracle Spatial defines the type VERTEX_SET_TYPE as:

 CREATE TYPE vertex_set_type as TABLE OF vertex_type;

Oracle Spatial defines the object type VERTEX_TYPE as:

 CREATE TYPE vertex_type AS OBJECT
    (x NUMBER,
     y NUMBER,
     z NUMBER,
     w NUMBER,
     id NUMBER);

The GetVertices function, because it returns a ‘table of objects’, must be used in a SQL SELECT statement via a TABLE function which will present the elemnts of the set to SQL as a table of records. A simple example:

 SELECT v.*
   FROM TABLE(mdsys.sdo_util.GetVertices(mdsys.sdo_geometry('POLYGON ((1 1,5 2,5 7,2 7,1 1))',NULL))) v;
 
 X                      Y                      Z                      W                      ID
 ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
 1                      1                                                                    1
 5                      2                                                                    2
 5                      7                                                                    3
 2                      7                                                                    4
 1                      1                                                                    5
 
 5 rows selected

Now, as at PostgreSQL 8.3, there is no equivalent function in PostGIS, though there are some who are pushing for it to be implemented. In the meantime, as an exercise, I thought I would create a custom plpgsql function that does what GetVertices does.

Here is my coding of the function:

 -- First create the required type
 CREATE TYPE coordtype AS
    (x double precision,
     y double precision,
     z double precision,
     m double precision);
 
 -- Now define the function
 CREATE OR REPLACE FUNCTION ST_DumpPoints(p_geometry geometry)
   RETURNS SETOF CoordType IMMUTABLE
 AS $$
 DECLARE
     v_GeometryType varchar(1000);
     v_rec RECORD;
     v_point CoordType;
     c_points CURSOR ( p_geom geometry,
                       p_Geometrytype text )
     IS
       SELECT ST_X(p_geom),ST_Y(p_geom),ST_Z(p_geom),ST_M(p_geom)
        WHERE p_geometrytype = 'ST_Point'
       UNION ALL
       SELECT ST_X(p),ST_Y(p),ST_Z(p),ST_M(p)
         FROM ( SELECT ST_GeometryN(p_geom,generate_series(1,ST_NumGeometries(p_geom))) as p
                 WHERE p_geometrytype = 'ST_MultiPoint'
              ) AS multipoint
       UNION ALL
       SELECT ST_X(sp),ST_Y(sp),ST_Z(sp),ST_M(sp)
         FROM ( SELECT ST_PointN(p_geom, generate_series(1, ST_NPoints(p_geom))) as sp
                 WHERE ( p_GeometryType = 'ST_LineString' )
              ) AS linestring
        UNION ALL
        SELECT ST_X(sp),ST_Y(sp),ST_Z(sp),ST_M(sp)
          FROM ( SELECT ST_PointN(b.geom, generate_series(1, ST_NPoints(b.geom))) as sp
                   FROM (SELECT ST_GeometryN(p_geom,generate_series(1,ST_NumGeometries(p_geom))) as geom
                          WHERE ( p_GeometryType = 'ST_MultiLineString' )
                        ) as b
               ) AS multiline
        UNION ALL
        SELECT ST_X(sp),ST_Y(sp),ST_Z(sp),ST_M(sp)
          FROM ( SELECT ST_PointN(a.geom, generate_series(1, ST_NPoints(a.geom))) as sp
                   FROM ( SELECT ST_ExteriorRing(p_geom) as geom
                           WHERE ( p_GeometryType = 'ST_Polygon' )
                          UNION ALL
                          SELECT ST_InteriorRingN(p_geom,generate_series(1,ST_NumInteriorRings(p_geom))) as geom
                           WHERE ( p_GeometryType = 'ST_Polygon' )
                        ) a
               ) as polygon
        UNION ALL
        SELECT ST_X(sp),ST_Y(sp),ST_Z(sp),ST_M(sp)
          FROM ( SELECT ST_PointN(a.geom, generate_series(1, ST_NPoints(a.geom))) as sp
                   FROM ( SELECT ST_ExteriorRing(b.geom) as geom
                            FROM (SELECT ST_GeometryN(p_geom,generate_series(1,ST_NumGeometries(p_geom))) as geom
                                   WHERE ( p_GeometryType = 'ST_MultiPolygon' )
                                 ) b
                          UNION ALL
                          SELECT ST_interiorringn(c.geom,generate_series(1,ST_numinteriorrings(c.geom))) as geom
                            FROM (SELECT ST_GeometryN(p_geom,generate_series(1,ST_NumGeometries(p_geom))) as geom
                                   WHERE ( p_GeometryType = 'ST_MultiPolygon' )
                                 ) c
                        ) a
                      ) as multipoly;
      c_refcursor refcursor;
  BEGIN
      IF ( p_geometry is NULL ) THEN
        return;
      END IF;
      v_GeometryType := ST_GeometryType(p_geometry);
      IF ( v_GeometryType = 'ST_Geometry' ) THEN
        -- Could be anything... use native PostGIS function
        v_GeometryType = GeometryType(p_geometry);
        IF ( v_geometryType = 'GEOMETRYCOLLECTION' ) THEN
           FOR v_geom IN 1..ST_NumGeometries(p_geometry) LOOP
              FOR v_rec IN SELECT * FROM ST_DumpPoints(ST_GeometryN(p_geometry,v_geom)) LOOP
                  RETURN NEXT v_rec;
              END LOOP;
           END LOOP;
        ELSE
           -- Probably CURVED something...
           RETURN;
        END IF;
      END IF;
      IF ( v_geometryType NOT IN ('ST_Geometry','GEOMETRYCOLLECTION') ) THEN
        OPEN c_points(p_geometry,v_Geometrytype);
        c_refcursor := c_points;
        LOOP
          FETCH c_refcursor INTO
                v_point.x, v_point.y, v_point.z, v_point.m;
          EXIT WHEN NOT FOUND;
          RETURN NEXT v_point;
        END LOOP;
        CLOSE c_refcursor;
      END IF;
  END;
  $$ LANGUAGE 'plpgsql';

Firstly, I create a special coordinate type for use in the function (note that I have not added an ID field). I could have had the function return all its data as ST_Point geometries but I elected not to do so. One could create a simple overloaded function that did this if that is what is required.

Note that I do all the “heavy lifting” in a single SQL statement that will only execute one of the many SELECT statements if the geometry object is of the right type. This allows me to not have a whole series of IF statements in the body of the function that execute specific, separate, SQL statements depending on geometry type. This is my particular coding “style” – I do not claim it to be the best way, just my way!

Finally, I haven’t supported circular curves in geometries as the PostGIS API for accessing them is still very immature. At some stage I will do so.

Here are some tests:

 select * from ST_DumpPoints('POINT(1 2 3 4)'::geometry) as p;

 select * from ST_DumpPoints('MULTIPOINT(0 0 0, 1 1 1, 2 2 2, 3 3 3)') as p;

 select * from ST_DumpPoints('LINESTRING(0 0, 1 1, 2 2, 3 3)'::geometry) as p;

 select * from ST_DumpPoints('MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))'::geometry) as p;

 select * from ST_DumpPoints('POLYGON((326454.7 5455793.7,326621.3 5455813.7,326455.4 5455796.6,326454.7 5455793.7))'::geometry) as p;

 select * from ST_DumpPoints('MULTIPOLYGON(((326454.7 5455793.7,326621.3 5455813.7,326455.4 5455796.6,326454.7 5455793.7)),((326771.6 5455831.6,326924.1 5455849.9,326901.9 5455874.2,326900.7 5455875.8,326888.9 5455867.3,326866 5455853.1,326862 5455851.2,326847.4 5455845.8,326827.7 5455841.2,326771.6 5455831.6)))'::geometry) as p;

 select * from ST_DumpPoints('GEOMETRYCOLLECTION(POINT(2 3 4),LINESTRING(2 3 4,3 4 5))'::geometry) as p;

I hope this function is of use to someone.