MariaDB. ST_Distance_Sphere does not exist. Let's create!

ST_Distance_Sphere는 MariaDB의 버전을 탄다.

로컬(10.4.32-MariaDB)에서는 ST_Distance_Sphere가 작동해서 테스트를 문제없었지만

서버(10.4.8-MariaDB)에서는 ST_Distance_Sphere does not exist 에러가 발생했다.

아래에 ST_Distance_Sphere 함수 지원에 대한 정보가 있지만 믿을만한지 모르겠다...

https://mariadb.com/kb/en/st_distance_sphere/

ST_DISTANCE_SPHERE

 

없으면 만들어서 사용한다.

함수를 만드는 방법을 찾다가 어떤 블로그에서 하버사인(Haversine) 공식을 이용한 쿼리를 발견했다.

간단하면서도 정리가 잘 되어 있었다.(감사합니다)

-- https://markwatkinson.com/posts/st_distance_sphere-mariadb 에서 퍼옴

DELIMITER $$
DROP FUNCTION IF EXISTS st_distance_sphere;
CREATE FUNCTION st_distance_sphere(pt1 POINT, pt2 POINT)
RETURNS FLOAT

BEGIN 
DECLARE lat1 FLOAT;
DECLARE lat2 FLOAT;
DECLARE lng1 FLOAT;
DECLARE lng2 FLOAT;
DECLARE dlat FLOAT;
DECLARE dlng FLOAT;
DECLARE R FLOAT; 
DECLARE A FLOAT;
DECLARE RADIAN FLOAT;

SET R = 6371000;

SET RADIAN = PI() / 180;

SET lat1 = ST_X(pt1) * RADIAN;
SET lat2 = ST_X(pt2) * RADIAN;
SET lng1 = ST_Y(pt1) * RADIAN;
SET lng2 = ST_Y(pt2) * RADIAN;

SET dlat = lat2 - lat1;
SET dlng = lng2 - lng1;

SET A = SIN(dlat / 2) * SIN(dlat / 2) +
        COS(lat1) *
        COS(lat2) *
        SIN(dlng / 2) * SIN(dlng / 2);
        
RETURN R * 2 * ASIN(SQRT(A));
END$$

 

하지만 사용하려고 쿼리를 보던 중 이부분을 수정해야 하지 않을까 하는 생각이 들었다.

Point(longitude, latitude)이기 때문이다.

 

ST_X와 ST_Y를 바꿔서 lat1, lat2, lng1, lng2의 값을 바꿔주면 더 좋겠다.

완성!

DELIMITER $$
DROP FUNCTION IF EXISTS st_distance_sphere;
CREATE FUNCTION st_distance_sphere(pt1 POINT, pt2 POINT)
RETURNS FLOAT

BEGIN 
DECLARE lat1 FLOAT;
DECLARE lat2 FLOAT;
DECLARE lng1 FLOAT;
DECLARE lng2 FLOAT;
DECLARE dlat FLOAT;
DECLARE dlng FLOAT;
DECLARE R FLOAT; 
DECLARE A FLOAT;
DECLARE RADIAN FLOAT;

SET R = 6371000;

SET RADIAN = PI() / 180;

-- 기존
-- SET lat1 = ST_X(pt1) * RADIAN;
-- SET lat2 = ST_X(pt2) * RADIAN;
-- SET lng1 = ST_Y(pt1) * RADIAN;
-- SET lng2 = ST_Y(pt2) * RADIAN;
-- 수정
SET lat1 = ST_Y(pt1) * RADIAN;
SET lat2 = ST_Y(pt2) * RADIAN;
SET lng1 = ST_X(pt1) * RADIAN;
SET lng2 = ST_X(pt2) * RADIAN;

SET dlat = lat2 - lat1;
SET dlng = lng2 - lng1;

SET A = SIN(dlat / 2) * SIN(dlat / 2) +
        COS(lat1) *
        COS(lat2) *
        SIN(dlng / 2) * SIN(dlng / 2);
        
RETURN R * 2 * ASIN(SQRT(A));
END$$

 

 

 

--------------------------------------------------------

참고

https://en.wikipedia.org/wiki/Haversine_formula

https://stackoverflow.com/questions/44409012/function-st-distance-sphere-does-not-exist-in-mariadb
https://markwatkinson.com/posts/st_distance_sphere-mariadb