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?
