[postgis-users] Need help concatentating 60, 000 polygon geometries

[Martin Davis]

This is exactly the way that spatial databases are supposed to work.  
They maintain a spatial index on your set of geometrys, so you don't 
have to do things like write BSP trees.  The kind of speedup that you 
observed is exactly what we'd expect to see. 

ST_Collect() is an in-memory function, so you can't expect to throw 
large numbers of geometries at it and have it be performant.

The moral of the story: always look for ways to take advantage of 
spatial indexing!


Shane Spencer wrote:
> Funny, I actually just used Collect() for my first time today, it had
> a really hard time collecting more than 10000 of my polygons.  I
> noticed every time I added 1000 polygons to the selection limit while
> testing Collect() it doubled in time needed.  6000 polygons required a
> ton of memory.  I eventually started using Collect() with Envelopes()
> of the geometry, which was much faster but i wasn't patient enough to
> let it finish collecting 85k polygon extents.
>
> I decided to impart the original problem on myself, to see what I
> would do in Scotts shoes.  I was about to write a BSP table and some
> functions to do fast BSP collision detection which would have been an
> insane waste of resources.. Then I decided to try using GiST indexes,
> which is also a first for me.
>
> Once I created the GiST index on the table that has all the polygons,
> I selected only polygons who's bounding box contains the point, then
> was able to use collision detection via 'contains' on a much smaller
> set of polygons.
>
> 85k polygons, 2 seconds to create initial GiST index, 15 ms to do
> query.  Much better than 300ms w/o the index and using explicit OGC
> functions.
>
> select *, ST_Contains(wkb_geometry,
> GeomFromEWKT('SRID=32767;POINT(1680729.269 2623817.919)')) from
> parcels_muni where wkb_geometry &&
> ST_Envelope(GeomFromEWKT('SRID=32767;POINT(1680729.269
> 2623817.919)'));
>
> Amazing, truly.. I was about ready to do BSP's in SQL :)
>
> On 10/4/07, Kevin Neufeld <kneufeld at refractions.net> wrote:
>   
>> Scott Schulthess wrote:
>>     
>>> Hello everyone,
>>>
>>>
>>>
>>> I have a table with 60,000 rows.
>>>
>>>
>>>
>>> Each row has a geometry field that is a polygon.
>>>
>>>
>>>
>>> I want to concatenate all of these geometry objects into one big
>>> multipolygon, or at the very least, a polygon, so I can query easily
>>> on one geometry object to see if a point lies within that multipolygon.
>>>
>>>  ...
>>>
>>>       
>> Use collect(geometry set).
>>
>> CREATE TABLE my_multi_polygon AS
>>   SELECT collect(geom) AS geom
>>   FROM my_poly_table;
>>
>> -------------
>> Kevin Neufeld
>> Software Developer
>> Refractions Research Inc.
>> 300-1207 Douglas St.
>> Victoria, B.C., V8W 2E7
>>
>> Phone: (250) 383-3022
>> Email: kneufeld at refractions.net
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>     
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022