| 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: | Whole Thread | Raw Message | 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? |