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 Replies to “Importing Geonames.org Data Into MySQL”