Click here to Skip to main content
Click here to Skip to main content
Articles » Languages » C# » Utilities » Downloads
 
Add your own
alternative version

ZIP Code Utility

, 2 Jan 2005 CPOL
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.
#region FILE HEADER
/// <project>ZipCodeUtil</project>
/// <assembly>SagaraSoftware.ZipCodeUtil.dll</assembly>
/// <filename>AccessProvider.cs</filename>
/// <creator>Jon Sagara</creator>
/// <description>
/// Contains the AccessProvider class, which is used to read data from MS Access. 
/// </description>
/// <copyright>
/// Copyright (c) 2004 Sagara Software.  All rights reserved.
/// </copyright>
/// <disclaimer>
/// This file is provided "as is" with no expressed or implied warranty.  The author accepts no 
///  liability for any damage/loss of business that this product may cause.
/// </disclaimer>
/// <history>
///	<change date="12/29/2004" changedby="Jon Sagara">File created.</changed>
/// </history>
#endregion

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace SagaraSoftware.ZipCodeUtil
{
	/// <summary>
	/// AccessProvider implements the IDataProvider interface, interacting with an MS Access 
	/// database.
	/// </summary>
	public class AccessProvider : IDataProvider
	{
		#region CONSTRUCTORS
		/// <summary>
		/// Default constructor.  Does nothing.
		/// </summary>
		public AccessProvider ()
		{
		}
		#endregion


		#region IDataProvider Members


		/// <summary>
		/// Look up a <see cref="SagaraSoftware.ZipCodeUtil.Location" /> by ZIP Code.  If Latitude
		///  or Longitude are NULL, they are set to Double.MinValue.
		/// </summary>
		/// <param name="inZipCode">ZIP Code to lookup.</param>
		/// <returns><see cref="SagaraSoftware.ZipCodeUtil.Location" /> of the ZIP Code.</returns>
		public Location DoLookupByZipCode (string inZipCode)
		{
			OleDbConnection oleConn = null;
			OleDbCommand oleCmd = null;
			OleDbDataReader oleReader = null;
			Location loc = null;
			string strConnString = ConfigurationSettings.AppSettings["ZipCodeConnString"];
			StringBuilder sql = new StringBuilder ();

			if (null == strConnString || string.Empty == strConnString)
				throw new ApplicationException ("You must provide a connection string for your MS Access database.");

			sql.Append ("SELECT * FROM ZIP_CODES WHERE ZIP = ?");

			oleConn = new OleDbConnection (strConnString);
			oleCmd = new OleDbCommand (sql.ToString (), oleConn);
			oleCmd.Parameters.Add (new OleDbParameter ("ZIP", inZipCode));
			oleConn.Open ();
			
			try
			{
				oleReader = oleCmd.ExecuteReader ();

				if (oleReader.Read ())
				{
					loc = new Location ();

					loc.City = Convert.ToString (oleReader["CITY"]);
					loc.State = Convert.ToString (oleReader["STATE"]);
					loc.ZipCode = inZipCode;
					loc.County = Convert.ToString (oleReader["COUNTY"]);
					loc.Latitude = (DBNull.Value == oleReader["LATITUDE"]) ? Double.MinValue : Double.Parse (Convert.ToString (oleReader["LATITUDE"]));
					loc.Longitude = (DBNull.Value == oleReader["LONGITUDE"]) ? Double.MinValue : Double.Parse (Convert.ToString (oleReader["LONGITUDE"]));
					loc.ZipClass = Convert.ToString (oleReader["ZIP_CLASS"]);
				}
			}
			catch (Exception e)
			{
				throw new ApplicationException ("Error getting data from database", e);
			}
			finally
			{
				if (null != oleReader)
					oleReader.Close ();
				if (null != oleConn)
					oleConn.Close ();
			}

			return loc;
		}


		/// <summary>
		/// Lookup one or more <see cref="SagaraSoftware.ZipCodeUtil.Location" />s by City/State.
		///  In the database, some cities are represented by more than one ZIP Code.
		/// </summary>
		/// <param name="inCity">Name of the City.</param>
		/// <param name="inState">Name of the State.</param>
		/// <returns>An array of <see cref="SagaraSoftware.ZipCodeUtil.Location" /> objects whose City/State matches the input City/State.</returns>
		public Location[] DoLookupByCityState (string inCity, string inState)
		{
			OleDbConnection oleConn = null;
			OleDbCommand oleCmd = null;
			OleDbDataReader oleReader = null;
			ArrayList locs = new ArrayList ();
			string strConnString = ConfigurationSettings.AppSettings["ZipCodeConnString"];
			StringBuilder sql = new StringBuilder ();

			if (null == strConnString || string.Empty == strConnString)
				throw new ApplicationException ("You must provide a connection string for your MS Access database.");

			sql.Append ("SELECT * FROM ZIP_CODES WHERE CITY = ? AND STATE = ? ORDER BY ZIP");

			oleConn = new OleDbConnection (strConnString);
			oleCmd = new OleDbCommand (sql.ToString (), oleConn);
			oleCmd.Parameters.Add (new OleDbParameter ("CITY", inCity));
			oleCmd.Parameters.Add (new OleDbParameter ("STATE", inState));
			oleConn.Open ();
			
			try
			{
				oleReader = oleCmd.ExecuteReader ();
				string jon = oleCmd.CommandText;

				while (oleReader.Read ())
				{
					Location loc = new Location ();

					loc.City = Convert.ToString (oleReader["CITY"]);
					loc.State = Convert.ToString (oleReader["STATE"]);
					loc.ZipCode = Convert.ToString (oleReader["ZIP"]);
					loc.County = Convert.ToString (oleReader["COUNTY"]);
					loc.Latitude = Double.Parse (Convert.ToString (oleReader["LATITUDE"]));
					loc.Longitude = Double.Parse (Convert.ToString (oleReader["LONGITUDE"]));
					loc.ZipClass = Convert.ToString (oleReader["ZIP_CLASS"]);

					locs.Add (loc);
				}
			}
			catch (Exception e)
			{
				throw new ApplicationException ("Error getting data from database", e);
			}
			finally
			{
				if (null != oleReader)
					oleReader.Close ();
				if (null != oleConn)
					oleConn.Close ();
			}

			return (Location[]) locs.ToArray (typeof (Location));
		}


		/// <summary>
		/// Finds all <see cref="SagaraSoftware.ZipCodeUtil.LocationInRadius" />es within X miles
		///  of inRefLoc.
		/// </summary>
		/// <remarks>
		/// To speed the calculation, this method finds all areas within a square area of dimension
		///  (2*Radius)x(2*Radius).  Any city with a Lat/Lon pair that falls within this square is
		///  returned.  However, only those cities whose distance is less than or equal to Radius
		///  miles from inRefLoc are returned.  This has the unfortunate side effect of selecting
		///  from ~22% more area than is necessary.
		/// </remarks>
		/// <param name="inRefLoc">The central location from which we are trying to find other locations within the specified radius.</param>
		/// <param name="inBounds">A class containing the "box" that encloses inRefLoc.  Used to approximate a circle of Radius R centered around the point inRefLoc.</param>
		/// <returns>0 or more <see cref="SagaraSoftware.ZipCodeUtil.LocationInRadius" />es that are
		///  within Radius miles of inRefLoc.</returns>
		public LocationInRadius[] GetLocationsWithinRadius (Location inRefLoc, RadiusBox inBounds)
		{
			OleDbConnection oleConn = null;
			OleDbCommand oleCmd = null;
			OleDbDataReader oleReader = null;
			ArrayList locs = new ArrayList ();
			string strConnString = ConfigurationSettings.AppSettings["ZipCodeConnString"];
			StringBuilder sql = new StringBuilder ();

			if (null == strConnString || string.Empty == strConnString)
				throw new ApplicationException ("You must provide a connection string for your MS Access database.");

			sql.Append ("SELECT * FROM ZIP_CODES WHERE ");
			sql.Append ("IIf(ISNULL(LATITUDE),999.0,CDbl(LATITUDE)) >= ? AND ");
			sql.Append ("IIf(ISNULL(LATITUDE),999.0,CDbl(LATITUDE)) <= ? AND ");
			sql.Append ("IIf(ISNULL(LONGITUDE),999.0,CDbl(LONGITUDE)) >= ? AND ");
			sql.Append ("IIf(ISNULL(LONGITUDE),999.0,CDbl(LONGITUDE)) <= ? ");
			sql.Append ("ORDER BY CITY, STATE, ZIP");

			oleConn = new OleDbConnection (strConnString);
			oleCmd = new OleDbCommand (sql.ToString (), oleConn);
			oleCmd.Parameters.Add (new OleDbParameter ("SouthLat", inBounds.BottomLine));
			oleCmd.Parameters.Add (new OleDbParameter ("NorthLat", inBounds.TopLine));
			oleCmd.Parameters.Add (new OleDbParameter ("WestLong", inBounds.LeftLine));
			oleCmd.Parameters.Add (new OleDbParameter ("EastLong", inBounds.RightLine));
			oleConn.Open ();
			
			try
			{
				oleReader = oleCmd.ExecuteReader ();

				while (oleReader.Read ())
				{
					LocationInRadius loc = new LocationInRadius ();

					loc.City = Convert.ToString (oleReader["CITY"]);
					loc.State = Convert.ToString (oleReader["STATE"]);
					loc.ZipCode = Convert.ToString (oleReader["ZIP"]);
					loc.County = Convert.ToString (oleReader["COUNTY"]);
					loc.Latitude = Double.Parse (Convert.ToString (oleReader["LATITUDE"]));
					loc.Longitude = Double.Parse (Convert.ToString (oleReader["LONGITUDE"]));
					loc.ZipClass = Convert.ToString (oleReader["ZIP_CLASS"]);
					loc.DistanceToCenter = Distance.GetDistance (inRefLoc, loc);

					if (loc.DistanceToCenter <= inBounds.Radius)
						locs.Add (loc);
				}
			}
			catch (Exception e)
			{
				throw new ApplicationException ("Error getting data from database", e);
			}
			finally
			{
				if (null != oleReader)
					oleReader.Close ();
				if (null != oleConn)
					oleConn.Close ();
				locs.Sort (new LocationInRadiusComparer ());
			}

			return (LocationInRadius[]) locs.ToArray (typeof (LocationInRadius));
		}

		#endregion


		/// <summary>
		/// Allows for sorting of an ArrayList of <see cref="SagaraSoftware.ZipCodeUtil.LocationInRadius" /> 
		///  objects by DistanceToCenter, ascending.
		/// </summary>
		private class LocationInRadiusComparer : IComparer
		{
			public int Compare (object x, object y)
			{
				LocationInRadius lx = (LocationInRadius) x;
				LocationInRadius ly = (LocationInRadius) y;

				if (lx.DistanceToCenter < ly.DistanceToCenter)
					return -1;
				else if (lx.DistanceToCenter > ly.DistanceToCenter)
					return 1;
				else
					return 0;
			}
		}
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

Share

About the Author

Jon Sagara
Software Developer (Senior) Sagara Software, Inc.
United States United States
Jon graduated from Cal Poly with a B.S. Computer Engineering. He is currently building a Silverlight-based report scheduling interface for a pharmaceutical reporting company.
 
When he's not fooling around with computers or reading, he's busy spending time with his super wife, Kelly, his two boys, and their rambunctious dog, Homer.
 
Visit my blog
Follow on   Twitter

| Advertise | Privacy | Mobile
Web01 | 2.8.141029.1 | Last Updated 2 Jan 2005
Article Copyright 2005 by Jon Sagara
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid