[postgis-users] Large (and slow) Intersection

[Paul Ramsey]

I don't understand why you are not using the simple SQL for this 
problem:

select points.the_geom
from points, polys
where
   points.the_geom && polys.the_geom
and
   polys.keyfield = 'KEYVALUE'
and
   within(points.the_geom,polys.the_geom)

Make sure you have USE_STATS turned on and have run select 
update_geometry_stats(), (or if you are in 8.0 land, have simply run 
vacuum analyze (i love that)).

On Friday, August 27, 2004, at 08:39 AM, James McEachern wrote:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632