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