Thursday, March 22, 2007

Importing zip codes

This entry will show you how to quickly import zip codes into your database. From there, they can be used with AJAX lookups or verification.

Step 1: Download the zips.txt into your RoR (rails) db directory or some other place.
http://www.census.gov/tiger/tms/gazetteer/zips.txt

Step 2: Generate the table in MySQL

CREATE TABLE zip_codes (
id INTEGER NOT NULL AUTO_INCREMENT
, zip CHAR(5)
, state CHAR(2)
, town VARCHAR(50)
, population INTEGER
, PRIMARY KEY (id)
);


Step 3: Generate the model

ruby script\generator scaffold zip_code


Step 4: Run this import code

require 'csv'
CSV.open("#{RAILS_ROOT}/db/zips.txt", "r") do |row|
zip = ZipCode.new
zip.zip = row[1]
zip.state = row[2]
zip.town = row[3]
zip.population = row[6]
zip.save!
end


You can then monitor the table size and wait for it to grow to 29k+.

1 comment:

ian connor said...

I also exported the SQL so you can run this instead.
zipcodes-sql.zip