[postgis-users] Large (and slow) Intersection

[strk at refractions.net]

I did not understand your problem, does this query do what you need ?

SELECT points.the_geom
	FROM points, polys 
	WHERE points.the_geom && polys.the_geom
	AND withing(points.the_geom, polys.the_geom);

--strk;

On Fri, Aug 27, 2004 at 01:20:29AM -0600, James McEachern wrote:
> 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
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users