# PHP and SQL: Calculate or Query Great Circle Distance Between Points of Latitude and Longitude With The Haversine Formula 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.

The simple way of calculating a distance between two points is using the Pythagorean formula to calculate the hypotenuse of a triangle (A² + B² = C²). This is known as the Euclidean distance.

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.

### Great Circle Distance

The routes that are traveled long distances around the Earth are known as the Great Circle Distance. That is… the shortest distance between two points on a sphere is different than the points in a flat map. Combine that with the fact that the latitude and longitude lines aren't equidistant… and you've got a difficult calculation.

Here's a fantastic video explanation of how Great Circles work.

### The Haversine Formula

The distance using the curvature of the Earth is incorporated in 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 than directly to the location.

### PHP: Calculate Distance Between 2 Points of Latitude and Longitude

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 = 'miles') {
\$theta = \$longitude1 - \$longitude2;
\$distance = acos(\$distance);
\$distance = \$distance * 60 * 1.1515;
switch(\$unit) {
case 'miles':
break;
case 'kilometers' :
\$distance = \$distance * 1.609344;
}
return (round(\$distance,2));
}``````

### SQL: Retrieving All Records Within A Range By Calculating Distance In Miles Using Latitude and Longitude

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

The query for retrieving all of the records within a specific distance by calculating distance in miles between two points of latitude and longitude are:

``\$query = "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 `table` WHERE distance <= ".\$distance."``

You'll need to customize this:

• \$longitude – this is a PHP variable where I'm passing the longitude of the point.
• \$latitude – this is a PHP variable where I'm passing the longitude of the point.
• \$distance – this is the distance that you would like to find all the records less or equal to.
• table – this is the table… you'll want to replace that with your table name.
• latitude – this is the field of your latitude.
• longitude – this is the field of your longitude.

### SQL: Retrieving All Records Within A Range By Calculating Distance In Kilometers Using Latitude and Longitude

And here's the SQL query using kilometers in MySQL:

``\$query = "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 `table` WHERE distance <= ".\$distance."``

You'll need to customize this:

• \$longitude – this is a PHP variable where I'm passing the longitude of the point.
• \$latitude – this is a PHP variable where I'm passing the longitude of the point.
• \$distance – this is the distance that you would like to find all the records less or equal to.
• table – this is the table… you'll want to replace that with your table name.
• latitude – this is the field of your latitude.
• longitude – this is the field of your longitude.

I utilized this code in an enterprise mapping platform that we utilized for a retail store with over 1,000 locations across North America and it worked beautifully.

1. 1 Kerry

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 Peter M

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 Douglas Karr

Thanks very much Peter and Kerry! If you like working on GIS projects, I’d recommend:

1. Taking a look at my GIS category
2. And definitely check out Mapping News by Mapperz
3. 4 irmanator

Thank you very much… 😀

4. 5 rails friend

I searched the whole day for distance calculations and found the harversine algorithm, thanks to you for giving the example on how to put it in an sql statement. Thanks and greets, Daniel

• 6 Douglas Karr

Glad to help out, rails friend!

Now I’m in search of an ‘in Polygon’ PHP function that will take an array of sequenced latitude and longitude coordinates and figure out if another point is within or outside of the polygon.

• 7 Douglas Karr
5. 8 david chan

i think your SQL needs a having statement.
instead of WHERE distance <= \$distance you might need to
use HAVING distance <= \$distance

otherwise thanks for saving me a bunch of time and energy.

• 9 Douglas Karr

Hi David,

If you’re doing any type of GROUP BY statement, you will need HAVING. I am not doing that in the example above.

Doug

6. 10 John Burch

As of MySQL 5.x, you can’t use an alias on a WHERE clause see http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Use HAVING instead of WHERE in the above querys

7. 11 Salman

Thank you very much. You have done great job That’s the thing what i actually want. Thanks alot.

8. 12 Grant

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.

• 13 Douglas Karr

I’m blessed to have readers much smarter than I am!

