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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
