Skip to content

Calculate Distance using Latitude and Longitude: PHP & Mysql

June 30, 2009

This is a Geographic calculations to find the distance between two locations. If you remember the claculation of distance between two points, it was simply the hypotenuse of a triangle (A² + B² = C²), but it doesn’t apply with Geography since the distance between lines of latitude and longitude are not an equal distance apart. If you use some kind of simple triangulation equation, it may measure distance accurately in one location and terribly wrong in the other, because of the curvature of the Earth.

Anyways, here’s the PHP formula for calculating the distance between two points (along with Mile vs. Kilometer conversion) rounded to two decimal places:

function getDistanceBetweenPointsNew($latitude1, $longitude1,
$latitude2, $longitude2, $unit = 'Mi')
{
   $theta = $longitude1 - $longitude2;
   $distance = (sin(deg2rad($latitude1)) *
   sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) *
   cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
   $distance = acos($distance);
   $distance = rad2deg($distance);
   $distance = $distance * 60 * 1.1515;
   switch($unit)
   {
      case 'Mi': break;
      case 'Km' : $distance = $distance *1.609344;
   }
   return (round($distance,2));
}

It’s also possible to use MySQL to do a calculation to find all records within a specific distance. In this example, I’m going to query MyTable to find all the records that are less than or equal to variable $distance (in Miles) to my location at $latitude and $longitude:

$qry = "SELECT *,(((acos(sin((".$latitude."*pi()/180)) * 
sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * 
cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)*
pi()/180))))*180/pi())*60*1.1515) as distance FROM `MyTable` 
WHERE distance <= ".$distance."

For Kilometers:

$qry = "SELECT *,(((acos(sin((".$latitude."*pi()/180)) * 
sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * 
cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)
*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM 
`MyTable` WHERE distance <= ".$distance." 
About these ads

From → Google API

8 Comments
  1. You can’t use a column alias in a where clause, at least not on my version of mysql. Instead, I used a “HAVING” clause and things worked splendidly. Thank you for this!

  2. Aaron permalink

    Hi, Aravind. I’m glad I found your code because this is precisely what I need for a project I’m working on.

    I just need to know where specifically in your code are the fields defined for latitude and longitude? Is it in these statements “(`Latitude`*pi()/180)” ?

    • Hi Aaron, Thanks a lot…
      The `Latitude` is the column name of the table and the ‘$latitude’ is the exact latitude, where you convert from the address entered in any of the form detail.

  3. Aaron permalink

    One more thing. How would I define the distance variable?

  4. Aaron Johnson permalink

    Hi, Aravind,

    I’m getting this error Unknown column ‘distance’ in ‘where clause’

    From what I understand, I shouldn’t be getting this error because the query is configured to transform the latitude and longitude fields and select AS distance.

    I’m trying it out on an empty database. Is that the reason?

    • Hi Aaron,

      if you get such error, just try the following,

      $qry = “SELECT *,(((acos(sin((“.$latitude.”*pi()/180)) *
      sin((`Latitude`*pi()/180))+cos((“.$latitude.”*pi()/180)) *
      cos((`Latitude`*pi()/180)) * cos(((“.$longitude.”- `Longitude`)
      *pi()/180))))*180/pi())*60*1.1515*1.609344) FROM
      `MyTable` WHERE (((acos(sin((“.$latitude.”*pi()/180)) *
      sin((`Latitude`*pi()/180))+cos((“.$latitude.”*pi()/180)) *
      cos((`Latitude`*pi()/180)) * cos(((“.$longitude.”- `Longitude`)
      *pi()/180))))*180/pi())*60*1.1515*1.609344) <= ".$distance."

      This will definitely work or else as eddie said you can also use HAVING like the following,

      $qry = "SELECT *,(((acos(sin((".$latitude."*pi()/180)) *
      sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) *
      cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)
      *pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM
      `MyTable` HAVING distance <= ".$distance."

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: