Click here to Skip to main content
15,860,859 members
Articles / Programming Languages / SQL
Article

ZIP Code Utility

Rate me:
Please Sign up or sign in to vote.
4.98/5 (20 votes)
2 Jan 2005CPOL3 min read 175.9K   3.7K   88   23
This article provides an easy method to lookup a U.S. City/State by ZIP Code, or one or more ZIP Codes by City/State. It also describes a method to calculate the distance between two ZIP Codes and find all other ZIP Codes within a radius of X miles of a specified ZIP Code.

Image 1

Introduction

Intrigued by Ben Fry's zipdecode [^] applet, I decided to write a little ZIP Code utility that allows lookups of U.S. locations by ZIP Code, City/State, or all three. Since the data were already in the database in the form of latitude/longitude pairs, I added the capability to find the distance between two points, and to find all other ZIP Codes within a radius of X miles from the original location.

Background

Database

The MS Access database contains the following fields:

Field NameDescription
ZIPThe ZIP Code
LATITUDELatitude coordinate (decimal degrees)
LONGITUDELongitude coordinate (decimal degrees)
CITYCity name
STATEState abbreviation
COUNTYCounty name
ZIP_CLASSZIP Code class

ZIP Code — City/State lookups

The lookups are straightforward database queries using the OleDb* classes.

Distance calculation

To calculate the distance between two points, I used the Haversine Formula, which I found on the Ask Dr. Math web site.

ZIP Codes within a radius of X miles

Most ZIP Codes in the database contain latitude/longitude coordinates. To make the SQL query as simple as possible, I used a square of size 2Rx2R (where R is the radius of the circle) to encompass the search area as shown in the figure below.

Image 2

This has the unfortunate side effect of searching an area ~22% larger than needed, but these "outliers" are filtered out of the result set on the client side before being returned to the calling application. I could have added a stored procedure to perform the distance calculation, but I didn't want to modify the database in any way. That way, if the author decides to update the data, (hopefully) all the users of this library will have to replace the old database file with the new one.

Now, using this approximation, the SQL query becomes as simple as this:

SQL
SELECT * 
FROM ZIP_CODES 
WHERE
    LATITUDE >= <Southern Latitude Line> AND 
    LATITUDE <= <Northern Latitude Line> AND 
    LONGITUDE >= <Western Longitude Line> AND
    LONGITUDE <= <Eastern Longitude Line>

To calculate the Northern/Southern Latitude and Western/Eastern Longitude lines, I again turned to Ask Dr. Math.

Important classes

Class NameDescription
ZipCodeUtilThe ZipCodeUtil class provides methods to lookup City/State by ZIP Code, or ZIP Code by City/State.
LocationA Location represents a City, State, ZIP Code, County, Latitude, Longitude, and ZIP Class. This just so happens to correspond to the columns of the ZIP_CODES table.
LocationInRadiusDerives from Location, and adds the DistanceToCenter property.
DistanceThe Distance class' static GetDistance method takes two Location objects and uses their Latitudes and Longitudes to determine the distance between them.
RadiusProvides a static method that takes a Location and a radius (in miles), and returns the LocationInRadiuses that fall within that radius.

Using the code

Using the code is very straightforward.

  1. Download the ZIP Codes database (see link at top of article).
  2. Compile the ZipCodeUtil library in VS.NET.
  3. Add a reference to the new DLL (SagaraSoftware.ZipCodeUtil.dll) to your application.
  4. Add the following appSettings to your application's config file (you'll need to add a config file if one doesn't already exist):
    XML
    <add key="ZipCodeProviderType" value="Access" />
    XML
    <add key="ZipCodeConnString" value=
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
              D:\Example\Path\To\Database\zipbase.mdb" />
  5. Add code to use the ZIP Code Utility library.

Here is the sample code from the example application:

