calculating spherical distance in sql

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?

Responses

Browse pgsql-general by date

  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?