Asp Forum - US Zipcode API for Ruby?

Mark Ericson

12/13/2005 5:48:00 PM

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.

9 Answers

Dan Diebolt

12/13/2005 6:00:00 PM

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

http://civicspacelabs.org/home/developer...

http://civicspacelabs.org/releases/zipcodes/zipcodes-csv-10-Au...

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/...

---------------------------------
Yahoo! Shopping
Find Great Deals on Holiday Gifts at Yahoo! Shopping

Mark Ericson

12/13/2005 6:10:00 PM

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 <dandiebolt@yahoo.com> wrote:
>
> There is a 40,000+ zip code database in CivicSpace labs that has lat and
> long by zip code:
>
> http://civicspacelabs.org/home/developer...
>
> http://civicspacelabs.org/releases/zipcodes/zipcodes-csv-10-Au...
>
> 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/...
>
>
> ---------------------------------
> Yahoo! Shopping
> Find Great Deals on Holiday Gifts at Yahoo! Shopping
>

Dan Diebolt

12/13/2005 6:29:00 PM

>The only thing remaining is an efficient algorithm for a search for all zipcodes within a given radius.

http://www.4guysfromrolla.com/webtech/0401...

' 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

---------------------------------
Yahoo! Shopping
Find Great Deals on Holiday Gifts at Yahoo! Shopping

Paul Duncan

12/15/2005 3:47:00 AM

* 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/zipf...):

---- 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.

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

Mark Ericson

12/15/2005 4:00:00 AM

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

On 12/14/05, Paul Duncan <pabs@pablotron.org> 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/zipf...):
>
> ---- 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) }
> }
>

Steve Litt

12/15/2005 1:41:00 PM

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.troublesh...
slitt@troubleshooters.com

Mark Ericson

12/15/2005 5:53:00 PM

> 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/developer...

http://civicspacelabs.org/releases/zipcodes/zipcodes-csv-10-Au...

Dan Diebolt

12/15/2005 6:17:00 PM

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/ga...
http://www.census.gov/tiger/tms/gazettee...
http://www.census.gov/tiger/tms/gazettee...

Maybe will will see a geo-tagging based ruby quiz in the future ...

---------------------------------
Yahoo! Shopping
Find Great Deals on Holiday Gifts at Yahoo! Shopping

Paul Duncan

12/15/2005 10:46:00 PM

* 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-...

Here's the OpenPGP signature for that tarball:

http://pablotron.org/files/zipfind-....asc

Hope that helps.

> SteveT
>
> Steve Litt
> http://www.troublesh...
> slitt@troubleshooters.com

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

comp.lang.ruby

US Zipcode API for Ruby?

Mark Ericson

Dan Diebolt

Mark Ericson

Dan Diebolt

Paul Duncan

Mark Ericson

Steve Litt

Mark Ericson

Dan Diebolt

Paul Duncan

x Login to ForumsZone