Calculate Distance In Mysql with Latitude and Longitude

So you have a whole table full of members or places with latitude and longitude’s associated with them. Just replace the $lat and $lon with the center point you want to find distances from. You can also change the distance<=10 to a number you want to search from. This will limit your results to all results that are under 10 miles from the starting point

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `members` HAVING `distance`<=’10′ ORDER BY `distance` ASC

About mike
Currently works for Recurly as a Senior Linux Admin. He has a wonderful wife Thanuja and 2 great children (Anusha and Brandon). His major side project is Photoblog.

Comments

86 Responses to “Calculate Distance In Mysql with Latitude and Longitude”
  1. Dave says:

    Thanks for the great algorithm! I added some back-ticks ( ` ) to your code to make it easier to read for the uninitiated. Anything denoted by a back-tick, (NOT the same as a single quote), means it is a field or table in your database table in this case the field names would be `lat` and `lon` and the table name would be `members`

    ————————-

    SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS($lat * PI() / 180) * COS(`lat` * PI() / 180) * COS(($lon – `lon`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM `members` HAVING distance<=’10′ ORDER BY distance ASC

    • mike says:

      Thanks for improving it.. I’ll add the backticks into the post

      • Alan says:

        Hi Mike

        here is my query which does not work, can you figure it out im soo lost

        SELECT ((ACOS(SIN(-27 * PI() / 180) * SIN(latitude * PI() / 180) + COS(-27 * PI() / 180) * COS(latitude * PI() / 180) * COS((153 – longtitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM Postcode HAVING distance<=10 ORDER BY distance ASC ………this is the query

        .Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '– longtitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM Pos' at line 1

        Oh I am using Austalian postcode incase you needed to know

        thanks
        Alan

  2. Eric says:

    in this part: HAVING `distance`<=’10′

    what are those characters around the number 10?

  3. Eric says:

    Anyway thanks for posting this, my problem is that I want to use this on a WordPress database where the coordinates are stored in the wp_postmeta table like this:

    meta_id

    post_id

    meta_key

    meta_value

    11294
    4448
    _latitude
    40.722039

    11295
    4448
    _longitude
    -74.004142

    WHERE wp_posts.ID = wp_postmeta.post_id

    If you happen to know how to write that query I would greatly appreciate that information!

    • justin says:

      set @latitude = 121.12; — center latitude
      set @longitude = 4.20; — center longitude
      set @distance = 10; — search distance

      select p.ID, p.post_name, ((ACOS(SIN(@latitude * PI() / 180) * SIN(`latitude.meta_value` * PI() / 180) + COS(@latitude * PI() / 180) * COS(`latitude.meta_value` * PI() / 180) * COS((@longitude – `longitude.meta_value`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance
      from wp_posts p
      left join wp_postmeta latitude on latitude.post_id = p.ID and latitude.meta_key = ‘_latitude’
      left join wp_postmeta longitude on longitude.post_id = p.ID and longitude.meta_key = ‘_longitude’
      having distance < @distance;

  4. Mitch says:

    What does the sql table look like to store the latitude and logitude information?

  5. mortanon says:

    If the table members is big you will have performance issues because of the full table scan.Take a look here for better solution ;-)

    http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

    • mike says:

      actually you are completely wrong.. This is the query I run on Photoblog to find members around you and if you index the latitude and longitude columns it will never do a full table scan

      • mortanon says:

        I dont think so :-)
        My comment refer to the query you wrote:

        SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `members` HAVING `distance`<=’10′ ORDER BY `distance` ASC

        I assume your table design looks like this :

        members
        | member_id | latitude | longitude | … |

        This particular query will never ever use an index because :
        a) the query do not have where clause
        b) the query have to calculate the distance for all the records in the table members to emit only those "HAVING `distance`<=’10′"

        a) and b) means always full table scan on MySQL.

        Try EXPLAIN on this query and see what it says ;-)

        Take a look at the link a send to you to see how to optimize your query using "between".

        regards

        • mike says:


          +----+-------------+---------+-------+---------------+------+---------+------+-------+-----------------------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +----+-------------+---------+-------+---------------+------+---------+------+-------+-----------------------------+
          | 1 | SIMPLE | members | range | id,lat,lon | lat | 4 | NULL | 21532 | Using where; Using filesort |
          +----+-------------+---------+-------+---------------+------+---------+------+-------+-----------------------------+

          My members table is around 800k rows and I around NYC where we have a ton of users. I agree I do need to redo the query to better optimize it but for now it runs at .2 seconds for an uncached query.

          • Joe says:

            Mortanon does have a point but Its a simple upgrade, and only needed on a big database. All you do is trim down the results your going to do the full crunching to by an approximate method. If you only care about results in a 10 mile radius than you can do a simple select with a 10 mile square box and then do the proper crunching. You could even approximate your 10 mile square box to start with and say I have no users futher north than 60 degrees so ill pick a 10 mile square box at that point by a difference in co-ordinates and use the same distance down south (even though it would be a bigger box in miles it would still trim down your database a lot!)

  6. Tricks says:

    Hi i’ve tried to implement this on my site but the query doesn’t work?

  7. john says:

    This is what I’m using for the `distance`:

    (DEGREES(ACOS(SIN(RADIANS(” . $user_data['user_lat'] . “)) * SIN(RADIANS(p.location_geolat)) + COS(RADIANS(” . $user_data['user_lat'] . “)) * COS(RADIANS(p.location_geolat)) * COS(RADIANS(” . $user_data['user_lon'] . ” – p.location_geolon)))) * 60 * 1.1515) AS `distance`

  8. Brendt says:

    Do you know what you have to change in this equation to change the distance to kilometers instead of miles?

  9. Simon says:

    Hi Mike,

    Looks good.
    I too would be interested in understanding how to apply kilometres instead of miles.

    Would you be able to help with a similar scenario – taking a series of points which represent an itinerary, find all the recorded locations within in a specified distance (eg: 25 Km) ?
    Best regards

  10. Scott Cariss says:

    1 miles = 1.609344 kilometers

    Times the output number by 1.609344 to get the value in Km

  11. Gabi says:

    Hi, anyone know if that equation is faster in PL/SQL than normal SQL command? Also what is fastest equation?

    thanks

  12. David says:

    Any idea why this would be returning an empty set, when there are plenty of records that should qualify for a given distance?

  13. Andy Moore says:

    Mike,

    Respect! That is truly awesome!

    Thank you for sharing, you saved me hours of banging my head on my desk.

    Cheers

  14. Chirag says:

    Great post.
    One shot workable solution.

    Thanks
    Chirag

  15. Barbayar says:

    I was searching this query. Thank you. But, I have a question. Is this Haversine formula? I tested this query. It works great. But Haversine Formula’s definition is little bit different. Isn’t it?

    And, I tested it with same location. It didn’t return zero. Can you explain that for me?

    Thank you, and sorry for my poor English.

  16. John Wards says:

    Hi,

    I am busy trying to do this in MySQL, but I wanted to confirm that the maths does what it says on the tin. So I drew a straight line on google and got the start end lat/lon and plugged it into various functions.

    I am doing distances over short and long distances so it needs to cope with both. Sadly your example is not accurate.

    I think its all to do with the curvature of the earth etc.

    I am going to convert this:
    http://www.movable-type.co.uk/scripts/latlong.html

    into a stored procedure, which I’ll blog about and link back here.

  17. balu says:

    Is it we are getting distance in MILES or in meters?

  18. balu says:

    i m using following formula…

    3956 * 2 * ASIN(SQRT( POWER(SIN((inLAT -
    LATITUDE) * pi()/180 / 2), 2) +
    COS(inLAT * pi()/180) * COS(LATITUDE * pi()/180) *
    POWER(SIN((inLONG – LONGITUDE) * pi()/180 / 2), 2) )) as
    distance

  19. Radhika says:

    hi,

    Does it works with SQL server also?

    Regards,
    Radhika

  20. Yos says:

    Thanks, works perfect…

  21. Wouter says:

    Looks very nice, although i can’t get it working:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘– lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM lp_praktij’ at line 1

    I noticed the strange ‘– but that is not showing in my query, only in the error message

  22. Rick says:

    Hey guys, I’m have some issues with query:

    user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘– longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `z’ at line 1 query: SELECT zip,((ACOS(SIN(33.786594 * PI() / 180) * SIN(latitude * PI() / 180) + COS(33.786594 * PI() / 180) * COS(latitude * PI() / 180) * COS((-118.298662 – longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `zip_codes` HAVING `distance`<=100 ORDER BY `distance` ASC in C:\inetpub\ECOM\Store\includes\common.inc(1695) : eval()'d code on line 65.

    I think some of the problems were related to backticks rendering improperly in wordpress, but I can't figure out what is wrong now.. maybe an extra parenthesis somewhere?

    • Rick says:

      oops.. here was my original query:

      $zip_result = db_query(” SELECT zip,((ACOS(SIN(“.$lat.” * PI() / 180) * SIN(latitude * PI() / 180) + COS(“.$lat.” * PI() / 180) * COS(latitude * PI() / 180) * COS((“.$long.” – longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `zip_codes` HAVING `distance`<=".(int)$distance." ORDER BY `distance` ASC ");

  23. Brian Eppert says:

    Mike, great post that saved me some time, thanks! A few things:

    In your original post above there is an en dash (–) character that should really be a minus sign. So those who copy-paste the SQL will get an error upon execution.

    Also, here’s an HQL adaptation for users of Hibernate (with a Profile entity having properties latitude & longitude):

    from Profile p where ((acos(sin(:latitude * pi() / 180) * sin(p.latitude * pi() / 180) + cos(:latitude * pi() / 180) * cos(p.latitude * pi() / 180) * cos((:longitude – p.longitude) * PI() / 180)) * 180 / pi()) * 60 * 1.1515) < :radius

    And here is just the distance algorithm in Java:

    ((Math.acos(Math.sin(latitude * Math.PI / 180) * Math.sin(p.latitude * Math.PI / 180) + Math.cos(latitude * Math.PI / 180) * Math.cos(p.latitude * Math.PI / 180) * Math.cos((longitude – p.longitude) * Math.PI / 180)) * 180 / Math.PI) * 60 * 1.1515)

    Cheers,
    -b.e.

  24. Nice – SQL works well. I created a function to make it easier to call.

    create FUNCTION geo_distance (in_lat decimal(10,6), in_lon decimal(10,6), latitude decimal(10,6), longitude decimal(10,6))
    returns decimal(10,3) DETERMINISTIC
    return ((ACOS(SIN(in_lat * PI() / 180) * SIN(latitude * PI() / 180) + COS(in_lat * PI() / 180) * COS(latitude * PI() / 180) * COS((in_lon – longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) ;

    –Sample Usage, assuming a table called Places with fields latitude & longitude:
    select geo_distance(-34.017330, 22.809500, latitude, longitude) as distance_from_input from places;

  25. Aaron says:

    Heya, Mike, great script! Just wanna say thanks. You’ve helped me loads. One question though. How can I add a WHERE clause in there? Whenever I try it throws me an error. I wanna select by date, field name is startDate and variable name is $startDate…

  26. AL says:

    Does this need any particular version of MySQL to work? I pasted in exactly as is and I just get a syntax error when executing it.

  27. AL says:

    Never mind, I figured it out. When pasting the code into Toad it was converting the – character to something else. Weird..

    • mike says:

      syntax errors are usually due to the putting incorrect minus signs or ‘ in the post. Go through the query and replace them

  28. Kevin says:

    Awesome, awesome code. Really appreciate the help. I’m just curious, how were you able to engineer this equation? Law of sines/law of cosines? Something more awesome I haven’t even thought of? Used to love trig in college.

    Kevin
    elevatedthird.com

  29. Jim says:

    Hi, thx for a nice script.

    I have bben using it for a while until today I discovered that if lon is example -1.567 then I would not get any results back. It works perfect on example 1.567 so if the lon field in mysql have a minus infront I would not get any results.

    My sql looks like this.

    $query = ‘SELECT t1.id, t1.name, t1.image, t1.address, t1.zip, t1.city, t1.country, t1.phone, t1.email, t1.url1, t1.url2, t1.latlng, t1.content, t1.ingress, t1.campaign, t1.ingress_short, ((ACOS(SIN(‘.$pos[0].’ * PI() / 180) * SIN(lat * PI() / 180) + COS(‘.$pos[0].’ * PI() / 180) * COS(lat * PI() / 180) * COS((‘.$pos[1].’ – `lon`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM locations AS t1 WHERE t1.client_id=’.$companyID.’ AND t1.active = “yes” AND t1.lat>1 AND lon>1 HAVING distance<='.$radius.' ORDER BY distance ASC '.$maxlimit;

    Can someone see what is wrong? The mysql field is a float field. Is that ok?

    Best Jim

  30. abru says:

    id – 1,
    college – auburn,
    zipcode – 36849,
    url – http://www.auburn.edu,
    ipeds is – 100858,
    campus – city,
    latitude – 32.59942,
    logitude – -85.4881

    given this with columns and values… how do you go about a script that will bring a bout the or m0st importantly a picture of the school with the lat, log and url…. and calling it from your database for your website ?

    let me add.. you’re are doing a nice job give and helping humanity

  31. gowtham says:

    Sir, i need to calculate the Positions of Latitude and Longitude in JAVA using Edit+…… ll u plz post me a querry for that.

  32. David Ryder says:

    Quite possibly one of the most impressive MySQL calculations I’ve ever seen. Thanks!

  33. Henry says:

    I have used this before from a different set of software. It is very cool. One thing I am unsure of though is what type of miles. Statute or nautical. Surely if it is Nautical miles as it is talking in terms of lat and lon then it is time 1.852 to get the distance in Kilometres?

  34. gxg says:

    hello. i want to ask you if your formula applies in my case. i want to build a site and when you visit the page of .. tower of london(just an example) i want to know what hotels are in a range of 10 km from the tower of london based on the coordonates of each. thank you

  35. Steve says:

    Im using http://postcodepal.com/?page=mysql-query

    It seems to work much better on larger databases.

  36. Andy Krier says:

    Hey Mike, Thank you for this. It was really helpful. I’m just stuck on one thing. I want to show the distance of each result as it pertains to my location. I’m guessing that this is something really simple that I am just failing at.

    Thanks in advance.

    • mike says:

      That query already does that.. it returns the distance from a member.. You pass in your current lat/lon via the $lat/$lon in the query.. so those are like PHP vars to form the query.

  37. gilberto says:

    GREAT! Practically saved my life haha

  38. raju says:

    Hi,

    I am Using a same algorithm like this.

    i am getting Results while querying in mysql window but….

    when i am using this with hibernate i am getting

    Caused by: java.sql.SQLException: Column ‘column name’ not found.

    can you please help me(RAJUMUDDANA@GMAIL.COM) how to work out this algorithm using hibernate.

    Thanks & Regards
    raju.

  39. German says:

    How can I echo the actual distance between starting point and the sql results in miles? i tried something like echo $distance; but it wont work,.. how can i do it ? thanks

  40. What a great algorithm. I needed exactly the same.

  41. I think some of the problems readers are having s that the ticks are formatted a little weird in the above code examples.

    Here’s what I have working:

    # Ruby
    SELECT ((ACOS(SIN(#{self.latitude} * PI() / 180) * SIN(`latitude` * PI() / 180) + COS(#{self.latitude} * PI() / 180) * COS(`latitude` * PI() / 180) * COS((#{self.longitude} – `longitude`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM `users` HAVING distance <= `5` ORDER BY distance ASC

  42. Anu says:

    Thank you so much for the algorithm. It really saved my day.

  43. Bill Teale says:

    I tried to take this code a step further, but I have done something wrong somewhere. Basically, on the site, the user inputs a five digit zip code. In the code below I have a query that get s the latitude and longitude from a zip code table (ZIP_FULL), then tries to use these fields in the distance query. I am stepping way out of my knowledge base, learning fast too, but can you give me some pointers as to what I have done wrong? I would really appreciate it!

    $colname_LOCATION = “-1″;
    if (isset($_POST['ZIPCODE'])) {
    $colname_LOCATION = $_POST['ZIPCODE'];
    }
    mysql_select_db($database_XMASTREE, $XMASTREE);
    $query_LOCATION = sprintf(“SELECT * FROM ZIP_FULL WHERE zip_code = %s”, GetSQLValueString($colname_LOCATION, “int”));
    $LOCATION = mysql_query($query_LOCATION, $XMASTREE) or die(mysql_error());
    $row_LOCATION = mysql_fetch_assoc($LOCATION);
    $totalRows_LOCATION = mysql_num_rows($LOCATION);

    $lat = $row_LOCATION['latitude'];
    $lon = $row_LOCATION['longitude'];

    mysql_select_db($database_XMASTREE, $XMASTREE);
    $query_DIST = “SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM MEMBERS HAVING distance <= 10 ORDER BY distance ASC";
    $DIST = mysql_query($query_DIST, $XMASTREE) or die(mysql_error());
    $row_DIST = mysql_fetch_assoc($DIST);
    $totalRows_DIST = mysql_num_rows($DIST);

  44. doesser says:

    What is ” * 60 * 1.1515″ in formula?

Trackbacks

Check out what others are saying about this post...
  1. [...] and “B” from “miles” to “degrees longitude/latitude.” http://zcentric.com/2010/03/11/calculate-distance-in-mysql-with-latitude-and-longitude/ has more information.   0 Comments Leave A [...]

  2. [...] I first had a need to work out what was near when working on an Ad Server and Mike Zupan’s site was a lifesaver for doing the headbendingly complication mathematical bit. Calculate distance in MySQL [...]

  3. [...] below:SELECT CALCULATE_DISTANCE(-33.91429, 18.42389, -34.079120, 18.446882);Thanks to zcentric.com for the SQL calculation.Tags: Development, MySQL, usefulMore from this category« You want [...]

  4. [...] What I’m doing is taking latitude and longitude values from another table and referencing them with this table. The formula works like a charm on finding locations within a given distance. I have it set at 25 miles and only want the closest match but you could change it to give you the nearest # of locations. I found the query here. [...]

  5. [...] What I’m doing is taking latitude and longitude values from another table and referencing them with this table. The formula works like a charm on finding locations within a given distance. I have it set at 25 miles and only want the closest match but you could change it to give you the nearest # of locations. I found the query here. [...]

  6. [...] It’s a little complicated algorithm, but here’s a link to one solution [...]

  7. […] is what I think you need: Calculate distance in MySQL using lat/lng. This will give you a circle, and an ability to have exclusion you […]

  8. […] you can match nearby hotspots with this SQL query and order by […]

  9. […] MySql function was posted. I haven’t tested it much, but from what I gathered from the post, if your latitude and longitude fields are indexed, this may work well for […]



Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!