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:
|@oLat||The latitude from which the distance will be calculated.|
|@oLon||The longitude from which the distance will be calculated.|
|@dLat||The latitude to which the distance will be calculated.|
|@dLon||The longitude to which the distance will be calculated.|
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.