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 
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) |
Hi,
I would like to do same thing as above but for India.
Is that possible?
AllCountries database seems to have very less number of records for India. Any idea how we could proceed?
Appreciate your response.
Priya
It should be possible but the detail to which such data is available varies from one country to another; although not 100% certain about it, I am willing to bet Geonames.org gets some of this data from the local governments and the ability of these governments to provide such data depends significantly on the technological tools they have access to [and willingness to do it].
One other thing you could do is to spread the word to your friends/contacts across India and get them to help you supply some coordinates [gathered using handheld GPS, etc.]. You can then add that information, in a format compliant with AllCountries database, to your local copy.
If I do find more information, I will pass it along.