[postgis-users] ST_Dump-like function for simple (non-multi) geometries ?

[Maxime van Noppen]

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
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: my_st_dump_points.sql
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090801/a5896aa2/attachment.ksh>