[SQL].2個經緯度坐標,計算直線距離

有一個專案需求是, 可以查詢某中心點 2000公尺內的景點, 之前是用 between 取出資料, 但取出的資料, 無法按照距離做排序, 所以還是乖乖地透過 SQL 把距離算出來.

計算坐標距離的 SQL 呼叫範例:

SELECT
  htx.ID, Longitude, Latitude
  ,acos(sin(radians(24.80181500042168)) * sin(radians(Latitude )) + cos(radians(24.80181500042168)) * cos(radians(Latitude )) * cos(radians(120.971596999978 - Longitude))) * 6372.8
as rout_distance
  , 24.80181500042168 as route_lat
  , 120.971596999978 as route_lon
FROM Info htx 
WHERE
(Longitude BETWEEN 120.961801499978 
AND 120.981392499978) and(Latitude BETWEEN 24.7928168004217 
AND 24.8108132004217) order by rout_distance , Type desc , Name

說明1: 24.80181500042168 是我要查詢的中心點 LAT, 120.971596999978 是我的中心點 LON.

說明2: 原本用來取距離是用 between 取, 但這有一個問題, 就是在正方向角角的, 其實會超過預設的距離.


ASP 關於 sql 組合的程式碼如下:

, acos(sin(radians("& me.route_lat &")) * sin(radians(HotelLatitude )) + cos(radians("& me.route_lat &")) * cos(radians(HotelLatitude )) * cos(radians("& me.route_lon &" - HotelLongitude))) * 6372.8 as rout_distance

專案的畫面如下:

資料來源:
Distance-based JOIN given Latitude/Longitude
http://stackoverflow.com/questions/8947998/distance-based-join-given-latitude-longitude