🙂

9. 14 Harry Hobson

The above formula is saving me a lot of time. Thank you very much.
I also have to switch between the NMEA format and Degrees. I found a formula at this URL at the bottom of the page. http://www.errorforum.com/knowledge-base/16273-converting-nmea-sentence-latitude-longitude-decimal-degrees.html

Does anyone know how to verify this?

Thank you!
Harry

10. 15 Harry Hobson

Hello,

Another question. Is there a formula for NMEA strings like the one below ?

1342.7500,N,10052.2287,E

\$GPRMC,032731.000,A,1342.7500,N,10052.2287,E,0.40,106.01,101106,,*0B

Thanks,
Harry

11. 16 John

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 GoZilla

Thank you very mouch for the script written in mysql, just had to make few minor adjustments (HAVING) 🙂
Gret job

13. 18 Robbie

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 tamik

thanks a lot works great

15. 20 Somebody

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 Somebody

Minor mistake in the example… that would be for within 50 km (not 100) since we are looking at the “radius” of our… square.

• 22 Douglas Karr

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 John

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 Matis

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…

• 25 Ace

Replace “WHERE distance” with “HAVING distance”.
Works like a charm, thanks, Douglas!

17. 26 matthew

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 nimrod

Hi Douglas,
thank you so much for this article – you just saved me alot of time.
take care,
nimrod @Israel

19. 28 Daniele Demichelis

Good article! I found a lot of articles describing how to compute distance between two points but I was really looking for the SQL snippet.

20. 29 Phplokesh

Thanks a lot works good

21. 30 drvinnie

Thank you much for this formula. It shaved some time on a store location project that was eating at me.

22. 31 drvinnie

Thanks a bundle. This little line of code saved me some considerable time in a store location project!

23. 32 Nospam8278

#1054 – Unknown column ‘distance’ in ‘where clause’

• 33 Douglas Karr

approve

• 34 David Weimer

Same here! What’s the problem :-/? how to solve the “distance” – Column problem? Help us, please!! 🙂

• 35 Anonymous

Try using HAVING instead of WHERE

24. 36 eric

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 Georgi Nikolov

instead of WHERE clause use :
HAVING distance < 50

• 38 Ryan Preisner

Thanks Georgi. I kept getting column ‘distance’ not found. Once I change the WHERE to HAVING it worked like a charm!

26. 39 Felix Penrose

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 Felix Penrose

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 rajinder singh

Thanks a Lot!

29. 42 rajinder singh

Thanks a Lot!

30. 43 Brent Russell

I don’t think the code is showing up anymore. Maybe it’s firefox?

• 44 Douglas Karr

I just tested both in Firefox and Chrome and it’s appearing. Try again?

31. 45 pavan hemdev

Hi. Thanks a lot. This works like a charm.

32. 46 Pranav Dubey

Thanks a lot Douglas. This is working perfect.

33. 47 Tim Dawson

I know this formula works, but I can’t see where the radius of the earth is taken into account. Can anyone enlighten me, please ?

• 48 Douglas Karr

Tim, for a full explanation of the Haversine formula (that’s not code), check out Wikipedia’s article: http://en.wikipedia.org/wiki/Haversine_formula

34. 49 Ashton Fitz-Gerald

Beautiful! This has helped me immensely!

35. 50 Khanh

Great stuff Douglas.  Have you tried getting the intersection point given the Long/Lat/Bearing of two points?

• 51 Douglas Karr

Haven’t done that yet, Khanh!

36. 52 Patrick Avella

Thank you Douglas, the SQL Query is exactly what I needed, and I thought I’d have to write it myself. You’ve saved me from possibly hours of latitude longitude learning curve!

37. 53 peter.lamont

I keep getting Errormessage: Unknown column ‘Distance’ in ‘where clause’  on the MySQL Query.

• 54 Douglas Karr

38. 55 Paulo Alexandre

Thank you for this great article! Just tested the code on my DB and worked great!

