[postgis-users] Large (and slow) Intersection

[James McEachern]

We have the GIST index, and use a statement just like this
for each individual polygon.

Our problem is that there are hundreds of polygons, and we would 
like to get a result with one statement. These polygons could
be in the four outer corners (but still very small in area),
which causes the the envelope to be large.

An example of the statement


SELECT points.the_geom
	FROM points 
	WHERE points.the_geom && 
       (SELECT geomunion(polys.the_geom) from polys 
       somekeyfield = "KEYVALUE')
	AND withing(points.the_geom, 
       (SELECT geomunion(polys.the_geom) from polys 
       somekeyfield = "KEYVALUE')); 


Now this works when the polygons are close together
geographically, but when they are wildly spaced over the
whole area, the && condition really returns all of the 
points.

The obvious solution is to first to first select all of the 
individual polys (no union), and then iteratively call your
SELECT for each polygon returned.

SELECT polys.the_geom from polys WHERE somekeyfield = "KEYVALUE';

foreach (poly in polyresult)
{
  SELECT points.the_geom
    FROM points 
    WHERE points.the_geom && poly
    AND withing(points.the_geom, poly);
  // keep track of the results of each iteration
} 

This gives the result quickly, but is more complex and more hits against
the database.

thanks,

jamesm

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
strk at refractions.net
Sent: Friday, August 27, 2004 2:17 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Large (and slow) Intersection

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users