Re: Finding points within 50 miles

How big is your data ? There are rather sophisticated and
very effective methods in astronomy. For example,
http://www.sai.msu.su/~megera/oddmuse/index.cgi/SkyPixelization,
http://www.sai.msu.su/~megera/oddmuse/index.cgi/pg_sphere


     Oleg
On Mon, 27 Jun 2005, Janning Vygen wrote:

> 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
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83