Skip to content

Instantly share code, notes, and snippets.

@Qwerios
Created March 14, 2016 14:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Qwerios/143f3208ee4166e3c4b6 to your computer and use it in GitHub Desktop.
Save Qwerios/143f3208ee4166e3c4b6 to your computer and use it in GitHub Desktop.
DELIMITER $$
CREATE FUNCTION `fnHaversine`(point1 point, point2 point) RETURNS double
DETERMINISTIC
begin
declare lon1, lon2 double;
declare lat1, lat2 double;
declare r_lat1, r_lat2 double;
declare d_lat double;
declare d_lon double;
declare a, c, R double;
-- Earth radius in KM
set R = 6371000;
set lat1 = ST_X( point1 );
set lat2 = ST_X( point2 );
set lon1 = ST_Y( point1 );
set lon2 = ST_Y( point2 );
set r_lat1 = radians( lat1 );
set r_lat2 = radians( lat2 );
set d_lat = radians( lat2 - lat1 );
set d_lon = radians( lon2 - lon1 );
set a = sin( d_lat / 2 ) * sin( d_lat / 2 ) + cos( r_lat1 ) * cos( r_lat2 ) * sin( d_lon / 2 ) * sin( d_lon / 2 );
set c = 2 * atan2( sqrt( a ), sqrt( 1 - a ) );
return R * c;
end$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment