Am Montag, 27. Juni 2005 01:40 schrieb CSN:
> 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?
I did it without some special features and datatypes some time ago. feel free
to modify and use for your own. It should give you an idea how to do it.
SELECT
c1.zip,
c2.zip,
6378.388 *
acos(
sin(radians(c1.latitude)) * sin(radians(c2.latitude))
+ cos(radians(c1.latitude)) * cos(radians(c2.latitude))
* cos(radians(c1.longitude - c2.longitude))
) AS distance
FROM
coordinates AS c1
CROSS JOIN coordinates AS c2
I had some problems with the calculation inside acos() sometimes being greater
than 1, which should not occur. Please use a
CASE WHEN sin(...) > 1 THEN 1 ELSE sin(...) END
if you have the same problem.
kind regards,
janning
