[postgis-devel] Split By function

[Kevin Neufeld]

Agreed that the syntax is a little strange ... and it has it's place.  
In particular, ST_GeometryN is faster for selecting one or a few 
geometries from a geometry collection. 
Using the test tables from before,

-- Select the 100th geometry from a table with a single multipoint geometry.
EXPLAIN ANALYZE SELECT ST_GeometryN(the_geom, 100) FROM _1000_m;
-- Total runtime: 0.468 ms

-- Using ST_Dump()...
EXPLAIN ANALYZE SELECT * FROM (SELECT (ST_Dump(the_geom)).* FROM 
_1000_m) AS a WHERE a.path[1] BETWEEN 100 AND 100;
-- Total runtime: 26.412 ms

However, since it's a set returning function, as we've seen in my 
previous post, if our use case is to disaggregate the entire collection, 
it's orders of magnitude faster.

If we modified the function to return a record instead of a column 
definition list, we could do a query like this:
SELECT * FROM ST_Dump((SELECT the_geom FROM _1000_m)) AS (p integer[], 
the_geom geometry) WHERE p[1] BETWEEN 100 AND 100;

This last query would avoid the .path and .geom notation, but I don't 
know if it's any clearer than what Regina demonstrated.

What in particular did you have in mind, Paul?

-- Cheers,
Kevin



Paul Ramsey wrote:
>
> On Jun 23, 2008, at 1:44 PM, Kevin Neufeld wrote:
>
>> So, not only does ST_GeometryN not scale well (what is that, 
>> quadratic scaling?), but it consumes all memory on my 4GB server.
>>
>> SELECT (ST_Dump(geom)).geom  on the other hand is linear and has no 
>> problems.
>>
>> Unfortunately, this doesn't solve the ST_PointN equivalent problem.
>>
>> I suggest we add a ST_DumpPoints() method to the TODO list!
>
> If that's the syntax you want... do you like the .path, .geom thing? 
> Perhaps we should do the syntax/naming we like and document that, 
> since st_dump appears to be currently unknown(ish).
>
> P
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel