[postgis-users] Efficient SQL for point in field of shapes?

[Sean]

On Sep 3, 12:55 pm, John Poole <jlpool... at gmail.com> wrote:
> I have a table of assessor parcels (polygons) and I want to take a
> given point (actually will be taking samplings from a geocache
> tracker, so I'll be doing hundreds, possibly thousands of such
> queries) and determine which, if any, of the shapes contain the point.
>  The goal is to determine which parcel a tracked person is within and
> at what times.
>
> I'm new to spatial database, and suspect performing a relate or
> ST_contains against the 40,000 shapes would be horribly inefficient,
> especially if I have thousands of points.  Should I be reading up on
> indexes, or is there an elegant solution to this problem.
>
> I tried searching the archive of this list for "point in field of
> shapes" but results were too generic.
>
> John L. Poole

That shouldn't be very taxing.  A bounding box check in the where
clause should help speed things up.  e.g.

select p.id
from parcels p left join geocache g on st_contains(p.geom, g.geom)
where p.geom && g.geom

You'll only really know how efficient it is when you try it with real
data.  Of course, make sure you have a spatial index on the polygons.

Sean