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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
