SQL 兩點經緯度求距離

參考資料 Fastest Way to Find Distance Between Two Lat/Long Points裡的 Binary Worrier 解法。

使用 MariaDB 10，測試的 DB table 為 locations

CREATE TABLE `locations` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `lat` double NOT NULL COMMENT '緯度',
  `lng` double NOT NULL COMMENT '經度'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `locations` (`id`, `name`, `lat`, `lng`) VALUES
(1, '南勢角捷運站', 24.990508728072932, 121.509155455015),
(2, '景安站', 24.993901994056493, 121.50479966768725),
(3, '台大門口', 25.016772139171792, 121.53351504607843);

ALTER TABLE `locations`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `locations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

-- 測試用的 SQL
-- 以南勢角捷運站為參考位置，求 3 公里以內的景點
SELECT *, 
       ( 6371 * ACOS( COS( RADIANS(24.990508728072932) ) 
              * COS( RADIANS( `lat` ) ) 
              * COS( RADIANS( `lng` ) - RADIANS(121.509155455015) ) 
              + SIN( RADIANS(24.990508728072932) ) 
              * SIN( RADIANS( `lat` ) ) ) ) AS `distance` 
from `locations` 
HAVING `distance` < 3 OR `distance` IS NULL
ORDER BY `distance`;