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

[Kevin Neufeld]

Nice.  I posted your function to the trac ticket for this function. (http://trac.osgeo.org/postgis/ticket/76)

I am a little concerned about scalability, but we'll see what the team thinks.

Good work.
-- Kevin

Maxime van Noppen wrote:
> 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)
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users