On Jun 26, 2005, at 7:40 PM, CSN wrote:
> If I have a table of items with latitude and longitude
> coordinates, is it possible to find all other items
> that are within, say, 50 miles of an item, using the
> geometric functions
> (http://www.postgresql.org/docs/8.0/interactive/functions-
> geometry.html)?
> If so, how?
We optimize this query by first finding the bounding square, then
comparing the lat/lon of the other objects (in our case zip codes)
for radius. This has the advantage of deleting a *lot* of possible
values before passing them to the heavy math formulas.
so ours boils down to something along these lines ($zip_radius is the
miles we're looking for)
the distance computation:
(acos((sin($input_lat/57.2958) * sin(zip_latitude/57.2958)) + (cos
($input_lat/57.2958) * cos(zip_latitude/57.2958) * cos(zip_longitude/
57.2958 - $input_long/57.2958))) * 3963) <= $zip_radius
and the bounding box is done like this:
$lat_range = $zip_radius / ((6076. / 5280.) * 60);
$long_range = $zip_radius / (((cos($input_lat * 3.141592653589 / 180)
* 6076.) / 5280.) * 60);
so just do a +/- of the center point lat/lon with the above values
and you have your square bounding box inside which you run your
distance computation.
Putting it together is left as an exercise for the reader (hint: just
AND your pieces together...)
Vivek Khera, Ph.D.
+1-301-869-4449 x806