Last active
December 4, 2017 16:15
-
-
Save viet-wego/34a27243c51a293bb54ddb228a7ab0fe to your computer and use it in GitHub Desktop.
MySQLfunction get distance between 2 points in km
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
use `your_schema`; | |
drop function if exists `get_distance`; | |
delimiter $$ | |
use `your_schema`$$ | |
create function `get_distance` (lat1 decimal(9,6), lng1 decimal(9,6), lat2 decimal(9,6), lng2 decimal(9,6)) returns decimal(10,3) | |
begin | |
declare R decimal(30,15); | |
declare rlat1 decimal(30,15); | |
declare rlat2 decimal(30,15); | |
declare dLat decimal(30,15); | |
declare dLng decimal(30,15); | |
declare distance decimal(30,15); | |
declare a decimal(30,15); | |
declare c decimal(30,15); | |
set R = 6371; -- earth radisu in km | |
set rlat1 = radians(lat1); | |
set rlat2 = radians(lat2); | |
set dLat = radians(lat2-lat1); | |
set dLng = radians(lng2-lng1); | |
set a = sin(dLat/2) * sin(dLat/2) + cos(rlat1) * cos(rlat2) * sin(dLng/2) * sin(dLng/2); | |
set c = 2 * atan2(sqrt(a), sqrt(1-a)); | |
set distance = R * c; | |
return distance; | |
end$$ | |
delimiter; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment