Calculating the distance between two GPS points in MySQL

There have been many tutorials floating around the ‘net for a while, detailing how to calculate the distance between an entry in your database, and a set of arbitrary GPS points. Not many of these tutorials will allow you to find the distance between two random points you might have.

In order to solve this (and remove the use of the rather large clumsy SQL calculation), I have re-written this MySQL calculation into a function, for easier reference.

DELIMITER $$
CREATE FUNCTION `CALCULATE_DISTANCE`(`@oLat` DECIMAL(10,7), `@oLon` DECIMAL(10,7), `@dLat` DECIMAL(10,7), `@dLon` DECIMAL(10,7))
    RETURNS DECIMAL(10,7)
    NO SQL
    BEGIN
	RETURN (
		(
			ACOS(
				SIN(`@dLat` * PI() / 180) *
				SIN(`@oLat` * PI() / 180) +
				COS(`@dLat` * PI() / 180) *
				COS(`@oLat` * PI() / 180) *
				COS((`@dLon` - `@oLon`) * PI() / 180)
			) *
			180 / PI()
		) *
		60 *
		1.1515 *
		1.609344
	);
    END$$
DELIMITER ;

The format of the arguments are as follows:

So, how do I use it?

It’s really easy. Simply execute the SQL provided above. If your database user doesn’t have the necessary privileges to be able to create functions, then you won’t be able to use this. After having run the SQL, you can find the distance between two arbitrary points like that shown below:

SELECT CALCULATE_DISTANCE(-33.91429, 18.42389, -34.079120, 18.446882);

Thanks to zcentric.com for the SQL calculation.