«   »

Importing Geonames.org Data Into MySQL

What is Geonames.org, you wonder? It’s a website [or an web-institution, if you like to phrase it that way] that provides – amongst other things – [pretty] useful information that maps geological data [country, region, city/town/village, etc.] to the GPS coordinates. The one database that I personally use corresponds to allCountries [download size is about 170MB ; requires about 800MB upon uncompressing it].


Why store this data in MySQL?

Personally, I keep a detailed record of where I have been using my trusted travel companion, Garmin GPSMap 60CSx. If the geonames.org data could be stored locally in MySQL format, then it makes the process of geotagging relatively quicker [check out almost any photo in my portfolio, for example]. And geotagging is just one of the many potential applications – computing distance between any to points/locations using [and thus testing the validity of] Haversine Formula or Spherical Law of Cosines could be another, and so on.


MySQL table structure

Upon reading through the geonames.org manual/documentation and browsing through the allCountries.txt, I found that the following table structure fits my requirements just fine. Name of the MySQL table should be the same as the base name of the file that contains data.


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 ;

It’s recommended that the UTF-8 be the character set defined for this table. While the readme.txt that comes with the download explains what these fields mean, they are included below for the sake of completeness.


geonameid         : integer id of record in geonames database
name              : name of geographical point (utf8) varchar(200)
asciiname         : name of geographical point in plain ascii characters, varchar(200)
alternatenames    : alternatenames, comma separated varchar(4000) (varchar(5000) for SQL Server)
latitude          : latitude in decimal degrees (wgs84)
longitude         : longitude in decimal degrees (wgs84)
feature class     : see http://www.geonames.org/export/codes.html, char(1)
feature code      : see http://www.geonames.org/export/codes.html, varchar(10)
country code      : ISO-3166 2-letter country code, 2 characters
cc2               : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
admin1 code       : fipscode (subject to change to iso code), isocode for the us and ch, see file admin1Codes.txt for display names of this code; varchar(20)
admin2 code       : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80)
admin3 code       : code for third level administrative division, varchar(20)
admin4 code       : code for fourth level administrative division, varchar(20)
population        : bigint (4 byte int)
elevation         : in meters, integer
gtopo30           : average elevation of 30'x30' (ca 900mx900m) area in meters, integer
timezone          : the timezone id (see file timeZone.txt)
modification date : date of last modification in yyyy-MM-dd format


Importing the data into MySQL

mysqlimport -h localhost -u MySQL-USER -p --fields-terminated-by='\t' --lines-terminated-by='\n' --local MyDatabase /path-to/allCountries.txt
# Enter password when prompted.

Given that there about 6 million entries in allCountries.txt, it will take a while for all of them to be imported.

3 responses to “Importing Geonames.org Data Into MySQL

  1. […] 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 […]

  2. […] Importing Geonames.org Data Into MySQL | PHP – Calculating Distance Between Two Locations Given Their GPS Coordinates […]

  3. […] 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 […]



Comments are closed.