Calculate/measure distance between two poins using Great Circle Distance algorithm.

Code translate from geopy http://code.google.com/p/geopy/.

DELIMITER ;;
DROP FUNCTION IF EXISTS `gcd2`;;
 
CREATE FUNCTION `gcd2`(longitude0 DOUBLE, latitude0 DOUBLE, longitude1 DOUBLE, latitude1 DOUBLE, metric VARCHAR(2)) RETURNS DOUBLE
    DETERMINISTIC
    COMMENT 'Great Circle Distance http://code.google.com/p/geopy/'
BEGIN
DECLARE gcdx DOUBLE;
DECLARE lng_rad0 DOUBLE;
DECLARE lat_rad0 DOUBLE;
DECLARE lng_rad1 DOUBLE;
DECLARE lat_rad1 DOUBLE;
DECLARE sin_lat0 DOUBLE;
DECLARE cos_lat0 DOUBLE;
DECLARE sin_lat1 DOUBLE;
DECLARE cos_lat1 DOUBLE;
DECLARE delta_lng DOUBLE;
DECLARE cos_delta_lng DOUBLE;
DECLARE sin_delta_lng DOUBLE;
DECLARE central_angle DOUBLE;
 
DECLARE earth_radius DOUBLE;
 
SET earth_radius := 6372.795;
 
SET lng_rad0 := RADIANS(longitude0);
SET lat_rad0 := RADIANS(latitude0);
SET lng_rad1 := RADIANS(longitude1);
SET lat_rad1 := RADIANS(latitude1);
 
SET sin_lat0 := SIN(lat_rad0);
SET cos_lat0 := COS(lat_rad0);
SET sin_lat1 := SIN(lat_rad1);
SET cos_lat1 := COS(lat_rad1);
 
SET delta_lng := lng_rad1 - lng_rad0;
SET cos_delta_lng := COS(delta_lng);
SET sin_delta_lng := SIN(delta_lng);
 
SET central_angle := acos(sin_lat0 * sin_lat1 + cos_lat0 * cos_lat1 * cos_delta_lng);
 
SET gcdx := atan2(sqrt(POW((cos_lat1 * sin_delta_lng), 2) + POW((cos_lat0 * sin_lat1 - sin_lat0 * cos_lat1 * cos_delta_lng), 2)), sin_lat0 * sin_lat1 + cos_lat0 * cos_lat1 * cos_delta_lng);
 
IF metric = 'km' THEN
 RETURN gcdx * earth_radius;
ELSEIF metric = 'mi' THEN
 RETURN (gcdx * earth_radius) * 0.621371192;
ELSEIF metric = 'nm' THEN
 RETURN (gcdx * earth_radius) / 1.852;
ELSE
 RETURN gcdx;
END IF;
END;;
 
DELIMITER ;

Current Tags

You must be logged in to tag this tool

No Comments yet