US Zipcode API for Ruby?

Discussion in 'Ruby' started by Mark Ericson, Dec 13, 2005.

  1. Mark Ericson

    Mark Ericson Guest

    ------=_Part_17217_18149005.1134496009606
    Content-Type: text/plain; charset=ISO-8859-1
    Content-Transfer-Encoding: quoted-printable
    Content-Disposition: inline

    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.

    ------=_Part_17217_18149005.1134496009606--
     
    Mark Ericson, Dec 13, 2005
    #1
    1. Advertising

  2. Mark Ericson

    Dan Diebolt Guest

    --0-967764346-1134496775=:71882
    Content-Type: text/plain; charset=iso-8859-1
    Content-Transfer-Encoding: quoted-printable

    There is a 40,000+ zip code database in CivicSpace labs that has lat and =
    long by zip code:
    =20
    http://civicspacelabs.org/home/developers/download
    =20
    http://civicspacelabs.org/releases/zipcodes/zipcodes-csv-10-Aug-2004.zi=
    p
    =20
    Distance between two points (lat, long) is calculated using the Haversi=
    ne formula:
    =20
    dlon =3D lon2 - lon1
    dlat =3D lat2 - lat1
    a =3D (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
    c =3D 2 * atan2(sqrt(a), sqrt(1-a))=20
    d =3D R * c

    See the following link or google for more:=20
    =20
    http://mathforum.org/library/drmath/view/51879.html

    =09
    ---------------------------------
    Yahoo! Shopping
    Find Great Deals on Holiday Gifts at Yahoo! Shopping=20
    --0-967764346-1134496775=:71882--
     
    Dan Diebolt, Dec 13, 2005
    #2
    1. Advertising

  3. Mark Ericson

    Mark Ericson Guest

    ------=_Part_17745_26238748.1134497327587
    Content-Type: text/plain; charset=ISO-8859-1
    Content-Transfer-Encoding: quoted-printable
    Content-Disposition: inline

    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

    On 12/13/05, Dan Diebolt <> wrote:
    >
    > 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/zipcodes-csv-10-Aug-2004.zi=

    p
    >
    > Distance between two points (lat, long) is calculated using the
    > Haversine formula:
    >
    > dlon =3D lon2 - lon1
    > dlat =3D lat2 - lat1
    > a =3D (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
    > c =3D 2 * atan2(sqrt(a), sqrt(1-a))
    > d =3D R * c
    >
    > See the following link or google for more:
    >
    > http://mathforum.org/library/drmath/view/51879.html
    >
    >
    > ---------------------------------
    > Yahoo! Shopping
    > Find Great Deals on Holiday Gifts at Yahoo! Shopping
    >


    ------=_Part_17745_26238748.1134497327587--
     
    Mark Ericson, Dec 13, 2005
    #3
  4. Mark Ericson

    Dan Diebolt Guest

    --0-533268413-1134498534=:63921
    Content-Type: text/plain; charset=iso-8859-1
    Content-Transfer-Encoding: quoted-printable

    >The only thing remaining is an efficient algorithm for a search for all =

    zipcodes within a given radius.
    =20
    http://www.4guysfromrolla.com/webtech/040100-1.shtml
    =20
    =20
    ' THIS VARIABLE SETS THE RADIUS IN MILES iRadius =3D 150 LatRange =3D=
    iradius / ((6076 / 5280) * 60) LongRange =3D iRadius / (((cos(cdbl(iSta=
    rtLat * _ 3.141592653589 / 180)) * 6076.) / 5280.) * 60) =
    LowLatitude =3D istartlat - LatRange HighLatitude =3D istartlat + LatRan=
    ge LowLongitude =3D istartlong - LongRange HighLongitude =3D istartlong=
    + LongRange =20


    =09
    ---------------------------------
    Yahoo! Shopping
    Find Great Deals on Holiday Gifts at Yahoo! Shopping=20
    --0-533268413-1134498534=:63921--
     
    Dan Diebolt, Dec 13, 2005
    #4
  5. Mark Ericson

    Paul Duncan Guest

    --HAv5+T9jbwMPl6Kw
    Content-Type: text/plain; charset=us-ascii
    Content-Disposition: inline
    Content-Transfer-Encoding: quoted-printable

    * Mark Ericson () wrote:
    > Excellent! The only thing remaining is an efficient algorithm for a sear=

    ch
    > 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=20
    http://pablotron.org/files/zipfind.tar.gz):

    ---- import.rb ----
    #!/usr/bin/env ruby
    =20
    # load libraries
    require 'rubygems' rescue nil
    require 'sqlite3'
    =20
    # constants
    SCAN_RE =3D /"(\d{5})","([^"]+)","(..)","([\d.-]+)","([\d.-]+)","([\d-]+)=
    ","(\d)"/
    SQL =3D "INSERT INTO zips(zip, city, state, lat, long, timezone, dst)=20
    VALUES (?, ?, ?, ?, ?, ?, ?)"
    TABLE_SCHEMA =3D "CREATE TABLE zips (
    id INTEGER NOT NULL PRIMARY KEY,
    =20
    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
    );"
    =20
    =20
    # handle command-line arguments
    unless ARGV.size =3D=3D 2
    $stderr.puts "Usage: #$0 <csv> <db>"
    exit -1
    end
    csv_path, db_path =3D ARGV
    =20
    # load database, create zip table and prepared statement
    db =3D SQLite3::Database.new(db_path)
    db.query(TABLE_SCHEMA)
    st =3D db.prepare(SQL)
    =20
    # 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
    =20
    require 'rubygems'
    require 'sqlite3'
    =20
    MI_R =3D 1.15
    =20
    # grab base zip code
    unless ARGV.size > 1
    $stderr.puts "Usage: #$0 <db> <zipcode> [radius]"
    exit -1
    end
    db_path, src_zip, radius =3D ARGV
    radius =3D (radius || 50).to_i
    =20
    # open database
    db =3D SQLite3::Database.new(db_path)
    =20
    # get lat/long for specified zip code
    sql =3D "SELECT lat, long FROM zips WHERE zip =3D ?"
    src_lat, src_long =3D db.get_first_row(sql, src_zip).map { |v| v.to_f }
    =20
    unless src_lat && src_long
    $stderr.puts "Unknown zip code '#{src_zip}'"
    exit -1
    end
    =20
    # calculate min/max lat/long
    ret, range =3D [], radius / 69.0
    =20
    # get all codes within given rectangle
    sql =3D "SELECT lat, long, city, state, zip
    FROM zips=20
    WHERE lat > ? AND lat < ?
    AND long > ? AND long < ?"
    args =3D [src_lat - range, src_lat + range,=20
    src_long - range, src_long + range]
    =20
    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 =3D row
    dst_lat, dst_long =3D dst_lat.to_f, dst_long.to_f
    =20
    # calculate distance between zip codes. if dst_zip is within the
    # specified radius, then add it to the list of results
    d =3D Math.sqrt((dst_lat - src_lat) ** 2 + (dst_long - src_long) ** 2)
    ret << [dst_zip, dst_city, dist_st, d * 69.0] if d <=3D range
    end
    =20
    # sort results by distance
    ret =3D ret.sort { |a, b| a[-1] <=3D> b[-1] }
    =20
    # print out results as a CSV
    puts '"city","state","zip","distance (mi)"',=20
    ret.map { |row| '"' << row.join('","') << '"' }
    ----

    > I suppose one technique might be to first narrow the databse search withi=

    n a
    > given a given square latitude/longitude range and then filter those resul=

    ts
    > 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. =20



    --=20
    Paul Duncan <> pabs in #ruby-lang (OPN IRC)
    http://www.pablotron.org/ OpenPGP Key ID: 0x82C29562

    --HAv5+T9jbwMPl6Kw
    Content-Type: application/pgp-signature; name="signature.asc"
    Content-Description: Digital signature
    Content-Disposition: inline

    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.4.0 (GNU/Linux)

    iD8DBQFDoOcjzdlT34LClWIRAh74AJ0Y3trUYm6otAH8s5uAoP6W37Uq7ACbB2cJ
    YiyjVF67Q3A5eo/bPnwL5cI=
    =q2s1
    -----END PGP SIGNATURE-----

    --HAv5+T9jbwMPl6Kw--
     
    Paul Duncan, Dec 15, 2005
    #5
  6. Mark Ericson

    Mark Ericson Guest

    ------=_Part_673_2012695.1134619192078
    Content-Type: text/plain; charset=ISO-8859-1
    Content-Transfer-Encoding: quoted-printable
    Content-Disposition: inline

    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=3DSQLOLEDB;Data Source=3Dlocalhost;Initial
    Catalog=3DUSZipCodes;User Id=3Dtest;Password=3Dtest") do | dbh |

    sql =3D "INSERT INTO ZipData (zipcode, city, state, latitude, longitude=
    ,
    timezone, dst) VALUES (?, ?, ?, ?, ?, ?, ?)"
    dbh.prepare(sql) do | sth |
    begin
    rdr =3D CSV.open("zipcode.csv", "r")
    header =3D 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



    On 12/14/05, Paul Duncan <> wrote:
    >
    > 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 =3D
    > /"(\d{5})","([^"]+)","(..)","([\d.-]+)","([\d.-]+)","([\d-]+)","(\d)"/
    > SQL =3D "INSERT INTO zips(zip, city, state, lat, long, timezone, dst)
    > VALUES (?, ?, ?, ?, ?, ?, ?)"
    > TABLE_SCHEMA =3D "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 =3D=3D 2
    > $stderr.puts "Usage: #$0 <csv> <db>"
    > exit -1
    > end
    > csv_path, db_path =3D ARGV
    >
    > # load database, create zip table and prepared statement
    > db =3D SQLite3::Database.new(db_path)
    > db.query(TABLE_SCHEMA)
    > st =3D 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) }
    > }
    >


    ------=_Part_673_2012695.1134619192078--
     
    Mark Ericson, Dec 15, 2005
    #6
  7. Mark Ericson

    Steve Litt Guest

    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
     
    Steve Litt, Dec 15, 2005
    #7
  8. Mark Ericson

    Mark Ericson Guest

    Mark Ericson, Dec 15, 2005
    #8
  9. Mark Ericson

    Dan Diebolt Guest

    --0-446491664-1134670621=:19265
    Content-Type: text/plain; charset=iso-8859-1
    Content-Transfer-Encoding: quoted-printable

    CivicSpaceLabs have that zip code file of about 40,000 zips. Commercial p=
    ackages and the USPS sell zip code programs and database that are over tw=
    ice 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=
    :
    =20
    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
    =20
    Maybe will will see a geo-tagging based ruby quiz in the future ...

    =09
    ---------------------------------
    Yahoo! Shopping
    Find Great Deals on Holiday Gifts at Yahoo! Shopping=20
    --0-446491664-1134670621=:19265--
     
    Dan Diebolt, Dec 15, 2005
    #9
  10. Mark Ericson

    Paul Duncan Guest

    --ATYltwmfWCpDp8Ax
    Content-Type: text/plain; charset=us-ascii
    Content-Disposition: inline
    Content-Transfer-Encoding: quoted-printable

    * Steve Litt () wrote:
    > On Wednesday 14 December 2005 10:46 pm, Paul Duncan wrote:
    >=20
    > > pabs@halcyon:~/proj/zip> ./find.rb zipcode.db 22003 3

    >=20
    > 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:
    =20
    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.

    > SteveT
    >=20
    > Steve Litt
    > http://www.troubleshooters.com
    >


    --=20
    Paul Duncan <> pabs in #ruby-lang (OPN IRC)
    http://www.pablotron.org/ OpenPGP Key ID: 0x82C29562

    --ATYltwmfWCpDp8Ax
    Content-Type: application/pgp-signature; name="signature.asc"
    Content-Description: Digital signature
    Content-Disposition: inline

    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.4.0 (GNU/Linux)

    iD8DBQFDofIMzdlT34LClWIRAqs5AKCpL3lNteGWeUq7V0sdujGnPAJpIACgupIU
    Kbo7KrRXiAB9nt8WXTP3CZ0=
    =2r3h
    -----END PGP SIGNATURE-----

    --ATYltwmfWCpDp8Ax--
     
    Paul Duncan, Dec 15, 2005
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. DaveF

    ZIPCODE DB ACCESS TO SQL

    DaveF, Sep 29, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    440
    Scott M.
    Sep 30, 2004
  2. Jeff Thur

    Need to Format a zipcode into xxxxx-xxxx.

    Jeff Thur, Feb 18, 2005, in forum: ASP .Net Datagrid Control
    Replies:
    1
    Views:
    199
  3. Jeff Thur
    Replies:
    1
    Views:
    151
    Alvin Bruney [MVP]
    Feb 17, 2005
  4. Irishmaninusa

    Canada ZipCode Finder

    Irishmaninusa, Aug 18, 2003, in forum: ASP General
    Replies:
    1
    Views:
    128
    Aaron Bertrand - MVP
    Aug 18, 2003
  5. William Morris

    Zipcode lookup by proximity...

    William Morris, Jun 9, 2004, in forum: ASP General
    Replies:
    8
    Views:
    208
    Aaron [SQL Server MVP]
    Jun 11, 2004
Loading...

Share This Page