Importing Data Into MySQL

What is, 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 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 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'

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, char(1)
feature code      : see, 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 Data Into MySQL”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.