SpatialDB Advisor

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;

x	y	z	m
1	2	3	4

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

x	y	z	m
0	0	0	(null)
1	1	1	(null)
2	2	2	(null)
3	3	3	(null)

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

x	y	z	m
0	0	(null)	(null)
1	1	(null)	(null)
2	2	(null)	(null)
3	3	(null)	(null)

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

x	y	z	m
0	0	(null)	(null)
1	1	(null)	(null)
1	2	(null)	(null)
2	3	(null)	(null)
3	2	(null)	(null)
5	4	(null)	(null)

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

x	y	z	m
326454.7	5455793.7	(null)	(null)
326621.3	5455813.7	(null)	(null)
326455.4	5455796.6	(null)	(null)
326454.7	5455793.7	(null)	(null)

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;

x	y	z	m
326454.7	5455793.7	(null)	(null)
326621.3	5455813.7	(null)	(null)
326455.4	5455796.6	(null)	(null)
326454.7	5455793.7	(null)	(null)
326771.6	5455831.6	(null)	(null)
326924.1	5455849.9	(null)	(null)
326901.9	5455874.2	(null)	(null)
326900.7	5455875.8	(null)	(null)
326888.9	5455867.3	(null)	(null)
326866	5455853.1	(null)	(null)
326862	5455851.2	(null)	(null)
326847.4	5455845.8	(null)	(null)
326827.7	5455841.2	(null)	(null)
326771.6	5455831.6	(null)	(null)

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

x	y	z	m
2	3	4	(null)
2	3	4	(null)
3	4	5	(null)

I hope this function is of use to someone.