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
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
