On 07/31/09 20:20, Kevin Neufeld wrote:
> Unfortunately, I think your solution is overly simplistic.
Of course, it is a little function dedicated to a specific use case for
my program.
> I think the
> community is after something that mimics the ST_Dump and ST_DumpRings
> functionality.
I agree.
> ie.
> SELECT (ST_Dump('MULTILINESTRING((0 0, 1 1),(2 2, 3 3))'::geometry)).*;
> path | geom
> ------+------------------------------------------------------------------------------------
>
> {1} |
> 01020000000200000000000000000000000000000000000000000000000000F03F000000000000F03F
>
> {2} |
> 0102000000020000000000000000000040000000000000004000000000000008400000000000000840
>
> (2 rows)
>
> In order to extract all the points from every geometry in a table we
> would need to be able to have this example query work that also returns
> a SETOF geometry_dump:
>
> SELECT (ST_DumpPoints(the_geom)).*
> FROM my_spatial_table;
>
> The path portion of the geometry_dump datatype is important because
> users know the index of POINT in the geometry. It'll be possible then,
> for example, to extract all but the endpoints. Additionally, users
> could group the points back together to rebuild the original geometry
> from whence they came.
>
> Also, your function would need to be a lot more robust (ie. ideally,
> able to accept all geometry types, POINT, LINESTRING, MULTIPOLYGON,
> GEOMETRYCOLLECTION, etc).
>
> I agree, the st_dump code does indeed look a little overwhelming. I
> don't think we're tied to having the function written in C (there are
> many plpgsql functions in PostGIS).
The only problem with plpgsql functions is that in versions prior to
PostgreSQL 8.4 you cannot mimic the 'RETURN SETOF' of the C. [1]
This can be solved by adding a sql function wrapper though.
[1]
http://www.depesz.com/index.php/2008/11/03/waiting-for-84-pl-srf-functions-in-selects/
> If you think you can come up with
> something that meets the desired specifications, please, by all means,
> feel free to propose something to the postgis-devel list. We'd love the
> help!
Here is a first draft (code is attached). The function handles any kind
of geometries and path.
- POINT
> # SELECT path,astext(geom) FROM (SELECT (my_ST_DumpPoints('POINT(0 0)')).*)
> AS foo;
> path | astext
> ------+------------
> {1} | POINT(0 0)
- MULTIPOINT
> # SELECT path,astext(geom) FROM (SELECT (my_ST_DumpPoints('MULTIPOINT((0 0),
> (1 1), (2 2))')).*) AS foo;
> path | astext
> -------+------------
> {1,1} | POINT(0 0)
> {2,1} | POINT(1 1)
> {3,1} | POINT(2 2)
- LINESTRING
> # SELECT path,astext(geom) FROM (SELECT (my_ST_DumpPoints('LINESTRING(0 3, 1
> 4, 1 5)')).*) AS foo;
> path | astext
> ------+------------
> {1} | POINT(0 3)
> {2} | POINT(1 4)
> {3} | POINT(1 5)
- CIRCULARSTRING
> # SELECT path,astext(geom) FROM (SELECT (my_ST_DumpPoints('CIRCULARSTRING(0
> 3, 1 4, 1 5)')).*) AS foo;
> path | astext
> ------+------------
> {1} | POINT(0 3)
> {2} | POINT(1 4)
> {3} | POINT(1 5)
- MULTILINESTRING
> # SELECT path,astext(geom) FROM (SELECT (my_ST_DumpPoints('MULTILINESTRING((0
> 3, 1 4, 1 5),(10 10, 15 10, 42 42))')).*) AS foo;
> path | astext
> -------+--------------
> {1,1} | POINT(0 3)
> {1,2} | POINT(1 4)
> {1,3} | POINT(1 5)
> {2,1} | POINT(10 10)
> {2,2} | POINT(15 10)
> {2,3} | POINT(42 42)
- POLYGON
> # SELECT path,astext(geom) FROM (SELECT (my_ST_DumpPoints('POLYGON((0 0, 50
> 50, 42 42, 0 0))')).*) AS foo;
> path | astext
> ------+--------------
> {1} | POINT(0 0)
> {2} | POINT(50 50)
> {3} | POINT(42 42)
> {4} | POINT(0 0)
- MULTIPOLYGON
> # SELECT path,astext(geom) FROM (SELECT (my_ST_DumpPoints('MULTIPOLYGON(((0
> 0, 1 1, 2 2, 0 0)), ((50 50, 40 40, 30 30, 50 50)))')).*) AS foo;
> path | astext
> -------+--------------
> {1,1} | POINT(0 0)
> {1,2} | POINT(1 1)
> {1,3} | POINT(2 2)
> {1,4} | POINT(0 0)
> {2,1} | POINT(50 50)
> {2,2} | POINT(40 40)
> {2,3} | POINT(30 30)
> {2,4} | POINT(50 50)
- GEOMETRYCOLLECTION
> # SELECT path,astext(geom) FROM (SELECT
> (my_ST_DumpPoints('GEOMETRYCOLLECTION(POINT(0 0), POLYGON((10 10, 20 10, 20
> 20, 10 10)))')).*) AS foo;
> path | astext
> -------+--------------
> {1,1} | POINT(0 0)
> {2,1} | POINT(10 10)
> {2,2} | POINT(20 10)
> {2,3} | POINT(20 20)
> {2,4} | POINT(10 10)
--
yabo
CREATE FUNCTION _my_ST_DumpPoints(the_geom geometry, cur_path integer[])
RETURNS SETOF geometry_dump AS $$
DECLARE
tmp geometry_dump;
nb_points integer;
nb_geom integer;
BEGIN
-- Special case (POLYGON) : return the points of the exterior ring of a
polygon
IF (ST_GeometryType(the_geom) = 'ST_Polygon') THEN
-- Support PostgreSQL < 8.4
FOR tmp IN SELECT * FROM _my_ST_DumpPoints(ST_ExteriorRing(the_geom),
cur_path) LOOP
RETURN NEXT tmp;
END LOOP;
-- PostgreSQL 8.4 required
-- RETURN QUERY (SELECT * FROM _my_ST_DumpPoints(ST_ExteriorRing(the_geom),
NULL));
RETURN;
END IF;
-- Special case (MULTI* OR GEOMETRYCOLLECTION) : iterate and return the
DumpPoints of the geometries
SELECT ST_NumGeometries(the_geom) INTO nb_geom;
IF (nb_geom IS NOT NULL) THEN
FOR i IN 1..nb_geom LOOP
-- Support PostgreSQL < 8.4
FOR tmp IN SELECT * FROM _my_ST_DumpPoints(ST_GeometryN(the_geom, i),
cur_path || ARRAY[i]) LOOP
RETURN NEXT tmp;
END LOOP;
-- PostgreSQL 8.4 required
-- RETURN QUERY (SELECT * FROM _my_ST_DumpPoints(ST_GeometryN(the_geom,
i), cur_path || ARRAY(i)));
END LOOP;
RETURN;
END IF;
-- Special case (POINT) : return the point
IF (ST_GeometryType(the_geom) = 'ST_Point') THEN
tmp.path = cur_path || ARRAY[1];
tmp.geom = the_geom;
RETURN NEXT tmp;
RETURN;
END IF;
-- Use ST_NumPoints rather than ST_NPoints to have a NULL value if the_geom
isn't
-- a LINESTRING or CIRCULARSTRING.
SELECT ST_NumPoints(the_geom) INTO nb_points;
-- This should never happen
IF (nb_points IS NULL) THEN
RAISE EXCEPTION 'Unexpected error while dumping geometry %',
ST_AsText(the_geom);
END IF;
FOR i IN 1..nb_points LOOP
tmp.path = cur_path || i;
tmp.geom := ST_PointN(the_geom, i);
RETURN NEXT tmp;
END LOOP;
END
$$ LANGUAGE plpgsql;
CREATE FUNCTION my_ST_DumpPoints(geometry) RETURNS SETOF geometry_dump AS $$
SELECT * FROM _my_ST_DumpPoints($1, NULL);
$$ LANGUAGE SQL;
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
