Today we had a need to convert the City and State returned from the MaxMind GEOIP1 database into a ZipCode. Which, as they mention on their site is not entirely accurate using their own database and recommend to go with the iBegin Free ZIPCode database[2] to compare the GEOIP results with to find our zipcode. Fancy.

Since we had to import the ZipCode’s into a MySQL database from a CSV file we had to figure out how to do this, and we’re archiving it here for you and for our own purposes.

First, we added a column to the zipcode database’s csv file, that was just blank. This is where our ID will go in the database.

Before, it looked like this;

ZIPCODE,TOWN,STATE,LONG,LAT,CLOSESTCITY

After our change, it looked like this;

ID,ZIPCODE,TOWN,STATE,LONG,LAT,CLOSESTCITY

Now we re-saved this as a MS-DOS, comma deliminated CSV file.

Next we went ahead and created a database on our database server to hold the newly created zipcode database. It looks like this;

CREATE geo;  
USE DATABASE geo;  
CREATE TABLE IF NOT EXISTS 'zipcodes' (  
'ID' int(12) NOT NULL auto_increment,  
'ZIPCODE' int(128) NOT NULL,  
'TOWN' varchar(128) NOT NULL,  
'STATE' varchar(128) NOT NULL,  
'LONG' text NOT NULL,  
'LAT' text NOT NULL,  
'CLOSESTCITY' varchar(128) NOT NULL,  
PRIMARY KEY ('ID')  
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41757 ;

Next we scp’d the .csv file up to our server with MySQL on it, and connected via the command line.

# mysql -u USERNAME -p DATABASENAME  
password: YOURPASSWORD

After successfully logging into your MySQL console, you will have to insert your records. This is what we did.

mysql> use geo;  
mysql> load data infile "/home/username/zipcode_us.csv" into table zipcodes fields terminated by ',';  
mysql> exit;

This will insert all 42-some thousand entries into your database to have them ready for querying.

Make sure you setup proper permissions on the database for users that can read from it. There is no need to give it write permissions because nothing will ever be written to this particular database, only read.

Depending on your database you might have to go in and delete the first row, because it will be populated with zeros.