Panayotis Matsinopoulos

Senior Software Engineer: Love Creating Software, Taking Entrepreneurial Risks, Teaching Computer Programming & Blogging here or in many other places

United States, Counties, Cities, Zips CSV & Ruby Loading Code

| Comments

I just wanted to have the United States, their Counties, the Cities in the Counties and the Zip codes in each City in CSV format. But I wanted that normalized. In different CSVs. So, here it is. You can also find here some Ruby on Rails sample code that loads these files. Primitive stuff, but sometimes useful.

My sponsor on that is Book&Table
I am about to start a Seminar: Introduction to Programming. If you are interested in, register here.
  • Here is the list of states.
  • Here is the list of counties. It has all counties and which State they belong to.
  • Here is the list of cities. It has all the cities and the County they belong to.
  • Here is the list of zip codes. It has all the zip codes and the city they correspond to (specified by city name, county name and state code).

ActiveRecord Models

State

1
2
3
4
5
6
# State
class State < ActiveRecord::Base
  has_many :counties, inverse_of: :state, dependent: :destroy
  validates :name,           presence: true, uniqueness: {case_sensitive: false}
  validates :two_digit_code, presence: true, uniqueness: {case_sensitivie: false}
end

County

1
2
3
4
5
6
7
8
# County
class County < ActiveRecord::Base
  belongs_to :state, inverse_of: :counties
  has_many   :cities, inverse_of: :county, dependent: :destroy

  validates :state, presence: true
  validates :name,  presence: true, uniqueness: {case_sensitive: false, scope: :state_id}
end

City

1
2
3
4
5
6
7
# City
class City < ActiveRecord::Base
  belongs_to :county, inverse_of: :cities

  validates :county, presence: true
  validates :name,   presence: true, uniqueness: {case_sensitive: false, scope: :county_id}
end

Zip

1
2
3
4
5
6
7
# Zip
class Zip < ActiveRecord::Base
  belongs_to :city, inverse_of: :zips

  validates :city, presence: true
  validates :code, presence: true, uniqueness: {case_sensitive: false}
end

Loading States

1
2
3
4
5
6
7
puts "Loading States..."
CSV.foreach("#{Rails.root}/db/states.csv") do |row|
  state_code = row[0]
  state_name = row[1]
  State.create_with(name: state_name).find_or_create_by!(two_digit_code: state_code)
end
puts "...end of loading States"```

Loading Counties

1
2
3
4
5
6
7
puts "Loading Counties..."
CSV.foreach("#{Rails.root}/db/counties.csv") do |row|
  state_code  = row[0]
  county_name = row[1]
  County.find_or_create_by!(state_id: State.find_by_two_digit_code(state_code).id, name: county_name)
end
puts "...end of loading Counties"

Loading Cities

1
2
3
4
5
6
7
8
9
10
11
12
13
puts "Loading Cities..."

CSV.foreach("#{Rails.root}/db/cities.csv") do |row|
  state_two_digit_code = row[0]
  state_id = State.find_by_two_digit_code!(state_two_digit_code).id

  county_name = row[1]
  city_name   = row[2]
  county      = County.find_by_state_id_and_name!(state_id, county_name)
  City.find_or_create_by!(county_id: county.id, name: city_name)
end

puts "...end of loading Cities"

Loading Zip Codes

1
2
3
4
5
6
7
8
9
10
11
12
13
puts "Loading Zips..."

CSV.foreach "#{Rails.root}/db/zips.csv" do |row|
  zip_code  = row[0]
  city      = row[1]
  county    = row[2]
  state     = row[3]
  state_id  = State.find_by_two_digit_code!(state).id
  county_id = County.find_by_name_and_state_id!(county, state_id).id
  Zip.create_with(city_id: City.find_by_name_and_county_id!(city, county_id).id).find_or_create_by!(code: zip_code)
end

puts "...end of loading Zips"
My sponsor on that is Book&Table
I am about to start a Seminar: Introduction to Programming. If you are interested in, register here.

Comments