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

[Shane Spencer]

strike that.. 1.5ms not 15ms..

I hope this is in line with the original problem.. Scott?

On 10/4/07, Shane Spencer <shane at bogomip.com> 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
> >
>