MySQL – Finding Locations Nearest To A Given Pair Of GPS Coordinates

Amongst other things, I have the habit of geotagging my photographs and for this purpose, I use my Garmin GPSMap 60CSx, an API I wrote to store tracks in MySQL & Geonames data in a MySQL [please refer to this if you wish to do so as well]. For completeness sake, the MySQL table structure that holds Geonames data is given below:

DROP TABLE IF EXISTS `MyDatabase`.`allCountries` ;
 
CREATE TABLE IF NOT EXISTS `MyDatabase`.`allCountries` (
  `geo_id`               INT(11) UNSIGNED NOT NULL PRIMARY KEY,
  `geo_name`             VARCHAR(200) NOT NULL DEFAULT '',
  `geo_ansiname`         VARCHAR(200) NOT NULL DEFAULT '',
  `geo_alternate_names`  VARCHAR(2000) NOT NULL DEFAULT '',
  `geo_latitude`         DOUBLE PRECISION(11,7) NOT NULL DEFAULT '0',
  `geo_longitude`        DOUBLE PRECISION(11,7) NOT NULL DEFAULT '0',
  `geo_feature_class`    CHAR(1) ,
  `geo_feature_code`     VARCHAR(10) ,
  `geo_country_code`     CHAR(2),
  `geo_country_code2`    VARCHAR(60),
  `geo_admin1_code`      VARCHAR(20) DEFAULT '',
  `geo_admin2_code`      VARCHAR(80) DEFAULT '',
  `geo_admin3_code`      VARCHAR(20) DEFAULT '',
  `geo_admin4_code`      VARCHAR(20) DEFAULT '',
  `geo_population`       BIGINT(11) DEFAULT '0',
  `geo_elevation`        INT(11) DEFAULT '0',
  `geo_gtopo30`          INT(11) DEFAULT '0',
  `geo_timezone`         VARCHAR(40),
  `geo_mod_date`         DATE DEFAULT '0000-00-00'
 
) CHARACTER SET utf8 ;


The work-flow might seem a bit complicated but from a personal perspective, it’s very much worth the effort. My camera and GPS unit are in sync with respect to time – enabling me to easily locate the GPS coordinates nearest in time to a photograph’s time-stamp [please refer to this post] – and, I can then use those GPS coordinates to look up the Geonames data [stored in my local MySQL database] and get details regarding the country, region/state, city/town/village. Once these details, they can be written into EXIF.

If you wish to just calculate the distance between any two given locations using their GPS coordinates &/or wish to see the underlying [entertaining/boring/depressing] mathematics, you may refer to this post.

Spherical Law of Cosines

Suppose that we want to find the five nearest places to (47.470779, -87.890699) using Spherical Law of Cosines, the following MySQL syntax would easily accomplish it:

SELECT geo_latitude, geo_longitude, geo_name, geo_country_code AS country, 
  (DEGREES(
    ACOS(
      SIN(RADIANS(47.470779)) * SIN(RADIANS(geo_latitude)) + 
      COS(RADIANS(47.470779)) * COS(RADIANS(geo_latitude)) * 
      COS(RADIANS(-87.890699 - geo_longitude))
    ) 
  ) * 60 * 1.1515) 
AS distance FROM `MyDatabase`.`allCountries` ORDER BY distance ASC LIMIT 20 ;
 
+--------------+---------------+--------------------------+---------+------------------+
| geo_latitude | geo_longitude | geo_name                 | country | distance         |
+--------------+---------------+--------------------------+---------+------------------+
|   47.4687939 |   -87.8884416 | Copper Harbor            | US      | 0.17298954791301 | 
|   47.4712940 |   -87.8967747 | Copper Harbor Marine     | US      | 0.28597118617703 | 
|   47.4671275 |   -87.8984414 | Brockway Nose            | US      | 0.44091073668603 | 
|   47.4749047 |   -87.8831638 | Porters Island           | US      |   0.452861175025 | 
|   47.4640718 |   -87.8842752 | Garden Brook             | US      | 0.55204707440545 | 
|   47.4669444 |   -87.8750000 | Copper Harbor            | US      | 0.77960573689416 | 
|   47.4712935 |   -87.8714975 | Copper Harbor            | US      | 0.89745791695046 | 
|   47.4671269 |   -87.8698310 | Fort Wilkins State Park  | US      |  1.0067544467887 | 
|   47.4546277 |   -87.8831645 | Lake Manganese           | US      |  1.1700749834931 | 
|   47.4537945 |   -87.8878866 | Aetna Creek              | US      |  1.1807894156494 | 
|   47.4662936 |   -87.8648312 | Old Fort Wilkins         | US      |  1.2472545660058 | 
|   47.4565726 |   -87.9078860 | Keweenaw County Park     | US      |  1.2680081028565 | 
|   47.4635159 |   -87.8642758 | Lake Fanny Hooe          | US      |   1.332234995507 | 
|   47.4507389 |   -87.8828868 | French Annie Creek       | US      |  1.4318523795825 | 
|   47.4743487 |   -87.8603866 | Copper Harbor Lighthouse | US      |  1.4369403253187 | 
|   47.4687930 |   -87.8414984 | Lily Lake                | US      |   2.301927515517 | 
|   47.4624043 |   -87.8370542 | Vulcan Creek             | US      |  2.5714873372428 | 
|   47.4646285 |   -87.9451070 | West Bluff               | US      |  2.5764230913608 | 
|   47.4629598 |   -87.8356654 | Mud Lake                 | US      |  2.6265551424738 | 
|   47.4760170 |   -87.9492732 | Mudd Lake                | US      |  2.7592603752696 | 
+--------------+---------------+--------------------------+---------+------------------+
20 rows in set (24.11 sec)

Haversine Formula

Suppose that we want to find the five nearest places to (47.470779, -87.890699) using Haversine Formula, the following MySQL syntax would easily accomplish it [please refer here to read about the motivation/reasoning behind using \mbox{atan2}() function instead of \sin(\min())].

SELECT geo_latitude, geo_longitude, geo_name, geo_country_code, 
      ((2 * 3960 * 
        ATAN2(
          SQRT(
            POWER(SIN((RADIANS(47.470779 - geo_latitude))/2), 2) +
            COS(RADIANS(geo_latitude)) *
            COS(RADIANS(47.470779 )) *
            POWER(SIN((RADIANS(-87.890699 - geo_longitude))/2), 2)
          ),
          SQRT(1-(
            POWER(SIN((RADIANS(47.470779 - geo_latitude))/2), 2) +
            COS(RADIANS(geo_latitude)) *
            COS(RADIANS(47.470779 )) *
            POWER(SIN((RADIANS(-87.890699 - geo_longitude))/2), 2)
          ))
        )
      )) AS distance  FROM `MyDatabase`.`allCountries` ORDER BY distance LIMIT 20;
 
+--------------+---------------+--------------------------+---------+------------------+
| geo_latitude | geo_longitude | geo_name                 | country | distance         |
+--------------+---------------+--------------------------+---------+------------------+
|   47.4687939 |   -87.8884416 | Copper Harbor            | US      | 0.17305224154353 | 
|   47.4712940 |   -87.8967747 | Copper Harbor Marine     | US      | 0.28607482689209 | 
|   47.4671275 |   -87.8984414 | Brockway Nose            | US      | 0.44107052690488 | 
|   47.4749047 |   -87.8831638 | Porters Island           | US      | 0.45302529198218 | 
|   47.4640718 |   -87.8842752 | Garden Brook             | US      | 0.55224713858662 | 
|   47.4669444 |   -87.8750000 | Copper Harbor            | US      | 0.77988826915573 | 
|   47.4712935 |   -87.8714975 | Copper Harbor            | US      |   0.897783156545 | 
|   47.4671269 |   -87.8698310 | Fort Wilkins State Park  | US      |  1.0071192973228 | 
|   47.4546277 |   -87.8831645 | Lake Manganese           | US      |  1.1704990222635 | 
|   47.4537945 |   -87.8878866 | Aetna Creek              | US      |  1.1812173376871 | 
|   47.4662936 |   -87.8648312 | Old Fort Wilkins         | US      |  1.2477065749721 | 
|   47.4565726 |   -87.9078860 | Keweenaw County Park     | US      |  1.2684676326803 | 
|   47.4635159 |   -87.8642758 | Lake Fanny Hooe          | US      |  1.3327178018892 | 
|   47.4507389 |   -87.8828868 | French Annie Creek       | US      |  1.4323712866872 | 
|   47.4743487 |   -87.8603866 | Copper Harbor Lighthouse | US      |  1.4374610759455 | 
|   47.4687930 |   -87.8414984 | Lily Lake                | US      |  2.3027617390801 | 
|   47.4624043 |   -87.8370542 | Vulcan Creek             | US      |  2.5724192504538 | 
|   47.4646285 |   -87.9451070 | West Bluff               | US      |  2.5773567937977 | 
|   47.4629598 |   -87.8356654 | Mud Lake                 | US      |  2.6275070123166 | 
|   47.4760170 |   -87.9492732 | Mudd Lake                | US      |  2.7602603384345 | 
+--------------+---------------+--------------------------+---------+------------------+
20 rows in set (38.85 sec)

Related Posts