[postgis-users] Large (and slow) Intersection

[James McEachern]

Hi All,

We are trying to locate point objects that fall within polygon objects.

Our data points are spread out over a large area, and the polygons used to
locate may be spread over an equally large area, but only would have a very
small coverage (< 1% of total area)

There are ~500K point objects, and upwards of 1000 polygons.

Of course if we union the polygons together, the resulting
envelope used for the && operation basically gets all of the points.
And this would run for ever.

By doing the query on any single polygon with the && and intersection,
we get the correct result very fast, therefore running it for 
1000 polygons, it would take around ~1000 seconds. Quite manageable.

I have seen previous posts regarding this problem/solution, and they used an
iterative solution, to walk over the polygon result set (in code)
doing each && intersection independently and summing (actually unioning)
the results. Creating a stored proc looks like one solution.

I am looking for a correlated sub-query (or something)
to get the result. 

Does anyone know if a single (correlated) query is possible?

Thanks,

James S McEachern
Geo Webworks Inc.
2020, 801 6 Avenue SW
Calgary, Alberta
T2P 3W2

403-301-4001