Importing MaxMind’s World Cities Data to MySQL
One of the inevitable early steps in the development of any location-aware app is linking location in the form of latitude and longitude to a city, state, and country. This short tutorial will describe the few steps needed to import MaxMind’s World Cities database into a table in your own MySQL database. These steps assume you have command-line access to your MySQL server.
Get the Data
Be sure to read MaxMind’s specifications about the content and use of their data. Then, download the latest file and extract it:
$ wget http://www.maxmind.com/download/worldcities/worldcitiespop.txt.gz $ gunzip worldcitiespop.txt.gz
Prep the Database
Connect to your database server and get the table structure ready.
$ mysql -h localhost -u dbuser -pdbpass myappdatabase
Depending on the application, it may make sense to index additional columns than the statement below.
mysql> CREATE TABLE cities ( country CHAR(2), city_ascii VARCHAR(100), city VARCHAR(255), region CHAR(2), population INT UNSIGNED, latitude DECIMAL(10, 6), longitude DECIMAL(10, 6), INDEX idx_lat_long (latitude, longitude), INDEX idx_country (country), INDEX idx_region (region) );
Import the Data
I learned about this powerful command over at tech-recipes.com. Learn more about the options for the LOAD DATA command in MySQL’s documentation.
mysql> LOAD DATA LOCAL INFILE 'worldcitiespop.txt' INTO TABLE cities FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (country, city_ascii, city, region, population, latitude, longitude);
Now sit back and wait… At the time of this writing, the database has over 3.1 million rows. On a local windows-based development laptop it took about 8 minutes, but under a minute on a dedicated database server. This could vary depending on available system resources.
When the command completes the data will be ready to use. Interestingly enough, MaxMind’s page references a similar free database from GeoNames. After a brief look at the documentation, it looks like there’s a lot more data that could be useful.
Categorised as: Programming