[postgis-users] Large (and slow) Intersection

[Ethan Alpert]

In addition make sure you've created spatial indexes for your tables. I use the approach strk pointed out and gives me a significant speed up (seconds versus minutes). Another thing I've discovered is the query planner isn't too smart about using other non-spatial indexes first so I often select into a temporary table using non-spatial indexes and then perform my spatial operations on the subset.
 
-e

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of strk at refractions.net
Sent: Fri 8/27/2004 2:16 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


-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 5902 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20040827/19f63097/attachment.bin>