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;
| 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.
SpatialDB Advisor
Home
Comment [1]
Simon,
Thanks. This works nicely so far.
I guess the main thing I would change would be the name since given the name one would expect it to behave like the built in dump functions in PostGIS.
Also I tend to prefix my own functions with something else besides ST to distinguish them from built-in PostGIS.
SG_GetPointCoords
sounds kind of neat.
— Regina · 15 February 2009, 09:11 · #