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 по дате отправления:

Предыдущее
От: "Andre' Blanchard"
Дата:
Сообщение: Follow Up: How to properly build postgresql version 7.2 on Unix Platforms?
Следующее
От: Doug McNaught
Дата:
Сообщение: Re: calculating spherical distance in sql