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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83