Problem Statement

The haversine formula is an equation important in navigation, giving great-circle distances between two points on a sphere from their longitudes and latitudes. It is a special case of a more general formula in spherical trigonometry, the law of haversines, relating the sides and angles of spherical "triangles".

Implement a great-circle distance function, or use a library function, to show the great-circle distance between:

Most of the examples below adopted Kaimbridge's recommended value of 6372.8 km for the earth radius. However, the derivation of this ellipsoidal quadratic mean radius is wrong (the averaging over azimuth is biased). When applying these examples in real applications, it is better to use the mean earth radius, 6371 km. This value is recommended by the International Union of Geodesy and Geophysics and it minimizes the RMS relative error between the great circle and geodesic distance.

Let's start with the solution:

DELIMITER $$

CREATE FUNCTION haversine (
		lat1 FLOAT, lon1 FLOAT,
		lat2 FLOAT, lon2 FLOAT
	) RETURNS FLOAT
	NO SQL DETERMINISTIC
BEGIN
	DECLARE r FLOAT unsigned DEFAULT 6372.8;
	DECLARE dLat FLOAT unsigned;
	DECLARE dLon FLOAT unsigned;
	DECLARE a FLOAT unsigned;
	DECLARE c FLOAT unsigned;
	
	SET dLat = ABS(RADIANS(lat2 - lat1));
	SET dLon = ABS(RADIANS(lon2 - lon1));
	SET lat1 = RADIANS(lat1);
	SET lat2 = RADIANS(lat2);
	
	SET a = POW(SIN(dLat / 2), 2) + COS(lat1) * COS(lat2) * POW(SIN(dLon / 2), 2);
	SET c = 2 * ASIN(SQRT(a));
	
	RETURN (r * c);
END$$

DELIMITER ;