[postgis-users] Efficient distance querying of lat/long point data in Postgis

[Rob Young]

Hi all,

I am looking to construct a query that selects all of the points
within a certain distance from another point, where all of the points
are stored as lat/long values.  I know that you have to use either
distance_sphere or distance_spheroid for calculating the distance of
the points, but it's creating a proper bounding box (which is speedy
with a GIST index) to reduce the number of points checked with
distance_* that I am wondering about.  I have done a lot of looking
around on the mailing list, but haven't a consensus on what the proper
way to handle this issue.  I found the following thread useful.

http://postgis.refractions.net/pipermail/postgis-users/2005-June/008496.html

However, it's mentioned that expanding around a point using the method
discussed doesn't work well as you approach the poles.

I was wondering if there is an estabilshed way to do this.

If not, I think I have a decent way to do this.  Say you want to find
all of the data with in X km of point A.  You could calculate four
points, B, C, D and E, which X/2 km away from the center.  B is due
north of A, C is due East of A, D is due south of A and E is due West
of A.  Then make a bounding box F with the upper left corner with the
coordinate of ( B.lat, E.lon ) and the lower right corner ( D.lat,
C.lon ).  Then use F to bound the query..  However, there are issues
wrapping around lat and long values with this, I think.

Anyway, I'd rather avoid doing that if possible, as getting all of
that right would be time consuming, especially if there is already a
decent solution to the problem.

Thanks for your help,
Robert Y