DEV Community

Cover image for Calculate Distance between 2 Geo Locations in PHP MySQL
Deepak Singh
Deepak Singh

Posted on • Updated on

Calculate Distance between 2 Geo Locations in PHP MySQL

Geolocation serves in a multitude of contexts. In this internet era, everything is on the single palm. Various technology uses various methods to get geolocations and distance between two locations.
We are going to use PHP MySQL as technology to calculate the distance between two geo coordinates.

How can I do that?

Using MySQL Query

So, I have a table geo_cord containing two fields latitude and longitude and I need to calculate the user input distance in km with another user inputs user_latitude and user_longitude.

Image description

Well! we need to write a query:

We need to find all results from Database within user input distance

$distance = 50; // user input distance
$user_latitude = '26.826999'; // user input latitude
$user_longitude = '-158.265114'; // user input logtitude

$sql = "SELECT ROUND(6371 * acos (cos ( radians($user_latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($user_longitude) ) + sin ( radians($user_latitude) ) * sin( radians( latitude ) ))) AS distance,geo_cord.* FROM geo_cord HAVING distance <= $distance";

Enter fullscreen mode Exit fullscreen mode
  • To convert to miles, multiply by 3971.
  • To convert to kilometers, multiply by 6373.
  • To convert to meters, multiply by 6373000.
  • To convert to feet, multiply by (3971 * 5280) 20914080.

There are other methods to achieve the same result, but I prefer this simple method with less coding and quick result.

Using PHP

I have found a formula in Wikipedia Haversine formula which determines the great-circle distance between two points on a sphere given their longitudes and latitudes.

So it also gives result:

function distance($latitude1, $longitude1, $latitude2, $longitude2) { 
        $pi80 = M_PI / 180; 
        $lat1 *= $pi80; 
        $lon1 *= $pi80; 
        $lat2 *= $pi80; 
        $lon2 *= $pi80; 
        $r = 6372.797; // radius of Earth in km 6371
        $dlat = $lat2 - $lat1; 
        $dlon = $lon2 - $lon1; 
        $a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1) * cos($lat2) * sin($dlon / 2) * sin($dlon / 2); 
        $c = 2 * atan2(sqrt($a), sqrt(1 - $a)); 
        $km = $r * $c; 
        return round($km); 
Enter fullscreen mode Exit fullscreen mode


There are simple methods to achieve complex challenges. We just need to convert all challenges into opportunities. 😋

NOTE: NOTE: The map image is only for example purpose. It is not actual output.

Top comments (3)

iacons profile image
Iacovos Constantinou

Another option is to use ST_Distance_Sphere which is available since MySQL 5.7 . The example provided could be rewritten as per below:

$sql = "SELECT ST_Distance_Sphere(point($user_longitude, $user_latitude), point(longitude, latitude) FROM geo_cord WHERE distance <= $distance"
Enter fullscreen mode Exit fullscreen mode
techmesh profile image
Deepak Singh

Indeed... Thanks for the heads up!

marbf profile image

Hi if I apply your formula to calculate the distance between the Netherlands and other countries by using this table
ROUND(6371 * cos (cos ( radians(C.latitude) ) * cos( radians(B.latitude ) ) * cos( radians( B.longitude ) - radians(C.longitude) ) + sin ( radians(C.latitude) ) * sin( radians( B.latitude ) )),2) AS distance
I do not get a correct outcome(e.g. distance between US and the Netherlands is 5190 and distance between UK and the Netherlands is 3472), could you please verify what is wrong? Thank you so much in advance.