Sign up to get extra content & updates via email!

Thanks for signing up!
I appreciate it!

Getting Distance between 2 points with MySQL Query


Calculating the distance between 2 points (longitude and latitude) certainly sounds like something that should be solved on the code layer, not on the database layer.

However, more often than not, it’s a lot faster to write it inside a query and get the result for a big list of locations.

So how is it done?
By using the Haversine formula (it’s not hard, I swear).
To calculate the great-circle distance of two points on a sphere
(for more information, please go to your local library, if year > 2004, check wikipedia)

The implementation of which (in a MySQL query), looks like this:

SELECT 6353 * 2 * ASIN(SQRT( POWER(SIN((lat1 -
abs(lat2)) * pi()/180 / 2),2) + COS(lat1 * pi()/180 ) * COS( 
abs(lat2) *  pi()/180) * POWER(SIN((long1 - long2) *  pi()/180 / 2), 2) ))

(6353 – Is the Earth’s Radius (in KM))
You can test it out, for instance, let’s get the distance between
the Eiffel Tower (48.858278,2.294254) and Big Ben (51.500705,-0.124575),
which should be about 340 KM aerial distance (man Europe is small!)
so running our query with the Longitudes and Latitudes set:

SELECT ROUND(6353 * 2 * ASIN(SQRT( POWER(SIN((48.858278 -
abs(51.500705)) * pi()/180 / 2),2) + COS(48.858278 * pi()/180 ) * COS( 
abs(51.500705) *  pi()/180) * POWER(SIN((2.294254 - -0.124575) *  pi()/180 / 2), 2) )), 2)

gets us 339.58.
As you can see I used the ROUND(..,2) function to make the result a bit more readable.

If you want to use this often, you might want to write a function that calculates distance:

CREATE FUNCTION calc_distance (lat1 DECIMAL(10,6), long1 DECIMAL(10,6), lat2 DECIMAL(10,6), long2 DECIMAL(10,6))
RETURNS DECIMAL(10,6)
RETURN (6353 * 2 * ASIN(SQRT( POWER(SIN((lat1 - abs(lat2)) * pi()/180 / 2),2) + COS(lat1 * pi()/180 ) * COS( abs(lat2) *  pi()/180) * POWER(SIN((long1 - long2) *  pi()/180 / 2), 2) )))

SELECT ROUND(calc_distance(51.500705,-0.124575,48.858278,2.294254), 2) --339.58

That’s better, now we can just use the function in queries to quickly calculate distances.

As a side note:
I do highly recommend using MySQL Spatial extensions for more complex calculations, though
honestly it seems that PostgreSQL has the upper hand in that area.

Posted in Technology
Welcome to KidsIL
A blog for Web Development & Technology


Check out my new series about MeanJS: Take a look at StartCast.
A podcast with the sole purpose of interviewing co-founders of Startups in Europe & around the world.


You should try Berlin On Feier, an App I built for finding the best parties in Berlin.