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

Shane Spencer shane at bogomip.com
Thu Oct 4 14:01:29 PDT 2007


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
>



More information about the postgis-users mailing list