HomeRamblings  ⁄  Ruby LanguageSQL

Geocoded Zipcodes

Published: July 07, 2008 (over 9 years ago)
Updated: over 2 years ago

You would think that loading up a database of zip codes would be an extremely simple case of finding a public database on www.usps.gov, and then loading up with a database bulk load. It turns out that, while the USPS does offer products for you to purchase, and a rather nice lookup interface for looking up zip codes and so on, there’s not really any free data to be had that I could tell. So the hunt was on to find some zip code data, preferably geocoded (latitude/longitude for GIS) zip codes and then load them into MySQL. A bit of hunting around turned up this list of zip code databases. The first link to CivicSpace database appears to be defunct (I got a Go Daddy “this page is parked” page). So, the next free one, offered by Popular Data turned out to be exactly what I was looking for. It may be a “little old” as the review site says, but its good enough for me to get started with associating my data with geocoded locations and explore the wonderful world of mapping offered by Google’s Maps API. To load it up in MySQL and PostgreSQL, I used the following Ruby script to generate a bunch of insert statements:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
require 'rubygems'
require 'fastercsv'
source_file = 'ZIP_CODES.txt'
outfile = File.new('us_zip_codes.sql','w')

# generate the truncate statement if we want to empty the table
outfile.puts "truncate us_zip_codes;"

# generate the SQL Insert statements
FasterCSV.foreach(source_file, :headers => false) do |cols|
  row = "INSERT INTO us_zip_codes VALUES ("
  row << as_string(cols[0]) << ","
  row << as_number(cols[1]) << ","
  row << as_number(cols[2]) << ","
  row << as_string(cols[3]) << ","
  row << as_string(cols[4]) << ","
  row << as_string(cols[5]) << ","
  row << as_string(cols[6]) << ");"
  outfile.puts row
end
outfile.close
puts "copied #{i} records."

Create the zip code table with the following SQL:

1
2
3
4
5
6
7
8
9
create table us_zip_codes (
  zip_code char(5), 
  latitude float(10,6),
  longitude float(10,6),
  city varchar(255),
  state char(2),
  county varchar(255),
  zip_class varchar(255)
);

And finally, with the table in place and the SQL insert statements generated, I loaded the data into MySQL with the following from command line:

mysql target_db_name 
comments powered by Disqus