From: | "Thomas T(dot) Thai" <tom(at)minnesota(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | calculating spherical distance in sql |
Date: | 2002-02-18 05:01:43 |
Message-ID: | Pine.NEB.4.43.0202172258230.16005-100000@ns01.minnesota.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
i'm trying to calculate spherical distance and seeing the closest zipcodes
to a specified zipcode. this query works:
find all zips less than 20 miles from zip XXXXX (s = starting zip table):
SELECT z.zip_code,z.poname,z.state,
(3958.75 *
acos(
sin(s.latitude/57.2958) *
sin(z.latitude/57.2958) +
cos(s.latitude/57.2958) *
cos(z.latitude/57.2958) *
cos(z.longitude/57.2958 - s.longitude/57.2958)
)
) AS dist
FROM zipcodes AS z, zipcodes AS s
WHERE s.zip_code='55404'
AND
(3958.75 *
acos(
sin(s.latitude/57.2958) *
sin(z.latitude/57.2958) +
cos(s.latitude/57.2958) *
cos(z.latitude/57.2958) *
cos(z.longitude/57.2958 - s.longitude/57.2958)
)
) <20
ORDER BY dist LIMIT 10;
that works fine. but it seems like a waste to calculate the distance
twice, so i thought about trying a simpler version:
SELECT z.zip_code,z.poname,z.state,
(3958.75 *
acos(
sin(s.latitude/57.2958) *
sin(z.latitude/57.2958) +
cos(s.latitude/57.2958) *
cos(z.latitude/57.2958) *
cos(z.longitude/57.2958 - s.longitude/57.2958)
)
) AS dist
FROM zipcodes AS z, zipcodes AS s
WHERE s.zip_code='55401'
AND
dist <20
ORDER BY dist LIMIT 10;
but that didn't work. any ideas?
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2002-02-18 05:18:27 | Re: Database Performance? |
Previous Message | Andre' Blanchard | 2002-02-18 04:12:02 | Follow Up: How to properly build postgresql version 7.2 on Unix Platforms? |