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.

  • http://moorberry.net Michael Minter

    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

  • Pingback: Fastest Way to Find Distance Between Two Lat/Long Points - PHP Solutions - Developers Q & A()

  • Anu

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

  • http://nc-chooseandcut.com Bill Teale

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

  • http://livestreetguide.com/ doesser

    What is ” * 60 * 1.1515″ in formula?

    • http://www.zcentric.com mike

      converting degrees to miles

  • http://codingpope.com John

    Hi,
    So how will I echo out the individual values of each rows that are true according to the query?
    I tried this but it didnt work:

    $query = mysql_query(“SELECT ((ACOS(SIN(7.3049491 * PI() / 180) * SIN(lat * PI() / 180) + COS(7.3049491 * PI() / 180) * COS(lat * PI() / 180) * COS((5.1346151 – lng) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `biodata` HAVING `distance` <='10' ORDER BY `distance` ASC");

    while ($row = mysql_fetch_array($query)){
    $lat = $row['lat'];
    $lnt = $row['lng'];

    echo $lat." ".$lng;
    }

  • http://codingpope.com John

    $query = mysql_query(“SELECT ((ACOS(SIN(7.3049491 * PI() / 180) * SIN(lat * PI() / 180) + COS(7.3049491 * PI() / 180) * COS(lat * PI() / 180) * COS((5.1346151 – lng) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `biodata` HAVING `distance` <='10' ORDER BY `distance` ASC");

    while ($row = mysql_fetch_array($query)){
    $lat = $row['lat'];
    $lnt = $row['lng'];

    echo $lat." ".$lng;
    }

  • vadim

    Great sql mike. Thank you.

  • Pingback: How to: Fastest Way to Find Distance Between Two Lat/Long Points | SevenNet()

  • Willy

    could you make ilustration to describe this calculation. Thanks !

css.php