Simple Finding distances based on Lat and Long MYSQL

♠ Posted by Unknown in MYSQL at 1:44 AM

Berikut cara simple menghitung jarak dengan input 2 lat long koordinat.
Latihan procedure msql:

Rumus: 
dlon = lon2 - lon1 
dlat = lat2 - lat1 
a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2 
c = 2 * atan2( sqrt(a), sqrt(1-a) ) 
d = R * c (where R is the radius of the Earth)

Procedure mysql:

DELIMITER $$
DROP PROCEDURE IF EXISTS `

SP_RADIUS

`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_RADIUS`(
IN sLatdata CHAR(30), IN sLngdata CHAR(30),
IN dLatdata CHAR(30), IN dLngdata CHAR(30))
BEGIN
SET @sLat = sLatdata * PI()/180;
SET @sLng = sLngdata * PI()/180;
SET @dLat = dLatdata * PI()/180;
SET @dLng = dLngdata * PI()/180;
SET @Rk = 6373;
SET @dlatx = @dLat - @sLat;
SET @dlonx = @dLng - @sLng;
SET @a  = POW(SIN(@dlatx/2),2) + COS(@sLat) * COS(@dLat) * POW(SIN(@dlonx/2),2);
SET @c  = 2 * ATAN2(SQRT(@a),SQRT(1-@a));
SET @dk = @c * @Rk;

SELECT @dk;
END$$
DELIMITER ;

///

CALL 

SP_RADIUS

('138.898556', '-77.037852', '38.897147', '-77.043934');