# US Zipcode API for Ruby?

**URL:** <https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155>\
**Category:** Ruby\
**Created:** [December 13, 2005, 6:48pm UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155 "2005-12-13T18:48:27Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 13, 2005, 6:48pm UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/1 "2005-12-13T18:48:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 13, 2005, 7:03pm UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/2 "2005-12-13T19:03:31Z")

</div>

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/home/developers/download)

[http://civicspacelabs.org/releases/zipcodes/zipcodes-csv-10-Aug-2004.zip](http://civicspacelabs.org/releases/zipcodes/zipcodes-csv-10-Aug-2004.zip)

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:

[Classroom Resources - National Council of Teachers of Mathematics](http://mathforum.org/library/drmath/view/51879.html)

---

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 13, 2005, 7:12pm UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/3 "2005-12-13T19:12:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 13, 2005, 7:30pm UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/4 "2005-12-13T19:30:35Z")

</div>

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

[ᐈ Betting 101: A Beginner's Journey into the World of Wagers 🥇 eNews](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

---

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 15, 2005, 4:49am UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/5 "2005-12-15T04:49:09Z")

</div>

- Mark E. (removed\_email\_address@domain.invalid) 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](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 ”  
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 [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.

---

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 15, 2005, 2:42pm UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/6 "2005-12-15T14:42:27Z")

</div>

On Wednesday 14 December 2005 10:46 pm, Paul D. wrote:

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

So where does one find zipcode.db?

SteveT

Steve L.

> **[Troubleshooters.Com](http://www.troubleshooters.com)**
>
> The process and mindset of troubleshooting.

removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 15, 2005, 5:01am UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/7 "2005-12-15T05:01:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 15, 2005, 6:55pm UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/8 "2005-12-15T18:55:08Z")

</div>

> So where does one find zipcode.db?

In an earlier post Dan D. 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/home/developers/download)

[http://civicspacelabs.org/releases/zipcodes/zipcodes-csv-10-Aug-2004.zip](http://civicspacelabs.org/releases/zipcodes/zipcodes-csv-10-Aug-2004.zip)

---

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 15, 2005, 7:19pm UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/9 "2005-12-15T19:19:16Z")

</div>

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/geo/www/gazetteer/gazette.html)  
[http://www.census.gov/tiger/tms/gazetteer/zips.txt](http://www.census.gov/tiger/tms/gazetteer/zips.txt)  
[http://www.census.gov/tiger/tms/gazetteer/zips.zip](http://www.census.gov/tiger/tms/gazetteer/zips.zip)

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

---

<div class="post-metadata">

**Author:** ![marke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/6bbea6/32.png) [@marke](https://www.ruby-forum.com/u/marke)\
**Post date:** [December 15, 2005, 11:46pm UTC](https://www.ruby-forum.com/t/us-zipcode-api-for-ruby/50155/10 "2005-12-15T23:46:28Z")

</div>

- Steve L. (removed\_email\_address@domain.invalid) wrote:

> On Wednesday 14 December 2005 10:46 pm, Paul D. 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](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](http://pablotron.org/files/zipfind-0.2.tar.gz.asc)

Hope that helps.