MySQL Calculate Distance & Bearing from Two Point Latitude & Longitude

Berikut adalah query mysql untuk menghitung jarak dari 2 point (latitude & longitude) dan menghitung bearing dari 2 point.


-- To convert to miles, multiply by 3959.
-- To convert to kilometers, multiply by 6371.
-- To convert to meters, multiply by 6371000.
-- To convert to feet, multiply by (6371000 * 3.28084) or 20902231.64.

-- Reference : 
-- https://farizfadian.blogspot.com/2019/02/mysql-calculate-distance-and-bearing-from-two-point-latitude-and-longitude.html
-- https://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql
-- https://stackoverflow.com/questions/24099740/can-mysql-determine-bearing-between-two-records-with-latitude-and-longitude


DROP FUNCTION IF EXISTS GEO_DISTANCE;
DROP FUNCTION IF EXISTS GEO_DISTANCE_KM;
DROP FUNCTION IF EXISTS GEO_DISTANCE_METERS;
DROP FUNCTION IF EXISTS GEO_DISTANCE_MILES;
DROP FUNCTION IF EXISTS GEO_DISTANCE_FEETS;
DROP FUNCTION IF EXISTS GEO_BEARING;

DELIMITER $$

-- DISTANCE
CREATE FUNCTION `GEO_DISTANCE` ( `lat1` CHAR(50), `lng1` CHAR(50), `lat2` CHAR(50), `lng2` CHAR(50) ) RETURNS CHAR(50) CHARSET utf8 
DETERMINISTIC 
COMMENT 'Returns the distance in degrees on the Earth between two known points of latitude and longitude. To get miles, multiply by 3959, km by 6371, meters by 6371000 and feets by (6371000 * 3.28084) or 20902231.64'
BEGIN
RETURN ACOS(
COS( RADIANS( lat2 ) ) 
* COS( RADIANS( lat1 ) ) 
* COS( RADIANS( lng1 ) - RADIANS( lng2 ) ) 
+ SIN( RADIANS( lat2 ) )
* SIN( RADIANS( lat1 ) )
); 
END$$

-- KILOMETERS
CREATE FUNCTION `GEO_DISTANCE_KM` ( `lat1` CHAR(50), `lon1` CHAR(50), `lat2` CHAR(50), `lon2` CHAR(50) ) RETURNS CHAR(50) CHARSET utf8 
DETERMINISTIC 
BEGIN
    RETURN GEO_DISTANCE(lat1, lon1, lat2, lon2) * 6371;
END$$

-- MILES
CREATE FUNCTION `GEO_DISTANCE_MILES` ( `lat1` CHAR(50), `lon1` CHAR(50), `lat2` CHAR(50), `lon2` CHAR(50) ) RETURNS CHAR(50) CHARSET utf8 
DETERMINISTIC 
BEGIN
    RETURN GEO_DISTANCE(lat1, lon1, lat2, lon2) * 3959;
END$$

-- METERS
CREATE FUNCTION `GEO_DISTANCE_METERS` ( `lat1` CHAR(50), `lon1` CHAR(50), `lat2` CHAR(50), `lon2` CHAR(50) ) RETURNS CHAR(50) CHARSET utf8 
DETERMINISTIC 
BEGIN
    RETURN GEO_DISTANCE(lat1, lon1, lat2, lon2) * 6371000;
END$$

-- FEETS
CREATE FUNCTION `GEO_DISTANCE_FEETS` ( `lat1` CHAR(50), `lon1` CHAR(50), `lat2` CHAR(50), `lon2` CHAR(50) ) RETURNS CHAR(50) CHARSET utf8 

DETERMINISTIC 
BEGIN
    RETURN GEO_DISTANCE(lat1, lon1, lat2, lon2) * 20902231.64;
END$$

-- BEARING
CREATE FUNCTION `GEO_BEARING` ( `lat1` CHAR(50), `lon1` CHAR(50), `lat2` CHAR(50), `lon2` CHAR(50) ) RETURNS CHAR(50) CHARSET utf8 
DETERMINISTIC 
COMMENT 'Returns the initial bearing, in degrees, to follow the great circle route
             from point (lat1,lon1), to point (lat2,lon2)' 
    BEGIN
    RETURN (360.0 + DEGREES(
    ATAN2 ( SIN ( RADIANS ( lon2 - lon1 ) ) * 
    COS ( RADIANS ( lat2) ),
    COS ( RADIANS ( lat1 ) ) * SIN ( RADIANS ( lat2 ) ) - SIN ( RADIANS ( lat1 ) ) * 
    COS ( RADIANS ( lat2 ) ) * COS ( RADIANS ( lon2 - lon1 ) ) 
    ) ) 
    ) % 360.0;
END$$

DELIMITER ; 


-- TEST
SELECT 
GEO_DISTANCE_KM(-6.2410127, 106.7841999, -6.2397986, 106.783651) AS DISTANCE_KM,
GEO_DISTANCE_METERS(-6.2410127, 106.7841999, -6.2397986, 106.783651) AS DISTANCE_METERS,
GEO_DISTANCE_MILES(-6.2410127, 106.7841999, -6.2397986, 106.783651) AS DISTANCE_MILES,
GEO_DISTANCE_FEETS(-6.2410127, 106.7841999, -6.2397986, 106.783651) AS DISTANCE_FEETS,
GEO_BEARING(-6.2410127, 106.7841999, -6.2397986, 106.783651) AS BEARING;


Result :