« Subversion – Backing Up Repository | MySQL – Finding Nearest Record In Date/Time »
Importing Geonames.org Data Into MySQL
July 29th, 2009 @ 13:17:52 | GPS, 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.
Here a wealth of information here. Thanks! I’ll be back for more
truly loved the article added to my favourites
Thank you very much for that great article
[...] 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 [...]
[...] Importing Geonames.org Data Into MySQL | PHP – Calculating Distance Between Two Locations Given Their GPS Coordinates [...]
The holy passion of friendship is so sweet and steady and loyal and enduring in nature that it will last through a whole lifetime, if not asked to lend money.
[...] 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 [...]
Great Blog! We’ve a similar site, and acquire a lot of spam. Would you face such problems? Can you please recommend some steps I decide on combat spam. Thanks.
Please analyze more where can i buy portable gps cheapest.I needed gps tools.My friend want it this month.
Maybe you should make changes to the post name title Gowtham » Seventh Sense
» Importing Geonames.org Data Into MySQL to more suited for your content you create. I enjoyed the the writing all the same.