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.

  • Dave

    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

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

      • Alan

        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


        • mike

          Yes.. it is a copy/paste issue.. go back to what you think the the – symbol and delete it and just re-type it again. It should work then

          • cagdas

            So what is the type of returning value ? what kind of distance ? kilometers? miles?

          • mike


        • zetta

          thats a conflict , replace the ‘_’ withe a minus sign ‘-‘

        • zetta

          it is a conflict , just replace the the ‘–’ with a minus sign ‘-‘ in (153 – longtitude)

  • Eric

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

    what are those characters around the number 10?

    • mike

      Those are a single quote

  • Eric

    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:







    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

      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;

      • pellicule

        I’m trying to do exactly the same thing (geo search for WP); I did use your function but seems it does not work.
        Any idea ?
        Here’s my code :

        • Joe

          You ever get this to work with wordpress custom meta?

      • syed

        the above query shows error.

        #1054 – Unknown column ‘latitude.metavalue’ in ‘field list’

      • syed

        the above query shows error.

        #1054 – Unknown column ‘latitude.metavalue’ in ‘field list’.

        can you please help me out.

  • Mitch

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

    • mike

      my lat/lon information was just 2 columns in the user profile table. They were both indexes and decimal types

      • Trace

        My field types are FLOAT instead of decimal. I’m not getting expected results with your query. Is this why?

        • mike

          Nope since mine are floats. You are having another issue somewhere in the query or data

  • mortanon

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

    • mike

      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

        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 :

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


        • mike

          | 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

            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!)

  • Tricks

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

    • mike

      What isn’t working?

  • john

    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`

  • Brendt

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

    • Robert

      Modify the HAVING value to use kilometers instead of miles: 10 * 1.60934 = 16.0934

  • Simon

    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

  • Scott Cariss

    1 miles = 1.609344 kilometers

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

  • Pingback: Tips and Tricks On Converting Addresses to GPS Location()

  • Gabi

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


  • David

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

  • Andy Moore


    Respect! That is truly awesome!

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


    • mike

      glad it saved you some time!

  • Pingback: Working with Latitude/Longitude locations in MySQL | Baneworld Coding Blog()

  • Chirag

    Great post.
    One shot workable solution.


  • Barbayar

    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.

    • Barbayar

      Sorry, It was my fault. It returned 0.

  • John Wards


    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:

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

  • balu

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

  • balu

    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

  • Radhika


    Does it works with SQL server also?


  • Yos

    Thanks, works perfect…

  • Wouter

    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

    • Greggy

      It’s just the minus character,
      I know it’s to late, but it could help an other person ;)

  • Rick

    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\ : 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

      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 ");

      • mike

        You had the issue the next commenter had.. find the – delete it and put in place your own minus sign

  • Pingback: Location Detection and Nearby Data | Andy Moore()

  • Pingback: geo location with mysql «

  • Brian Eppert

    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)


    • mike

      Thanks, I made the change

  • Donald Garlick

    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;

    • mike

      nice function!

  • Pingback: Calculating the distance between two GPS points in MySQL | Geoffrey Garbers()

  • Aaron

    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…

    • mike

      Add the where after the FROM table and before the HAVING

  • AL

    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.

  • AL

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

    • mike

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

  • Kevin

    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.


    • mike

      I did it so long ago, I really do not remember. I know I got formulas from other websites and pieced it together in sql.

  • Jim

    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.image, t1.address,,,,,, 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 = “yes” AND>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

    • Jim

      Sorry, found the reason.. prety obious when lon>1 is set in the query


  • abru

    id – 1,
    college – auburn,
    zipcode – 36849,
    url –,
    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

  • gowtham

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

  • David Ryder

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

  • Henry

    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?

  • gxg

    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

  • Steve

    Im using

    It seems to work much better on larger databases.

  • Andy Krier

    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

      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.

  • Pingback: query fails in php file but never in phpmyadmin()

  • Pingback: query fails in php file but never in phpmyadmin • PHP Help Coding Programming()

  • Pingback: select within 20 kilometers based on latitude/longitude - PHP Solutions - Developers Q & A()

  • gilberto

    GREAT! Practically saved my life haha

  • raju


    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

  • Pingback: Queries to find places within a given lat/lng - PHP Solutions - Developers Q & A()

  • German

    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

    • mike

      Sounds like a php problem and not the query.. you iterate through the results and echo it out

  • Pingback: Get nearest locations using latitude and longitude - PHP Solutions - Developers Q & A()

  • Altaf Hussain

    What a great algorithm. I needed exactly the same.