經緯度距離轉換

今日主題： 製造關於Map應用時，會使用使用者經緯度與DB的經緯度進行距離轉換，然後將使用者附近的例如100公尺的餐廳顯示在地圖上。

M$ SQL Server

CREATE FUNCTION [dbo].[distanceKM](
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
RETURNS decimal(12,6)
AS
BEGIN
-- CONSTANTS
DECLARE @EarthRadiusInMiles float;
SET @EarthRadiusInMiles = 3963.1
DECLARE @PI  float;
SET @PI = PI();
-- RADIANS conversion
DECLARE @lat1Radians float;
DECLARE @long1Radians float;
DECLARE @lat2Radians float;
DECLARE @long2Radians float;
SET @lat1Radians = @Latitude1 * @PI / 180;
SET @long1Radians = @Longitude1 * @PI / 180;
SET @lat2Radians = @Latitude2 * @PI / 180;
SET @long2Radians = @Longitude2 * @PI / 180;
RETURN Round(Acos(
Cos(@lat1Radians) * Cos(@long1Radians) * Cos(@lat2Radians) * Cos(@long2Radians) +
Cos(@lat1Radians) * Sin(@long1Radians) * Cos(@lat2Radians) * Sin(@long2Radians) +
Sin(@lat1Radians) * Sin(@lat2Radians)
) * @EarthRadiusInMiles * 1.609344, 6);
END

MySQL

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DELIMITER ;;

DROP FUNCTION IF EXISTS `distance`;;
CREATE FUNCTION `distance`(`geo1_latitude` decimal(10,6), `geo1_longitude` decimal(10,6), `geo2_latitude` decimal(10,6), `geo2_longitude` decimal(10,6)) RETURNS decimal(10,3)
    DETERMINISTIC
BEGIN
  return ((ACOS(SIN(geo1_latitude * PI() / 180) * SIN(geo2_latitude * PI() / 180) 
    + COS(geo1_latitude * PI() / 180) * COS(geo2_latitude * PI() / 180) 
    * COS((geo1_longitude - geo2_longitude) * PI() / 180)) * 180 / PI()) 
    * 60 * 1.1515);
END;;

DELIMITER ;

使用方式

SELECT *, distance(gps_lat, gps_lon, 25.046273,121.517498) as distanceKM
FROM `game_map`
WHERE distance(gps_lat, gps_lon, 25.046273,121.517498) < 0.1
ORDER BY distanceKM

— 完成 —