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

[John Poole]

On Tue, Sep 7, 2010 at 1:21 PM, Sean <seanasy at gmail.com> wrote:
>
>
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

You're right, it was not taxing, my worry was misplaced and the
response times are within acceptable ranges.

Thank you

-- 
John L. Poole

P.O. Box 6566
Napa, CA 94581-6566
707-812-1323

jlpoole56 at gmail.com