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@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
