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://linux.hoit.asia/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 :
