gistfile1.rb

# an ActiveRecord based solution to http://www.ruby-forum.com/topic/206201
# based on Peter Song's solution


# if anyone is interested in making a DataMapper version of this
# I'd be interested to see how it relates to ActiveRecord :)

require 'rubygems'
require 'hpricot'
require 'open-uri'
require 'active_record'


# I'm sure there is a nicer way to do this
# but IDK how to tell it to do nothing if the table already exists
# and file based migrations seem like overkill here, so I do it w/ sql
ActiveRecord::Base.instance_eval do
  establish_connection :adapter => 'sqlite3' , :database => 'cities.sqlite3'
  connection.execute <<-ADD_CITIES
    create table if not exists cities (
      id          INTEGER PRIMARY KEY   ,
      name        TEXT                  ,
      state       TEXT                  ,
      country     TEXT                  ,
      latitude    NUMERIC               ,
      longitude   NUMERIC
    );
  ADD_CITIES
end


# this class maps our city to the DB
# see the section called "Models" at http://guides.rubyonrails.org/
class City < ActiveRecord::Base
  # validate the record
  validates_presence_of        :name , :state , :country
  validates_numericality_of    :latitude , :longitude , :allow_nil => false
  before_validation            :set_lat_lon
private

  # look at the name , state , country and find the latitude and longitude
  def set_lat_lon
    address = URI.escape "http://worldkit.org/geocoder/rest/?city=#{name},#{state},#{country}"
    open address do |page|
      result = Hpricot(page)
      self.longitude = ( result % '//geo:long' ).innerHTML
      self.latitude  = ( result % '//geo:lat'  ).innerHTML
    end
  rescue # if they submitted bad data, it will fail validations, don't worry about exceptions
  end
end


# there is a bunch of data at the end of the file that I just got w/ a quick google search for cities
# It saves all but a few of them, because their top query result is their Google Map location which is predictably formatted, and locatable
# this function queries google for the information, and then parses the result to find the city and state
# it then saves that record into the db, and outputs whether it succeeded or not
def populate
  DATA.each do |city_details|
    begin
      address       =  URI.escape "http://www.google.com/search?q=#{city_details}"      # escape the query so things like spaces don't mess it up
      result        =  Hpricot(open address)                                            # open the page and parse it
      name , state  =  ( result % "#res div ol li.g h3.r a.l b" ).innerHTML.split(', ') # find the element on the page that has the name and state
      city          =  City.new :name => name , :state => state , :country => 'US'
      if city.save
        puts "Successfully created #{"%20s" % city.name} , #{city.state} , #{city.country} , (#{"%8.2f" % city.latitude},#{"%8.2f" % city.longitude})"
      else
        puts "#{city.inspect} failed to save from #{city_details}"
      end
    rescue
      puts "Could not generate a city from #{city_details}"
    end
  end
end


# call the above function
populate

# inspect all the records
City.all.each do |city|
  p city
end

# the cities copied from http://www.infoplease.com/ipa/A0108477.html
__END__
Albuquerque, N.M.
Arlington, Texas
Atlanta, Ga.
Austin, Tex.
Baltimore, Md.
Boston, Mass.
Charlotte, N.C.
Chicago, Ill.
Cleveland, Ohio
Colorado Springs, Colo.
Columbus, Ohio
Dallas, Tex.
Denver, Colo.
Detroit, Mich.
El Paso, Tex.
Fort Worth, Tex.
Fresno, Calif.
Honolulu, Hawaii
Houston, Tex.
Indianapolis, Ind.
Jacksonville, Fla.
Kansas City, Mo.
Las Vegas, Nev.
Long Beach, Calif.
Los Angeles, Calif.
Louisville/Jefferson County, Ky.
Memphis, Tenn.
Mesa, Ariz.
Miami, Fla.
Milwaukee, Wis.
Minneapolis, Minn.
Nashville-Davidson, Tenn.
New Orleans, La.
New York, N.Y.
Oakland, Calif.
Oklahoma City, Okla.
Omaha, Nebr.
Philadelphia, Pa.
Phoenix, Ariz.
Portland, Ore.
Sacramento, Calif.
St. Louis, Mo.
San Antonio, Tex.
San Diego, Calif.
San Francisco, Calif.
San Jose, Calif.
Seattle, Wash.
Tucson, Ariz.
Tulsa, Okla.
Virginia Beach, Va.
Washington, DC
Wichita, Kans.