Technology

PHP and MySQL: Calculating Distance

This month I’ve been programming quite a bit in PHP and MySQL with respect to GIS. Snooping around the net, I actually had a hard time finding some of the Geographic calculations to find the distance between two locations so I wanted to share them here.

Flight Map EuropeIf you remember ‘the old days’ of calculating a distance between two points, it was simply the hypotenuse of a triangle (A² + B² = C²).

That’s an interesting start but it doesn’t apply with Geography since the distance between lines of latitude and longitude are not an equal distance apart. As you get closer to the equator, lines of latitude get further 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.

That brings up the Haversine formula, which uses trigonometry to allow for the curvature of the earth. When you’re finding the distance between 2 places on earth (as the crow flies), a straight line is really an arc. This is applicable in air flight – have you ever looked at the actual map of flights and noticed they are arched? That’s because it’s shorter to fly in an arch between two points sometimes than directly to the location.

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."

I utilized similar computations in the Wild Birds Unlimited mapping platform that we built.

Download a Sponsored Marketing Whitepaper:
The 6th Annual B2B Marketing Data Report

The 6th Annual B2B Marketing Data Report

Unlocking the New Formula for Sales and Marketing Success. Download Now

76 Comments

  1. 1

    Thank you very much for sharing. This was an easy copy and paste job and works great. You’ve saved me alot of time.
    FYI for anyone porting to C:
    double deg2rad(double deg) { return deg*(3.14159265358979323846/180.0); }

  2. 2

    Very nice piece of posting – worked very nice – I only had to change the name of the table holding the lat-long. It works pretty fast to.. I have a reasonably small number of lat-longs (< 400) but I think this would scale nicely. Nice site too – i have just added it to my del.icio.us account and will check back regularly.

  3. 4
  4. 5
  5. 8
  6. 10
  7. 11
  8. 12

    Thanks so much for sharing this code. It saved me a lot of development time. Also, thanks to your readers for pointing out that a HAVING statement is necessary for MySQL 5.x. Very helpful.

  9. 14
  10. 15
  11. 16

    I also found that WHERE did not work for me. Changed it to HAVING and everything works perfect. At first i didnt read the comments and rewrote it using a nested select. Both will work just fine.

  12. 17
  13. 18

    Incredibly helpful, thank you very much! I was having some problems with the new “HAVING”, rather than “WHERE”, but once I read the comments here (after about half an hour of grinding my teeth in frustration =P), I got it working nicely. Thank you ^_^

  14. 19
  15. 20

    Keep in mind that a select statement like that will be very computationally intense and therefore slow. If you have a lot of those queries, it can bog things down quite quickly.

    A much less intense approach is to run a first (crude) select using a SQUARE area defined by a calculated distance i.e. “select * from tablename where latitude between lat1 and lat2 and longitude between lon1 and lon2”. lat1 = targetlatitude – latdiff, lat2 = targetlatitude + latdiff, similar with lon. latdiff ~= distance / 111 (for km), or distance/69 for miles since 1 degree of latitude is ~ 111 km (slight variation since earth is slightly oval, but sufficient for this purpose). londiff = distance / (abs(cos(deg2rad(latitude))*111)) — or 69 for miles (you can actually take a slightly larger square in order to account for variations). Then take the result of that and feed it into the radial select. Just don’t forget to account for out-of-bounds coordinates – i.e. the range of acceptable longitude is -180 to +180 and the range of acceptable latitude is -90 to +90 — in case your latdiff or londiff runs outside this range. Note that in most cases this may not be applicable since it only affects calculations over a line through the pacific ocean from pole to pole, though it does intersect part of chukotka and part of alaska.

    What we accomplish by this is a significant reduction in the number of points against which you make this calculation. If you have a million global points in the database distributed roughly evenly and you want to search within 100 km, then your first (fast) search is of an area 10000 sq km and will probably yield about 20 results (based on even distribution over a surface area of about 500M sq km), which means that you run the complex distance calculation 20 times for this query instead of a million times.

    • 21
      • 22

        Fantastic advice! I actually worked with a developer who wrote a function that pulled the inside square and then a recursive function that made ‘squares’ around the perimeter to include and exclude the remaining points. The result was an incredibly fast result – he could evaluate millions of points in microseconds.

        My approach above is definitely ‘crude’ but capable. Thanks again!

        • 23

          Doug,

          I have been trying to use mysql and php to evaluate whether a lat long point is within a polygon. Do you know if your developer friend published any examples on how to accomplish this task. Or do you know any good examples. Thanks in advance.

  16. 24

    Hi everyone this is my test SQL statement:

    SELECT DISTINCT area_id, (
    (
    (
    acos( sin( ( 13.65 * pi( ) /180 ) ) * sin( (
    `lat_dec` * pi( ) /180 ) ) + cos( ( 13.65 * pi( ) /180 ) ) * cos( (
    `lat_dec` * pi( ) /180 )
    ) * cos( (
    ( 51.02 - `lon_dec` ) * pi( ) /180 )
    )
    )
    ) *180 / pi( )
    ) *60 * 1.1515 * 1.609344
    ) AS distance
    FROM `post_codes` WHERE distance <= 50

    and Mysql is telling me that distance, doesn’t exist as a column, i can use order by, i can do it without WHERE, and it works, but not with it…

  17. 26

    This is great, however it is just as the birds fly. It would be great to try and incorporate the google maps API to this somehow (maybe using roads etc.) Just to give an idea using a different form of transportation. I still have yet to make a simulated annealing function in PHP that would be able to offer an efficient solution to the traveling salesman problem. But I think that I may be able to reuse some of your code to do so.

  18. 27
  19. 28
  20. 29
  21. 30
  22. 31
  23. 32
  24. 36

    2 days of research to finally find this page that solves my problem. Looks like i better bust out my WolframAlpha and brush up on my maths. The change from WHERE to HAVING has my script in working order. THANK YOU

  25. 37
  26. 39

    I wish this was the first page i’d found on this. After trying many different commands this was the only one to work properly, and with minimal changes needed to fit my own database.
    Thanks a lot!

  27. 40

    I wish this was the first page i’d found on this. After trying many different commands this was the only one to work properly, and with minimal changes needed to fit my own database.
    Thanks a lot!

  28. 41
  29. 42
  30. 43
  31. 45
  32. 46
  33. 47
  34. 49
  35. 50
  36. 52
  37. 53