(Note that in order to run the sample application, you'll first need to download the database and modify the config file to point to the database on your hard disk.)

C#
//    Location by ZIP Code.
Location location = ZipCodeUtil.LookupByZipCode ("93275");
if (null != location)
    Console.WriteLine (location.ToString ());

//    Location(s) by City/State.
Location[] locs = ZipCodeUtil.LookupByCityState ("Tulare", "CA");
if (null != locs && locs.Length > 0)
{
    foreach (Location loc in locs)
    {
        Console.WriteLine (loc.ToString ());
    }
}

//    Location by City/State/Zip
location = ZipCodeUtil.LookupByCityStateZip ("Tulare", "CA", "93275");
if (null != location)
    Console.WriteLine (location.ToString ());

//    Distance between two locations.
Location sf = ZipCodeUtil.LookupByZipCode ("94175");
Location la = ZipCodeUtil.LookupByZipCode ("90185");
Double dDistance = sf.DistanceFrom (la);
Console.WriteLine ("{0} is {1} miles from {2}", sf.City, dDistance, la.City);

//    Other Locations within an X-mile radius of a specific location.
locs = sf.LocationsWithinRadius (5.0);
if (null != locs && locs.Length > 0)
{
    foreach (Location loc in locs)
    {
        Console.WriteLine (loc.ToString ());
    }
}

Limitations

This library relies on data from a free database that doesn't look like it has been updated since September 2001. I cannot vouch for the accuracy of this data. If you plan on using this in a production environment, you may want to invest in a commercial ZIP Codes database that is guaranteed by its maker and that is updated regularly.

To do List

  • Pending approval from the creator of the database, provide MS SQL and MySQL versions.

History

  • 2nd Jan 2005 - Version 1.0.0
    • Initial release.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Sagara Software, Inc.
United States United States
Jon is a senior software developer who loves using .NET to solve problems.

When he's not fooling around with computers or reading, he's busy spending time with his super wife, Kelly, and his three boys. He also likes to take his mountain bike for a spin.

Visit my blog

Comments and Discussions

 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey12-May-13 23:32
professionalManoj Kumar Choubey12-May-13 23:32 
Questionzip code radius Pin
Peter Hammer7-May-13 0:56
Peter Hammer7-May-13 0:56 
AnswerRe: zip code radius Pin
Member 223384428-Feb-14 4:38
Member 223384428-Feb-14 4:38 
GeneralZip Code Radius Search Solution Pin
codezilla9412-Nov-07 14:13
codezilla9412-Nov-07 14:13 
GeneralRe: Zip Code Radius Search Solution Pin
Jon Sagara12-Nov-07 15:31
Jon Sagara12-Nov-07 15:31 
QuestionMissing Database Pin
ellios5-Dec-06 1:55
ellios5-Dec-06 1:55 
AnswerRe: Missing Database [modified] Pin
Jon Sagara5-Dec-06 4:11
Jon Sagara5-Dec-06 4:11 
SuggestionRe: Missing Database Pin
marsh wiggle5-Dec-12 10:43
marsh wiggle5-Dec-12 10:43 
GeneralGUI, GUI, GUI Pin
Bassam Abdul-Baki9-May-06 3:34
professionalBassam Abdul-Baki9-May-06 3:34 
QuestionStuffing data in a DLL? Pin
RK KL15-Feb-06 6:32
RK KL15-Feb-06 6:32 
AnswerRe: Stuffing data in a DLL? Pin
Jon Sagara15-Feb-06 6:51
Jon Sagara15-Feb-06 6:51 
AnswerRe: Stuffing data in a DLL? Pin
Spiff Dog7-Dec-11 7:39
Spiff Dog7-Dec-11 7:39 
GeneralMissing dlls Pin
mtone1-Feb-06 11:13
mtone1-Feb-06 11:13 
GeneralRe: Missing dlls Pin
Jon Sagara1-Feb-06 11:17
Jon Sagara1-Feb-06 11:17 
They're in ZipCodeUtil_src.zip[^], under the "DataProvider" directory.

Edit: Regarding the binaries, SagaraSoftware.ZipCodeUtil.dll contains both AccessProvider and IDataProvider.

Jon Sagara
Look at him. He runs like a Welshman. Doesn't he run like a Welshman? Doesn't he? I think he runs like a Welshman.
Sagara.org | Blog | My Articles

-- modified at 17:20 Wednesday 1st February, 2006
GeneralRe: Missing dlls Pin
mtone1-Feb-06 11:26
mtone1-Feb-06 11:26 
GeneralFiltering &quot;Outliers&quot; Pin
eanderson12-Jan-05 10:04
eanderson12-Jan-05 10:04 
GeneralI knew I shoulda written! Pin
eanderson12-Jan-05 8:46
eanderson12-Jan-05 8:46 
GeneralRe: I knew I shoulda written! Pin
David Crow12-Jan-05 9:55
David Crow12-Jan-05 9:55 
GeneralRe: I knew I shoulda written! Pin
Jon Sagara12-Jan-05 9:57
Jon Sagara12-Jan-05 9:57 
GeneralRe: I knew I shoulda written! Pin
eanderson12-Jan-05 10:00
eanderson12-Jan-05 10:00 
GeneralRe: I knew I shoulda written! Pin
ethanselzer10-Feb-05 14:45
ethanselzer10-Feb-05 14:45 
GeneralRe: I knew I shoulda written! Pin
renzea1-May-06 11:59
renzea1-May-06 11:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.