[mariadb]st_distance_sphere 만들어 사용하기

st_distance_sphere 함수 구현해놓은 것이 있길래 가져와서 사용해 봄. 좀 아까 거리 계산하는 싸이트와 거의 일치하는 결과값 나옴 ㅎㅎ ( 참고 : https://stackoverflow.com/questions/44409012/function-st-distance-sphere-does-not-exist-in-mariadb )

FUNCTION ST_Distance_Sphere does not exist in MariaDB

/* create function */
DELIMITER $$

CREATE
    FUNCTION `u_st_distance_sphere`(`pt1` POINT, `pt2` POINT)
    RETURNS DECIMAL(10,2)
    BEGIN
	RETURN 6371000 * 2 * ASIN(SQRT(POWER(SIN((ST_Y(pt2) - ST_Y(pt1)) * PI()/180 / 2), 2) + COS(ST_Y(pt1) * PI()/180 ) * COS(ST_Y(pt2) * PI()/180) * POWER(SIN((ST_X(pt2) - ST_X(pt1)) * PI()/180 / 2), 2) ));
    END$$

DELIMITER ;

SELECT mach_id, ST_X(pt1) AS lat, ST_Y(pt1) AS lng, ST_X(pt2) AS priv_lat, ST_Y(pt2) AS priv_lng
, ST_DISTANCE(pt2, pt1) AS dist, U_ST_DISTANCE_SPHERE(pt2, pt1) AS dist1
FROM
(
SELECT mach_id, mach_pt AS pt1, LAG(mach_pt,1) OVER(PARTITION BY mach_id ORDER BY reg_seq) AS pt2
FROM mach_position
) AS mp;