Ticket #926 (new enhancement)
ST_GeometryN, ST_PointN (input of array) return set
|Reported by:||robe||Owned by:||pramsey|
Thinking about ST_DumpPoints and how it doesn't scale with Line strings.
The problem with ST_GeometryN and ST_PointN is that as Paul noted in IRC, requires a memory copy of the whole geometry for each call. See thread - http://logs.qgis.org/postgis/%23postgis.2011-04-27.log
I've had other needs in the past where I needed to explode only a portion of a geometry but not all. ST_Dump becomes slow because it needs to return data I don't care about and ST_PointN/ST_GeometryN are too slow too because if I care about 1000 points of a 100,000 geometry -- memcopy 1000 times is also painful.
ST_GeometryN/ST_PointN/ST_InteriorRingN(geom, int) returns a set of geometries and takes an arbitrary 1-dimensional array of index positions.
This has a myriad of utilities -- e.g. I could take a huge line and break it into multiple segments easily without incurring memory copy penalty.
My linestring dump would then be.
SELECT ST_PointN(geom, ARRAY(SELECT generate_series(1, ST_NumPoints(geom)));
And of course if I only cared about points from 4 to 6, I would do which I predict would be really fast even for a 100,000 point linestring
Then if we swap out the ST_PointN use in ST_DumpPoints with this new implementation, it may beat bitnerd's implementation.
And be much less cryptic looking :).