jeffbrand.net

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.

That’s it!

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.

Comments Off on Importing MaxMind’s World Cities Data to MySQL