Write a query to find the distance between two points in MySql
Example:
Table:LatutudeAndLongitudeID sLatitude sLongitude eLatitude eLongitude distanceKM distanceMI1 28.5746 77.3561 28.617 77.3736 5.01 3.112 28.617 77.3736 27.6247 78.1373 133.27 82.81
Approach
Applying the Haversine Formula to MySQL
Calculating the distance between two locations using the Haversine Formula in MySQL requires us to call upon several of MySQL’s built-in Math functions, including cos(), sin(), acos(), and radians(). In case you were wondering like I was, the radians() function converts the value of a number from degrees to radians where pi radians equals 180 degrees.
R = 6367 km
R = 3956 mi (Miles)
Formula :
R* acos( cos( radians(lat1) ) * cos( radians(lat2) ) * cos( radians(lon2) - radians(lon1)) + sin(radians(lat1)) * sin( radians(lat2) )
Function for calculate distance between two point
CREATE DEFINER=`root`@`%` FUNCTION `distanceCalculateI`(sLatitude double, end_location_id double,eLatitude double, eLongitude double,convertType varchar(2)) RETURNS doubleDETERMINISTICBEGINDECLARE distance double default 0;# R = 6367 km OR 3956 miif(convertType='KM') ThenSELECT (6367 * acos(cos( radians(eLatitude) )* cos( radians( sLatitude ) )* cos( radians( sLongitude ) - radians(eLongitude) )+ sin( radians(eLatitude) )* sin( radians( sLatitude ) )) ) INTO distance;elseSELECT (3956 * acos(cos( radians(eLatitude) )* cos( radians( sLatitude ) )* cos( radians( sLongitude ) - radians(eLongitude) )+ sin( radians(eLatitude) )* sin( radians( sLatitude ) )) ) INTO distance;end if;RETURN distance;ENDFor distance in Miles
select distanceCalculateI(sourceLatitude,sourceLongitude,destinationLatitude,DestinationLongitude,'MI');
For distance in KM
select distanceCalculateI(sourceLatitude,sourceLongitude,destinationLatitude,DestinationLongitude,'KM');
Example:
select distanceCalculateI(28.5746, 77.3561,28.617,77.3736,'KM');
select distanceCalculateI(28.5746, 77.3561,28.617,77.3736,'MI');
select distanceCalculateI(27.6247, 78.1373,28.617,77.3736,'KM');
select distanceCalculateI(27.6247, 78.1373,28.617,77.3736,'MI');
No comments:
Post a Comment