calculating spherical distance in sql
От | Thomas T. Thai |
---|---|
Тема | calculating spherical distance in sql |
Дата | |
Msg-id | Pine.NEB.4.43.0202172258230.16005-100000@ns01.minnesota.com обсуждение исходный текст |
Список | 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?
В списке pgsql-general по дате отправления: