Find nearest latitude longitude with an SQL query

Question

I have latitude and longitude and I want to pull the record from the database  which has nearest latitude and longitude by the distance  if that distance gets longer than specified one  then don t retrieve it   Table structure   id latitude longitude place name city country state zip sealevel

User · Answer

Here is my full solution implemented in PHP.

This solution uses the Haversine formula as presented in http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL.

It should be noted that the Haversine formula experiences weaknesses around the poles. This answer shows how to implement the vincenty Great Circle Distance formula to get around this, however I chose to just use Haversine because it's good enough for my purposes.

I'm storing latitude as DECIMAL(10,8) and longitude as DECIMAL(11,8). Hopefully this helps!

showClosest.php

<?PHP
/**
 * Use the Haversine Formula to display the 100 closest matches to $origLat, $origLon
 * Only search the MySQL table $tableName for matches within a 10 mile ($dist) radius.
 */
include("./assets/db/db.php"); // Include database connection function
$db = new database(); // Initiate a new MySQL connection
$tableName = "db.table";
$origLat = 42.1365;
$origLon = -71.7559;
$dist = 10; // This is the maximum distance (in miles) away from $origLat, $origLon in which to search
$query = "SELECT name, latitude, longitude, 3956 * 2 * 
          ASIN(SQRT( POWER(SIN(($origLat - latitude)*pi()/180/2),2)
          +COS($origLat*pi()/180 )*COS(latitude*pi()/180)
          *POWER(SIN(($origLon-longitude)*pi()/180/2),2))) 
          as distance FROM $tableName WHERE 
          longitude between ($origLon-$dist/cos(radians($origLat))*69) 
          and ($origLon+$dist/cos(radians($origLat))*69) 
          and latitude between ($origLat-($dist/69)) 
          and ($origLat+($dist/69)) 
          having distance < $dist ORDER BY distance limit 100"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
    echo $row['name']." > ".$row['distance']."<BR>";
}
mysql_close($db);
?>

./assets/db/db.php

<?PHP
/**
 * Class to initiate a new MySQL connection based on $dbInfo settings found in dbSettings.php
 *
 * @example $db = new database(); // Initiate a new database connection
 * @example mysql_close($db); // close the connection
 */
class database{
    protected $databaseLink;
    function __construct(){
        include "dbSettings.php";
        $this->database = $dbInfo['host'];
        $this->mysql_user = $dbInfo['user'];
        $this->mysql_pass = $dbInfo['pass'];
        $this->openConnection();
        return $this->get_link();
    }
    function openConnection(){
    $this->databaseLink = mysql_connect($this->database, $this->mysql_user, $this->mysql_pass);
    }

    function get_link(){
    return $this->databaseLink;
    }
}
?>

./assets/db/dbSettings.php

<?php
$dbInfo = array(
    'host'      => "localhost",
    'user'      => "root",
    'pass'      => "password"
);
?>

It may be possible to increase performance by using a MySQL stored procedure as suggested by the "Geo-Distance-Search-with-MySQL" article posted above.

I have a database of ~17,000 places and the query execution time is 0.054 seconds.

User · Answer

It sounds like you want to do a nearest neighbour search with some bound on the distance  SQL does not support anything like this as far as I am aware and you would need to use an alternative data structure such as an R-tree or kd-tree

User · Answer

In extreme cases this approach fails  but for performance  I ve skipped the trigonometry and simply calculated the diagonal squared

User · Answer

Google s solution  Creating the Table When you create the MySQL table  you want to pay particular attention to the lat and lng attributes  With the current zoom capabilities of Google Maps  you should only need 6 digits of precision after the decimal  To keep the storage space required for your table at a minimum  you can specify that the lat and lng attributes are floats of size  10 6   That will let the fields store 6 digits after the decimal  plus up to 4 digits before the decimal  e g  -123 456789 degrees  Your table should also have an id attribute to serve as the primary key  CREATE TABLE  markers       id  INT NOT NULL AUTO INCREMENT PRIMARY KEY      name  VARCHAR  60   NOT NULL      address  VARCHAR  80   NOT NULL      lat  FLOAT  10  6   NOT NULL      lng  FLOAT  10  6   NOT NULL   ENGINE   MYISAM    Populating the Table After creating the table  it s time to populate it with data  The sample data provided below is for about 180 pizzarias scattered across the United States  In phpMyAdmin  you can use the IMPORT tab to import various file formats  including CSV  comma-separated values   Microsoft Excel and Google Spreadsheets both export to CSV format  so you can easily transfer data from spreadsheets to MySQL tables through exporting importing CSV files  INSERT INTO  markers    name    address    lat    lng   VALUES   Frankie Johnnie  amp  Luigo Too   939 W El Camino Real  Mountain View  CA   37 386339   -122 085823    INSERT INTO  markers    name    address    lat    lng   VALUES   Amici  s East Coast Pizzeria   790 Castro St  Mountain View  CA   37 38714   -122 083235    INSERT INTO  markers    name    address    lat    lng   VALUES   Kapp  s Pizza Bar  amp  Grill   191 Castro St  Mountain View  CA   37 393885   -122 078916    INSERT INTO  markers    name    address    lat    lng   VALUES   Round Table Pizza  Mountain View   570 N Shoreline Blvd  Mountain View  CA   37 402653   -122 079354    INSERT INTO  markers    name    address    lat    lng   VALUES   Tony  amp  Alba  s Pizza  amp  Pasta   619 Escuela Ave  Mountain View  CA   37 394011   -122 095528    INSERT INTO  markers    name    address    lat    lng   VALUES   Oregano  s Wood-Fired Pizza   4546 El Camino Real  Los Altos  CA   37 401724   -122 114646     Finding Locations with MySQL To find locations in your markers table that are within a certain radius distance of a given latitude longitude  you can use a SELECT statement based on the Haversine formula  The Haversine formula is used generally for computing great-circle distances between two pairs of coordinates on a sphere  An in-depth mathemetical explanation is given by Wikipedia and a good discussion of the formula as it relates to programming is on Movable Type s site  Here s the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37  -122 coordinate  It calculates the distance based on the latitude longitude of that row and the target latitude longitude  and then asks for only rows where the distance value is less than 25  orders the whole query by distance  and limits it to 20 results  To search by kilometers instead of miles  replace 3959 with 6371  SELECT  id        3959      acos cos radians 37         cos radians lat         cos radians lng  -     radians -122         sin radians 37         sin radians lat       AS distance  FROM markers  HAVING distance  lt  28  ORDER BY distance LIMIT 0  20   This one is to find latitudes and longitudes in a distance less than 28 miles  Another one is to find them in a distance between 28 and 29 miles  SELECT  id        3959      acos cos radians 37         cos radians lat         cos radians lng  -     radians -122         sin radians 37         sin radians lat       AS distance  FROM markers  HAVING distance  lt  29 and distance  gt  28  ORDER BY distance LIMIT 0  20   https   developers google com maps articles phpsqlsearch v3 creating-the-map

User · Answer

---- ----------------------- --------- -------------- ---------------    id   email                   name      location lat   location long    ---- ----------------------- --------- -------------- ---------------    7    test gmail com          rembo     23 0249256      72 5269697       25   test1 gmail com         Rajnis    23 0233221      72 5342112      ---- ----------------------- --------- -------------- ---------------    lat   23 02350629   long   72 53230239  DB   SELECT   quot  SELECT   FROM   SELECT         acos  sin    quot     lat   quot    pi     180     sin   lat   pi     180     cos    quot     lat   quot  pi     180      cos   lat   pi     180     cos     quot     long   quot  - LONG    pi     180        180   pi       60   1 1515   1 609344   as distance FROM users   users WHERE distance  lt   2 quot

User · Answer

Find nearest Users to my    Distance in meters  Based in Vincenty s formula   i have User table    ---- ----------------------- --------- -------------- ---------------    id   email                   name      location lat   location long    ---- ----------------------- --------- -------------- ---------------    13   xxxxxx xxxxxxxxxx com   Isaac     17 2675625     -97 6802361       14   xxxx xxxxxxx com mx     Monse     19 392702      -99 172596       ---- ----------------------- --------- -------------- ---------------    sql   -- my location   lat   19 391124   -99 165660 SELECT   ATAN      SQRT          POW COS RADIANS users location lat     SIN RADIANS users location long  - RADIANS -99 165660    2            POW COS RADIANS 19 391124     SIN RADIANS users location lat   -         SIN RADIANS 19 391124     cos RADIANS users location lat     cos RADIANS users location long  - RADIANS -99 165660    2                  SIN RADIANS 19 391124          SIN RADIANS users location lat          COS RADIANS 19 391124          COS RADIANS users location lat          COS RADIANS users location long  - RADIANS -99 165660        6371000  as distance  users id FROM users ORDER BY distance ASC   radius of the earth   6371000   in meters

User · Answer

Just in case you are lazy like me  here s a solution amalgamated from this and other answers on SO     set  orig lat 37 46   set  orig long -122 25   set  bounding distance 1   SELECT      ACOS SIN  orig lat   PI     180    SIN  lat    PI     180    COS  orig lat   PI     180    COS  lat    PI     180    COS   orig long -  long     PI     180     180   PI      60   1 1515  AS  distance   FROM  cities   WHERE      lat  BETWEEN   orig lat -  bounding distance  AND   orig lat    bounding distance    AND  long  BETWEEN   orig long -  bounding distance  AND   orig long    bounding distance    ORDER BY  distance  ASC limit 25

User · Answer

Sounds like you should just use PostGIS  SpatialLite  SQLServer2008  or Oracle Spatial  They can all answer this question for you with spatial SQL

User · Answer

The original answers to the question are good  but newer versions of mysql  MySQL 5 7 6 on  support geo queries  so you can now use built in functionality rather than doing complex queries  You can now do something like  select    ST Distance Sphere  point   input longitude    input latitude                                   point longitude  latitude      000621371192            as  distance in miles     from  TableName  having  distance in miles   lt    input max distance   order by  distance in miles  asc  The results are returned in meters  So if you want in KM simply use  001 instead of  000621371192  which is for miles   MySql docs are here

User · Answer

Check this code based on the article Geo-Distance-Search-with-MySQL   Example  find the 10 nearest hotels to my current location in a 10 miles radius    Please notice that  lat lng  values mustn t be negatives to perform all calculations  set  my lat 34 6087674878572   set  my lng 58 3783670308302  set  dist 10   10 miles radius  SELECT dest id  dest lat  dest lng   3956   2   ASIN SQRT POWER SIN   my lat -abs dest lat     pi   180   2  2    COS  my lat   pi   180     COS abs dest lat     pi   180    POWER SIN   my lng - abs dest lng      pi   180   2   2     as distance FROM hotel as dest having distance  lt   dist ORDER BY distance limit 10    Also notice that distance are expressed in terms of radius

User · Answer

Easy one     SELECT   FROM  WAYPOINTS  W ORDER BY ABS ABS W  LATITUDE -53 63    ABS W  LONGITUDE -9 9   ASC LIMIT 30    Just replace the coordinates with your required ones  The values have to be stored as double  This ist a working MySQL 5 x example   Cheers

User · Answer

You re looking for things like the haversine formula  See here as well   There s other ones but this is the most commonly cited   If you re looking for something even more robust  you might want to look at your databases GIS capabilities  They re capable of some cool things like telling you whether a point  City  appears within a given polygon  Region  Country  Continent

User · Answer

Mysql query for search coordinates with distance limit and where condition SELECT     3959 acos  cos  radians  28 5850154     cos  radians lat    cos  radians  lng   - radians  77 07207489999999       sin  radians  28 5850154       sin  radians  lat         AS distance FROM  Wo Products  WHERE  active     1  HAVING distance  lt  5

