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

[Simon Greener]

I wrote this back in February. I didn't include a geometry_dump {id} field but this could be added fairly easily.
http://www.spatialdbadvisor.com/postgis_tips_tricks/109/implementing-oracles-getvertices-function-in-postgis-st_dumppoints/
Simon
On Sat, 01 Aug 2009 04:20:16 +1000, Kevin Neufeld <kneufeld at refractions.net> wrote:

> Unfortunately, I think your solution is overly simplistic.  I think the community is after something that mimics the
> ST_Dump and ST_DumpRings functionality.
>
> 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).  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!
>
> Cheers,
> Kevin
>
>
> Maxime van Noppen wrote:
>> I was looking to PostGIS source code to see how hard it is to implement
>> and if I could be of any help but the st_dump function implementation is
>> quite complicated (at least for somebody exterior to the project).
>>
>> Is there any reason not to provide the function as an plpgsql
>> function ? Would a C implementation run really faster ?
>>
>> The SQL code seems quite simple, I currently use something like :
>>
>> CREATE FUNCTION dump_line(l geometry) RETURNS SETOF geometry AS $$
>> BEGIN
>>   FOR i IN 1..ST_NumPoints(l) LOOP
>>     RETURN NEXT ST_PointN(l, i);
>>   END LOOP;
>> END
>> $$ LANGUAGE plpgsql;
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 



-- 
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
  Email: simon at spatialdbadvisor.com
  Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3