[postgis-users] Fastest way to determine if a point has nearby points

Mathieu Viel viel at benchmark.fr
Thu Jan 24 07:06:10 PST 2008

Hi guys.

I have a table with thousands of points and i want to determine which 
points have nearby points (within 2 kilometers range).
I've been searching for a quick way to do this and i found the expand() 
+ distance_phere() method is working quite well but when working with 
few places.

Does any of you know how i could get all my results without waiting for 
24 hours, which is the time i got to wait for this SQL request :

SELECT name FROM my_places
JOIN my_places places_1 ON places_1.status = 1 AND places_1.geom IS NOT NULL
JOIN my_places places_2 ON places_1.id <> places_2.id AND 
places_2.status = 1 AND places_2.geom && Expand(places_2.geom,2000) AND 
distance_sphere(places_1.geom,places_2.geom) < 2000
WHERE    my_places.country_id = 1

The cost of the request is very high so i'm quite sure i have to change 
it but is there a way to determine what i want without the two JOIN clauses?

Thanks a lot for any tips.


More information about the postgis-users mailing list