Forum: Ruby US Zipcode API for Ruby?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Aa1f23332dbb5408a55ed190648ba172?d=identicon&s=25 Mark Ericson (Guest)
on 2005-12-13 18:48
(Received via mailing list)
Does anyone know of API (and database) for Ruby to provide information
(city/state) for zipcodes?  Also helpful would be zipcodes within a
given
radious.

If something doesn't exist natively for Ruby I might utilize a web
service.
Ad97b577f331ae29ed90da5751f2e44f?d=identicon&s=25 Dan Diebolt (dandiebolt)
on 2005-12-13 19:03
(Received via mailing list)
There is a 40,000+ zip code database in CivicSpace labs that has lat and
long by zip code:

  http://civicspacelabs.org/home/developers/download

  http://civicspacelabs.org/releases/zipcodes/zipcod...

  Distance between two points (lat, long) is calculated using the
Haversine formula:

    dlon = lon2 - lon1
  dlat = lat2 - lat1
  a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
  c = 2 * atan2(sqrt(a), sqrt(1-a))
  d = R * c

  See the following link or google for more:

  http://mathforum.org/library/drmath/view/51879.html
Aa1f23332dbb5408a55ed190648ba172?d=identicon&s=25 Mark Ericson (Guest)
on 2005-12-13 19:12
(Received via mailing list)
Excellent!  The only thing remaining is an efficient algorithm for a
search
for all zipcodes within a given radius.

I suppose one technique might be to first narrow the databse search
within a
given a given square latitude/longitude range and then filter those
results
by testing that they are within the given circle radius
Ad97b577f331ae29ed90da5751f2e44f?d=identicon&s=25 Dan Diebolt (dandiebolt)
on 2005-12-13 19:30
(Received via mailing list)
>The only thing remaining is an efficient algorithm for a search for all zipcodes within a 
given radius.

  http://www.4guysfromrolla.com/webtech/040100-1.shtml


' THIS VARIABLE SETS THE RADIUS IN MILES  iRadius = 150    LatRange =
iradius / ((6076 / 5280) * 60)  LongRange = iRadius /
(((cos(cdbl(iStartLat * _              3.141592653589 / 180)) * 6076.) /
5280.) * 60)    LowLatitude = istartlat - LatRange  HighLatitude =
istartlat + LatRange  LowLongitude = istartlong - LongRange
HighLongitude = istartlong + LongRange
Ff260830c27224f0e15f37362a6256d0?d=identicon&s=25 Paul Duncan (Guest)
on 2005-12-15 04:49
(Received via mailing list)
* Mark Ericson (mark.ericson@gmail.com) wrote:
> Excellent!  The only thing remaining is an efficient algorithm for a search
> for all zipcodes within a given radius.

Using Ruby and SQLite3:

  pabs@halcyon:~/proj/zip> ./import.rb zipcode.{csv,db}
  pabs@halcyon:~/proj/zip> ./find.rb zipcode.db 22003 3
  "city","state","zip","distance (mi)"
  "Annandale","VA","22003","0.0"
  "Springfield","VA","22161","1.62363604423677"
  "Springfield","VA","22151","1.87190097838136"
  "Falls Church","VA","22042","2.97362028549975"

Here's the code for each piece (also available at the URL
http://pablotron.org/files/zipfind.tar.gz):

  ---- import.rb ----
  #!/usr/bin/env ruby

  # load libraries
  require 'rubygems' rescue nil
  require 'sqlite3'

  # constants
  SCAN_RE =
/"(\d{5})","([^"]+)","(..)","([\d.-]+)","([\d.-]+)","([\d-]+)","(\d)"/
  SQL = "INSERT INTO zips(zip, city, state, lat, long, timezone, dst)
                VALUES (?, ?, ?, ?, ?, ?, ?)"
  TABLE_SCHEMA = "CREATE TABLE zips (
    id        INTEGER     NOT NULL PRIMARY KEY,

    zip       VARCHAR(5)  NOT NULL,
    city      TEXT        NOT NULL,
    state     VARCHAR(2)  NOT NULL,
    lat       FLOAT       NOT NULL,
    long      FLOAT       NOT NULL,
    timezone  INTEGER     NOT NULL,
    dst       BOOLEAN     NOT NULL
  );"


  # handle command-line arguments
  unless ARGV.size == 2
    $stderr.puts "Usage: #$0 <csv> <db>"
    exit -1
  end
  csv_path, db_path = ARGV

  # load database, create zip table and prepared statement
  db = SQLite3::Database.new(db_path)
  db.query(TABLE_SCHEMA)
  st = db.prepare(SQL)

  # parse CSV and add each line to the database
  db.transaction {
    File.read(csv_path).scan(SCAN_RE).each { |row| st.execute(*row) }
  }
  ----------

  ---- find.rb ----
  #!/usr/bin/env ruby

  require 'rubygems'
  require 'sqlite3'

  MI_R = 1.15

  # grab base zip code
  unless ARGV.size > 1
    $stderr.puts "Usage: #$0 <db> <zipcode> [radius]"
    exit -1
  end
  db_path, src_zip, radius = ARGV
  radius = (radius || 50).to_i

  # open database
  db = SQLite3::Database.new(db_path)

  # get lat/long for specified zip code
  sql = "SELECT lat, long FROM zips WHERE zip = ?"
  src_lat, src_long = db.get_first_row(sql, src_zip).map { |v| v.to_f }

  unless src_lat && src_long
    $stderr.puts "Unknown zip code '#{src_zip}'"
    exit -1
  end

  # calculate min/max lat/long
  ret, range = [], radius / 69.0

  # get all codes within given rectangle
  sql = "SELECT lat, long, city, state, zip
           FROM zips
          WHERE lat > ? AND lat < ?
            AND long > ? AND long < ?"
  args = [src_lat - range, src_lat + range,
          src_long - range, src_long + range]

  db.prepare(sql).execute(*args).each do |row|
    # get row values, convert lat/long to floats
    dst_lat, dst_long, dst_zip, dst_city, dist_st  = row
    dst_lat, dst_long = dst_lat.to_f, dst_long.to_f

    # calculate distance between zip codes.  if dst_zip is within the
    # specified radius, then add it to the list of results
    d = Math.sqrt((dst_lat - src_lat) ** 2 + (dst_long - src_long) ** 2)
    ret << [dst_zip, dst_city, dist_st, d * 69.0] if d <= range
  end

  # sort results by distance
  ret = ret.sort { |a, b| a[-1] <=> b[-1] }

  # print out results as a CSV
  puts '"city","state","zip","distance (mi)"',
       ret.map { |row| '"' << row.join('","') << '"' }
  ----