39. 56 Ash Menon

Douglas, thank you for this amazing code. Been cracking my head on how to do this on my GPS community portal. You’ve saved me hours.

• 57 Douglas Karr

Great to hear, Ash!

40. 58 chi-wei shih

thanks for posting this helpful article,
but for some reason I’d like to ask
how to get the distance between coords inside mysql db and coords inserted to php by user?
for  more clearly describe:
1.user have to insert [id] for selecting specified data from db and user itself’s coords
2.the php file get the target data (coords) using [id] and then calculate distance between user and target point

or can just simply get distance from the code below?

\$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.”       >>>>can I “take out” the distance from here?
thanks again,
Timmy S

• 59 chi-wei shih

never mind, I’ve figure out how the “function” work in php
\$dis=getDistanceBetweenPointsNew(\$userLati, \$userLongi, \$lati, \$longi, \$unit = ‘Km’)
thanks a lot!!

41. 60 REDN3CK G33K

ok, everything I have tried is not working. I mean, what I have works, but the distances are way off.

Could anybody possibly see what is wrong with this code?

if(isset(\$_POST[‘submitted’])){    \$z = \$_POST[‘zipcode’];    \$r = \$_POST[‘radius’];    echo “Results for “.\$z;       \$sql = mysql_query(“SELECT DISTINCT m.zipcode, m.MktName,m.LocAddSt,m.LocAddCity,m.LocAddState,m.x1,m.y1,m.verified,z1.lat,z2.lon,z1.city,z1.state    FROM mrk m, zip z1, zip z2    WHERE m.zipcode = z1.zipcode    AND z2.zipcode = \$z    AND (3963 * acos( truncate( sin( z2.lat / 57.2958 ) * sin( m.y1 / 57.2958 ) + cos( z2.lat / 57.2958 ) * cos( m.y1 / 57.2958 ) * cos( m.x1 / 57.2958 – z2.lon / 57.2958 ) , 8 ) ) ) <= \$r ")        or die(mysql_error());        while(\$row = mysql_fetch_array( \$sql )) {            \$store1 = \$row['MktName']."”;            \$store = \$row[‘LocAddSt’].””;            \$store .= \$row[‘LocAddCity’].”, “.\$row[‘LocAddState’].” “.\$row[‘zipcode’];            \$latitude1 = \$row[‘lat’];            \$longitude1 = \$row[‘lon’];            \$latitude2 = \$row[‘y1’];            \$longitude2 = \$row[‘x1’];            \$city = \$row[‘city’];            \$state = \$row[‘state’];            \$dis = getnew(\$latitude1, \$longitude1, \$latitude2, \$longitude2, \$unit = ‘Mi’);           // \$dis = distance(\$lat1, \$lon1, \$lat2, \$lon2);            \$verified = \$row[‘verified’];                                        if(\$verified == ‘1’){                    echo “”;                    echo “”.\$store.””;                            echo \$dis . ” mile(s) away”;                    echo “”;                }                else {                    echo “”.\$store.””;                            echo \$dis . ” mile(s) away”;                    echo “”;                }        }}

my functions.php code
function getnew(\$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)); }

42. 61 Dipak Chavda

43. 62 Cody Ned Romano

Hey Douglas, great article. I found your explanation of the geographical concepts and the code really interesting. My only suggestion would be to space and indent the code for display (like Stackoverflow, for example). I understand that you want to conserve space, but conventional code spacing / indentation would make it a lot easier for me, as a programmer, to read and dissect. Anyhow, that’s a small thing. Keep up the great work.

• 63 Douglas Karr

Thanks! I’ve modified the post a little… but the equations take up so much room and are so long that I’m not sure that it helps too much.

44. 64 facebook-12100445

Thank you SO much.

45. 65 Mani

here while using with function we are getting one type of distance..while using query its coming other type of distance

46. 66 Mangesh Narwade

I wont calculate distance between two state

47. 67 nazart

Muchas gracias por tan hermoso codigo…

