PHP and MySQL: Use the haversine formula to calculate the distance between two co-ordinates

April 03, 2015

I’m currently building a social media platform where members can find other users based on the distance between them.

The users have already provided their addresses, so how can we reliably calculate the distance between them?

You could use Google’s Distance Matrix API, but that would result in a lot of queries and expense, and it’s also not very searchable.

Enter the haversine formula.

What is the haversine formula?

The haversine formula is used to calculate the shortest distance between two points on the surface of a sphere (or a globe!), given their longitude and latitude.

For the more mathematically minded, the formula is described in greater detail on the haversine formula Wikipedia page.

Haversine in PHP

Here’s an implementation of the haversine formula in PHP. In the function, 6371 is the radius of the Earth in km. You could adapt your usage to 3390 for Mars or 1738 for the moon! But let’s stick to the Earth for now.

function calculateHaversineDistance(
	$latitudeFrom, 
	$longitudeFrom, 
	$latitudeTo, 
	$longitudeTo, 
	$radius = 6371
) {
  $latFrom = deg2rad($latitudeFrom);
  $lngFrom = deg2rad($longitudeFrom);
  $latTo = deg2rad($latitudeTo);
  $lngTo = deg2rad($longitudeTo);

  $latDelta = $latTo - $latFrom;
  $lngDelta = $lngTo - $lngFrom;

  $angle = 2 * asin(sqrt(pow(sin($latDelta / 2), 2) +
    cos($latFrom) * cos($latTo) * pow(sin($lngDelta / 2), 2)));
		
  return $angle * $radius;
}

You can use this function to calculate the distance between two points as follows:

$latitudeFrom = 51.5072;
$longitudeFrom = 0.1276;
$latitudeTo = 53.7676;
$longitudeTo = 0.3274;

$distance = calculateHaversineDistance($latitudeFrom, $longitudeFrom, $latitudeTo, $longitudeTo);

echo $distance . "km"; // 251.70606865073km

You may want to round the result to the nearest whole km or 100m:

echo round($distance) . 'km'; // 252km
echo number_format($distance, 1) . "km"; // 251.7km

This is great, but it only works for co-ordinates you have already fetched. What if we wanted to query a MySQL database to find other users within 50km?

Haversine in MySQL

Implementing the haversine formula in MySQL is very similar. Here’s a raw query that would fetch all addresses within 50km from a table addresses with the columns latitude and longitude:

SELECT *, 
    (
        6371 * acos (
        cos ( radians(51.5072) )
        * cos( radians( latitude ) )
        * cos( radians( longitude ) - radians(0.1276) )
        + sin ( radians(51.5072) )
        * sin( radians( latitude ) )
        )
    ) AS distance
FROM addresses
HAVING distance < 50
ORDER BY distance
LIMIT 0 , 20;

Here’s the same thing with Laravel’s query builder:

$lat = 51.5072;
$lng = 0.1276;
$distance = 50; // The radius in km

$addresses = DB::table('addresses')
    ->select(DB::raw("*,
        ( 6371 * acos( cos( radians(?) ) *
          cos( radians( latitude ) )
          * cos( radians( longitude ) - radians(?)
          ) + sin( radians(?) ) *
          sin( radians( latitude ) ) )
        ) AS distance", [$lat, $lng, $lat]))
    ->having('distance', '<', $distance)
    ->orderBy('distance')
    ->limit(20)
    ->get();

The query calculates the distance and uses it as a virtual column so you can use it in other aspects of the query, such as ordering. In the example, we’re selecting the closest 20 addresses and ordering them by nearest first.

When using the query builder, it will be available as a property on the result object:

foreach ($addresses as $address) {
    echo $address->distance . "km away\n";
}