User · Answer

This problem is not very hard at all  but it gets more complicated if you need to optimize it    What I mean is  do you have 100 locations in your database or 100 million  It makes a big difference    If the number of locations is small  get them out of SQL and into code by just doing -    Select   from Location   Once you get them into code  calculate the distance between each lat lon and your original with the Haversine formula and sort it

User · Answer

MS SQL Edition here           DECLARE  SLAT AS FLOAT         DECLARE  SLON AS FLOAT          SET  SLAT   38 150785         SET  SLON   27 360249          SELECT TOP 10  LATITUDE    LONGITUDE   SQRT              POWER 69 1     LATITUDE  -  SLAT   2                POWER 69 1     SLON -  LONGITUDE     COS  LATITUDE    57 3   2   AS distance         FROM  TABLE  ORDER BY 3

User · Answer

Try this  it show the nearest points to provided coordinates  within 50 km   It works perfectly   SELECT m name      m lat  m lon      p distance unit                DEGREES ACOS COS RADIANS p latpoint                  COS RADIANS m lat                  COS RADIANS p longpoint  - RADIANS m lon                  SIN RADIANS p latpoint                  SIN RADIANS m lat     AS distance in km FROM  lt table name gt  AS m JOIN         SELECT  lt userLat gt  AS latpoint   lt userLon gt  AS longpoint               50 0 AS radius  111 045 AS distance unit        AS p ON 1 1 WHERE m lat BETWEEN p latpoint  -  p radius   p distance unit      AND p latpoint     p radius   p distance unit      AND m lon BETWEEN p longpoint -  p radius    p distance unit   COS RADIANS p latpoint         AND p longpoint    p radius    p distance unit   COS RADIANS p latpoint     ORDER BY distance in km   Just change  lt table name gt    lt userLat gt  and  lt userLon gt   You can read more about this solution here  http   www plumislandmedia net mysql haversine-mysql-nearest-loc

User · Answer

SELECT latitude  longitude  SQRT      POW 69 1    latitude -  startlat    2        POW 69 1     startlng  - longitude    COS latitude   57 3   2   AS distance FROM TableName HAVING distance  lt  25 ORDER BY distance    where  starlat  and  startlng  is the position where to start measuring the distance

User · Answer

simpledb execSQL  CREATE TABLE IF NOT EXISTS     tablename     id INTEGER PRIMARY KEY   AUTOINCREMENT lat double lng double address varchar                 simpledb execSQL  insert into      tablename      lat lng address values  22 2891001   70 780154   craftbox                   simpledb execSQL  insert into      tablename      lat lng address values  22 2901396   70 7782428   kotecha        22 2904718   70 7783906             simpledb execSQL  insert into      tablename      lat lng address values  22 2863155   70 772108   kkv Hall                   simpledb execSQL  insert into      tablename      lat lng address values  22 275993   70 778076   nana mava                   simpledb execSQL  insert into      tablename      lat lng address values  22 2667148   70 7609386   Govani boys hostal             double curentlat 22 2667258     22 2677258     double curentlong 70 76096826   70 76096826      double curentlat1 curentlat 0 0010000      double curentlat2 curentlat-0 0010000       double curentlong1 curentlong 0 0010000      double curentlong2 curentlong-0 0010000       try           Cursor c simpledb rawQuery  select   from    tablename    where  lat BETWEEN    curentlat2    and    curentlat1     or  lng BETWEEN            curentlong2    and    curentlong1      null            Log d  SQL    c toString             if c getCount   gt 0                        while  c moveToNext                                  double d c getDouble 1                   double d1 c getDouble 2                                      catch  Exception e                e printStackTrace

[mysql] Find nearest latitude/longitude with an SQL query

showClosest.php

./assets/db/db.php

./assets/db/dbSettings.php

Examples related to mysql

Examples related to sql

Examples related to coordinates

Examples related to geospatial