48. 68 madvice

Thisd i good cosinus functions. I dont know math, but thanks!

49. 69 Chirag

Great Job… 🙂 (y)

50. 70 axi35alexis

it seems faster (mysql 5.9) to use twice the formula in the select and where:
\$formula = “(((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)”;
\$sql = ‘SELECT *, ‘.\$formula.’ as distance FROM table WHERE ‘..\$formula.’ <= '.\$distance;

51. 71 zack

thanks…

not working if
“WHERE distance”

working if
“HAVING distance”

52. 72 kabbomoy

PHP was at first created as a simple scripting platform called “Personal Home Page”. Nowadays PHP (the short for Hypertext Preprocessor) is an alternative of the Microsoft’s Active Server Pages (ASP) technology.

PHP is an open source server-side language which is used for creating dynamic web pages. It can be embedded into HTML. PHP is usually used in conjunction with a MySQL database on Linux/UNIX web servers. It is probably the most popular scripting language.

53. 73 Kupendra

I found above solution not working properly.
I need to change to :

\$qqq = “SELECT *,(((acos(sin((“.\$latitude.”*pi()/180)) * sin((`latt`*pi()/180))+cos((” . \$latitude . “*pi()/180)) * cos((`latt`*pi()/180)) * cos(((” . \$longitude . “- `longt`)*pi()/180))))*180/pi())*60*1.1515) as distance FROM `register` “;

• 74 Douglas Karr

Thanks Kupendra!

54. 75 deeprathod74

thank you sir wroking perfectly.. but i have one question if i want to output without decimal point then what can i do..?

55. 76 Daniel

I made a get request to my web-server http://localhost:8000/users/findusers/53.47792/-2.23389/20/
53.47792 = \$latitude
-2.23389 = \$longitude
and 20 = the distance i want to retrieve

However using you formula, it retrieves all rows in my db

\$results = DB::select( DB::raw(“SELECT *, (((acos(sin((“.\$latitude.”*pi()/180)) * sin((lat*pi()/180))+cos((“.\$latitude.”*pi()/180)) * cos((lat*pi()/180)) * cos(((“.\$longitude.”- lng)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM markers HAVING distance >= “.\$distance ));

[{“id”:1,”name”:”Frankie Johnnie & Luigo Too”,”address”:”939 W El Camino Real, Mountain View, CA”,”lat”:37.386337280273,”lng”:-122.08582305908,”distance”:16079.294719663},{“id”:2,”name”:”Amici’s East Coast Pizzeria”,”address”:”790 Castro St, Mountain View, CA”,”lat”:37.387138366699,”lng”:-122.08323669434,”distance”:16079.175940152},{“id”:3,”name”:”Kapp’s Pizza Bar & Grill”,”address”:”191 Castro St, Mountain View, CA”,”lat”:37.393886566162,”lng”:-122.07891845703,”distance”:16078.381373826},{“id”:4,”name”:”Round Table Pizza: Mountain View”,”address”:”570 N Shoreline Blvd, Mountain View, CA”,”lat”:37.402652740479,”lng”:-122.07935333252,”distance”:16077.420540582},{“id”:5,”name”:”Tony & Alba’s Pizza & Pasta”,”address”:”619 Escuela Ave, Mountain View, CA”,”lat”:37.394012451172,”lng”:-122.09552764893,”distance”:16078.563225154},{“id”:6,”name”:”Oregano’s Wood-Fired Pizza”,”address”:”4546 El Camino Real, Los Altos, CA”,”lat”:37.401725769043,”lng”:-122.11464691162,”distance”:16077.937560795},{“id”:7,”name”:”The bars and grills”,”address”:”24 Whiteley Street, Manchester”,”lat”:53.485118865967,”lng”:-2.1828699111938,”distance”:8038.7620112314}]

I want to retrieve just rows with 20 miles but it brings all rows. Please what am i doing wrong

This site uses Akismet to reduce spam. Learn how your comment data is processed.