> I suppose one technique might be to first narrow the databse search within a
> given a given square latitude/longitude range and then filter those results
> by testing that they are within the given circle radius

That's all the code above does.  There's some room for optimization
there; for example, you could create a region field, then calculate list
of regions that intersect with the search radius.  If you index on the
region field, then the query becomes essentially an index lookup instead
of a lat/long comparison (you still have to do the second distance
calculation, of course).

Anyway, I didn't do that because the code above runs pretty quickly on
my machine.
Aa1f23332dbb5408a55ed190648ba172?d=identicon&s=25 Mark Ericson (Guest)
on 2005-12-15 05:01
(Received via mailing list)
Excellent!  You beat me to it.  My approach to import was somewhat
different, your probably has the advantage of a transaction per row.

require 'csv'
require 'dbi'

DBI.connect("DBI:ADO:Provider=SQLOLEDB;Data Source=localhost;Initial
Catalog=USZipCodes;User Id=test;Password=test") do | dbh |

    sql = "INSERT INTO ZipData (zipcode, city, state, latitude,
longitude,
timezone, dst) VALUES (?, ?, ?, ?, ?, ?, ?)"
    dbh.prepare(sql) do | sth |
      begin
        rdr = CSV.open("zipcode.csv", "r")
        header = rdr.shift  # skip header row
        rdr.each do |row|
          sth.execute(row[0], row[1], row[2], row[3], row[4], row[5],
row[6])
        end
      ensure
        CSV.close unless CSV.nil?
      end
    end
end
1b62a85b59ccab03b84ee5ec378f75b4?d=identicon&s=25 Steve Litt (Guest)
on 2005-12-15 14:42
(Received via mailing list)
On Wednesday 14 December 2005 10:46 pm, Paul Duncan wrote:

>   pabs@halcyon:~/proj/zip> ./find.rb zipcode.db 22003 3

So where does one find zipcode.db?

SteveT

Steve Litt
http://www.troubleshooters.com
slitt@troubleshooters.com
Aa1f23332dbb5408a55ed190648ba172?d=identicon&s=25 Mark Ericson (Guest)
on 2005-12-15 18:55
(Received via mailing list)
> So where does one find zipcode.db?

In an earlier post Dan Diebolt shared this:

There is a 40,000+ zip code database in CivicSpace labs that has lat
and long by zip code:

 http://civicspacelabs.org/home/developers/download

 http://civicspacelabs.org/releases/zipcodes/zipcod...
Ad97b577f331ae29ed90da5751f2e44f?d=identicon&s=25 Dan Diebolt (dandiebolt)
on 2005-12-15 19:19
(Received via mailing list)
CivicSpaceLabs have that zip code file of about 40,000 zips. Commercial
packages and the USPS sell zip code programs and database that are over
twice that large. I believe the CivicSpaceLabs zip code database comes
out of the US Census Bureau. The 4GuysFromRolla article previously given
gave a url to the Gazetterr which is broken - I think this is the
correct url:

  http://www.census.gov/geo/www/gazetteer/gazette.html
http://www.census.gov/tiger/tms/gazetteer/zips.txt
  http://www.census.gov/tiger/tms/gazetteer/zips.zip

  Maybe will will see a geo-tagging based ruby quiz in the future ...
Ff260830c27224f0e15f37362a6256d0?d=identicon&s=25 Paul Duncan (Guest)
on 2005-12-15 23:46
(Received via mailing list)
* Steve Litt (slitt@earthlink.net) wrote:
> On Wednesday 14 December 2005 10:46 pm, Paul Duncan wrote:
>
> >   pabs@halcyon:~/proj/zip> ./find.rb zipcode.db 22003 3
>
> So where does one find zipcode.db?

It's generated from the zipcode CSV pasted in a previous email.

That's what the "./import.rb zipcode.{csv,db}" line does; imports the
contents of the CSV into the database.

Incidentally, I also wrote a quick script to calculate the distance
between two zip codes.  It works the same as the others:

  pabs@halcyon:~/proj/zip/zipfind> ./len.rb ./zipcode.db 22003 97405
  3187.72 miles

I packaged all of them up at the following URL:

  http://pablotron.org/files/zipfind-0.2.tar.gz

Here's the OpenPGP signature for that tarball:

  http://pablotron.org/files/zipfind-0.2.tar.gz.asc

Hope that helps.
This topic is locked and can not be replied to.