[postgis-users] efficiently testing for points near polygons

[David Kaplan]

Hi,

I have a large set of oceanic point data that I need to test to identify
all points that are within a certain distance of land.  I initially
tried doing the most obvious thing I could think of - buffer land
polygons and test points for intersection, but this is taking too long
for the full dataset and is giving me strange "terminating connection
due to administrator command" errors.  I imagine that there is something
in my initial strategy that doesn't scale well, but it is hard for me to
identify what is the best strategy.  Hopefully someone already knows the
answer and can give me a hand.

Here are some concrete questions that I hope someone can help me with:

1) How exactly are indexes used for point data?  AS BBox doesn't make
much sense (at least to me), I imagine that this somehow groups
identical points so that operations only have to be done once for each
distinct point.  Is this the case?

2) I initially made a function to test for proximity to land:

CREATE OR REPLACE FUNCTION near_land(geo geometry)
RETURNS boolean AS
$BODY$
SELECT bool_or( ST_Intersects($1,wkb_geometry_5km_buf) ) 
FROM gshhs_h_l1;
$BODY$ 
LANGUAGE 'sql' STABLE;

This works fast for small amounts of data, but I have a feeling that
this prohibits using indexes for the full dataset and therefore could be
causing much of my slowness.  Is this so?

3) Would it be best to ST_Union all my polygons before making the
comparison, knowing that the polygons don't overlap?  Or is this no
different than just doing the intersect and then using bool_or?

4) Would using long transaction support help me avoid the "administrator
command" errors?  I don't really know what long transaction support
does, but my transaction certainly is long...

Thanks for the help.

Cheers,
David



-- 
**********************************
David M. Kaplan
Charge de Recherche 1

Institut de Recherche pour le Developpement
Centre de Recherche Halieutique Mediterraneenne et Tropicale
av. Jean Monnet
B.P. 171
34203 Sete cedex
France

Phone: +33 (0)4 99 57 32 27
Fax: +33 (0)4 99 57 32 95

http://www.ur097.ird.fr/team/dkaplan/index.html
http://www.amped.ird.fr/
**********************************