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.

## Leave a Reply