Re: Finding points within 50 miles

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