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

[Shane Spencer]

This was quite the learning experience..  As I was going through all
of this it became very evident what was the preferred and most
efficient solution.

I would totally dig seeing a BSP solution for large data sets, I'm
currently writing a few test cases and generating a ton of
non-overlapping random polygons (thanks Delaunay) and wigglin em a
bit.  I wanna see what a few million lines feels like, and how well a
GiST would work vs some sort of BSP crazyness.  I can't wait to sit on
my thumb for a few hours while generating a BSP data set :)

What a great list :)

On 10/5/07, Martin Davis <mbdavis at refractions.net> wrote:
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>