GIS 위치 기반 비교를 위한 유틸리티 함수

MySQL의 GIS Extension을 사용하거나,
아니면, 기본 숫자형의 타입을 이용하여 위치 정보를 관리하는 경우,
특정 GPS 좌표로부터 반경 몇Km 이내의 좌료 정보를 검색하는 경우가 많이 발생한다.
그러한 조작들을 위해서 몇 가지 유용한 MySQL Stored function을 만들어 보았다.
(각 함수의 DETERMINISTIC 옵션은 절대 빼면 안됨)






-- // ------------------------------------------------------------------------------
-- // 두 GPS 좌표간의 실제 거리를 Km 단위로 리턴해주는 함수 ------------------------
DELIMITER ;;
CREATE 
  DEFINER='svc_account'@'%'
FUNCTION GeoDistance(p_lat1 float, p_lon1 float, p_lat2 float, p_lon2 float) RETURNS float
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : position1(from)'s latitude(위도) */
  /*!99999 Param 2 : position1(from)'s longitude (경도)*/
  /*!99999 Param 3 : position2(to)'s latitude(위도) */
  /*!99999 Param 4 : position2(to)'s longitude(경도) */
  
  DECLARE v_theta float;
  DECLARE v_dist float;
           
  SET v_theta = p_lon1 - p_lon2;
  SET v_dist = SIN(p_lat1 * PI() / 180.0) * SIN(p_lat2 * PI() / 180.0) + 
          COS(p_lat1 * PI() / 180.0) * COS(p_lat2 * PI() / 180.0) * COS(v_theta * PI() / 180.0);

  SET v_dist = ACOS(v_dist);
  SET v_dist = v_dist / PI() * 180.0;
  SET v_dist = v_dist * 60 * 1.1515;
  SET v_dist = v_dist * 1.609344; /*!99999 Convert miles to Kilometers */
      
  RETURN v_dist;
END
;;
DELIMITER ;




-- // ------------------------------------------------------------------------------
-- // 사용 예제 --------------------------------------------------------------------
mysql>select GeoDistance(32.96970, -96.80322, 29.46786, -98.53506) as distance_km;
+-----------------+
| distance_km     |
+-----------------+
| 422.73672485352 | 
+-----------------+

mysql>select GeoDistance(32.00000, -96.00000, 32.10000, -96.00000) as distance_km;
+-----------------+
| distance_km     |
+-----------------+
| 11.215754508972 | 
+-----------------+






-- // ------------------------------------------------------------------------------
-- // 특정 원점으로부터 반경 ? Km 사각 영역의 위도 경도 좌표를 리턴하는 함수  ------
DELIMITER ;;


CREATE 
  DEFINER='svc_account'@'%'
FUNCTION GetMinLongitude(p_lat double, p_lon double, p_radiuskilo int) RETURNS double
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  RETURN p_lon - (p_radiuskilo / abs(cos(radians(p_lat))*111.2));
END
;;




CREATE
  DEFINER='svc_account'@'%'
FUNCTION GetMaxLongitude(p_lon double, p_lat double, p_radiuskilo int) RETURNS double
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  RETURN p_lon + (p_radiuskilo / abs(cos(radians(p_lat))*111.2));
END
;;




CREATE
  DEFINER='svc_account'@'%'
FUNCTION GetMinLatitude(p_lon double, p_lat double, p_radiuskilo int) RETURNS double
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  RETURN p_lat - (p_radiuskilo / 111.2);
END
;;


CREATE
  DEFINER='svc_account'@'%'
FUNCTION GetMaxLatitude(p_lon double, p_lat double, p_radiuskilo int) RETURNS double
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  RETURN p_lat + (p_radiuskilo / 111.2);
END
;;


DELIMITER ;




-- // ------------------------------------------------------------------------------
-- // 사용 예제 --------------------------------------------------------------------
-- // shop_position.latitude와 shop_position.longitude 컬럼은 DOUBLE, FLOAT 타입임
SELECT *
FROM shop_position sp
WHERE
  sp.longitude between GetMinLongitude(137.2164, 34.9981, 3) and GetMaxLongitude(137.2164, 34.9981, 3)
  and sp.latitude between GetMinLatitude(137.2164, 34.9981, 3) and GetMaxLatitude(137.2164, 34.9981, 3)
  /* 하단의 조건은 사각 영역이 아니라, 원형으로 반경 검색일 경우 필요한 조건임 */
  and SQRT(POW(sp.longitude - 132.4625946, 2) + POW(sp.latitude - 34.3914636, 2)) < (3 /*Km*/ / 111.2 /*Km*/);






-- // ------------------------------------------------------------------------------
-- // 특정 원점으로부터 반경 ? Km 사각 영역의 Polygon 객체를 생성 리턴해주는 함수  -
DELIMITER ;;


CREATE 
  DEFINER='svc_account'@'%' 
FUNCTION GetRectBoundary(p_lat double, p_lon double, p_radiuskilo int) RETURNS Polygon
    DETERMINISTIC NO SQL
    SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  DECLARE v_minLongitude double;
  DECLARE v_maxLongitude double;
  DECLARE v_minLatitude double;
  DECLARE v_maxLatitude double;
  DECLARE v_RectBoundary Polygon;
  
  SET v_minLongitude = p_lon - (p_radiuskilo / abs(cos(radians(p_lat))*111.2));
  SET v_maxLongitude = p_lon + (p_radiuskilo / abs(cos(radians(p_lat))*111.2));
  SET v_minLatitude = p_lat - (p_radiuskilo / 111.2);
  SET v_maxLatitude = p_lat + (p_radiuskilo / 111.2);
  
  SET v_RectBoundary = GeomFromText(concat('POLYGON(('
    ,v_minLongitude,' ',v_minLatitude,', '
    ,v_maxLongitude,' ',v_minLatitude,', '
    ,v_maxLongitude,' ',v_maxLatitude,', '
    ,v_minLongitude,' ',v_maxLatitude,', '
    ,v_minLongitude,' ',v_minLatitude,'))')) ;
    
  RETURN v_RectBoundary;  
END
;;


DELIMITER ;




-- // ------------------------------------------------------------------------------

-- // 사용 예제 --------------------------------------------------------------------

-- // 아래 예제는 원점(위도,경도) = (34.3914636, 132.4625946) 이며, 반경은 3Km 조회

-- // shop_position.location 컬럼은 Point 타입의 컬럼이어야 함
SELECT *
FROM shop_position sp
WHERE
  Contains( GetRectBoundary(34.3914636, 132.4625946, 3), sp.location )
  /* 하단의 조건은 사각 영역이 아니라, 원형으로 반경 검색일 경우 필요한 조건임 */
  and SQRT(POW(X(sp.location) - 132.4625946, 2) + POW(Y(sp.location) - 34.3914636, 2)) < (3 /*Km*/ / 111.2 /*Km*/);