The Zip Code Database Project With distance calculation!
The Zip Code Database project exists to provide US Zip Codes in their entirety; latitude and longitude coordinates included! The downloads are in CSV and MySQL table dump formats.
News
- March 6, 2006
- Peter Santoro has graciously donated a bash script that converts from CSV to SQL Inserts. It is generic SQL and should work in every SQL-compliant database.
- March 3, 2006
- Site info updated: Added a FAQ section and added distance calculation for Excel by Jonathan Colson.
- February 8, 2006
- Thanks to Rob Sterner, the distance calculation formula has been written up in Java. See below.
- November 21, 2005
-
The Zip Code Database Project has now ported all US Zip Code data (with latitude and
longitude coordinates) to the comma-separated-values file format. This will greatly
increase the adoptability for anyone in need of this data.
The two releases today are identical except one is in the zip format, the other in gzip. Download here. - November 11, 2005
- Zip Code Database updated with more data including city and state names. Plus, forums were opened on the SourceForge site.
Download
SourceForge hosts the download here.
Also see the project page for more info.
FAQ
- What is the license for the Zip Code Database Project?
- Public Domain; Use it as you wish.
- Where did this data come from?
- All the longitude/latitude data came from the 2000 U.S. Census. The file was released as a ZCTA (Zip Code Tabulation Areas) on the Gazetteer website.
- When will this data be updated?
-
Unfortunately, we don't know. We will have to find an alternative source for the geographic
data as it seems the gazetteer may not be updated until 2010 (the next census), if at
all.
If you know where to find up-to-date data, please let us know!
Distance Calculation
This is a simple distance calculation method that calculates the distance between two Zip Codes in miles by passing the latitude and longitude coordinates found in the database. Currently supported languages: PHP, Python, Java, Excel.
(Have a way to do it in another language? Submit your code at the email address below. Everyone will love you!)
In Python:
from math import *
def calcDist(lat_A, long_A, lat_B, long_B):
distance = (sin(radians(lat_A)) *
sin(radians(lat_B)) +
cos(radians(lat_A)) *
cos(radians(lat_B)) *
cos(radians(long_A - long_B)))
distance = (degrees(acos(distance))) * 69.09
return distance
In PHP:
function calcDist($lat_A, $long_A, $lat_B, $long_B) {
$distance = sin(deg2rad($lat_A))
* sin(deg2rad($lat_B))
+ cos(deg2rad($lat_A))
* cos(deg2rad($lat_B))
* cos(deg2rad($long_A - $long_B));
$distance = (rad2deg(acos($distance))) * 69.09;
return $distance;
}
In Java:
import java.lang.Math;
import java.lang.Double;
public int calcDistance(double latA, double longA, double latB, double longB)
{
double theDistance = (Math.sin(Math.toRadians(latA)) *
Math.sin(Math.toRadians(latB)) +
Math.cos(Math.toRadians(latA)) *
Math.cos(Math.toRadians(latB)) *
Math.cos(Math.toRadians(longA - longB)));
return = (Math.toDegrees(Math.acos(theDistance))) * 69.09;
}
In Excel:
=IF(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1 - Long2) > 1,
RadiusofEarth * ACOS(1), RadiusofEarth *
ACOS(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1-Long2)))
Some notes on this calculation by Jonathan Colson:
Excel uses radians not degrees for trig functions so all latitude and longitudes must be converted to radians before putting them in the above formula. Excel has an intrinsic function =RADIAN(degrees)
RadiusofEarth can be any unit of measure: statute miles, nautical miles, or kilometers. The radius of Earth is 6378.1 kilometers or 3963.1 statute miles.
I used the VLOOKUP function to find the latitude and longitude of my 2 zip codes.
The source for the formula is http://www.mathforum.com/library/drmath/view/51711.html
Contact
Email me at superjared@users.sourceforge.net or post to the discussion forums.
Acknowledgments
Thanks to:
- Casey Smith for updating my previous work!
- Rob Sterner for translating the distance calculation to Java!
- Jonathan Colson for providing the distance calculation for Excel!
- Peter Santoro for the bash script that converts CSV to SQL inserts!