Getting All ZIP Codes In A Given Radius From A Known Point / ZIP Code Via ASP.NET

Some time ago I wrote a PHP-MySQL based solution to getting all ZIP Codes in a given radius from a known point / ZIP Code. I’ve long intended to do an ASP.NET version of that post, and here it is.

I won’t bother revisiting the mechanics in detail. I do urge you to read the post on the PHP version of this solution, at least to familiarize yourself with the mechanics of what I am doing and the compromises I’ve taken in coming up with this solution.

I will note the following for the “get to the point” types:

  • The first thing we need is to procure a geocoded database table of ZIP Codes. There are several out there; the one I am using is the ZIP Code Database project, available at Sourceforge. You’ll need to figure out how to get their CSV file into your SQL Server database; BULK INSERT is an option, or you can script it.
  • The basic method I am going to use is to create a square. Specifically, I am going to:
    • ask the end user for a starting ZIP Code, and a radius from that point from which he would like other ZIP Codes to come;
    • create a square by selecting points North, South, East and West at the given distance from the starting point; then
    • query the database for all points that fall within that square (or, in other words, all points with latitudes less than North, greater than South, less than East and greater than West).
  • I’m going to put my results in a GridView. However, you could easily just use a DataReader or DataTable to get the relevant records and do with them as you like.
  • The formulas I am using to compute longitude and latitude coordinates come from moveabletype.co.uk.

Step 1: Helper Functions

Before we get into the nitty-gritty, we need to create two helper functions: One that converts degrees into radians, and another that reverses the process. (In trigonometry, angles are calculated in radians; a radian is the ratio between the length of an arc and its radius. If an arc is as long as its radius, that’s 1c).

First, to convert from degrees to radians:

Function Deg2Rad(ByVal sglDegrees As Single) As Single
	Return sglDegrees * (Math.PI / 180.0)
End Function

And to go from radians to degrees:

Function Rad2Deg(ByVal sglRadians As Single) As Single
	Return sglRadians * (180.0 / Math.PI)
End Function

Step 2: Functions To Plot Latitude And Longitude

Now that we can move back and forth between degrees and radians, we can calculate the latitude and longitude of points at a known distance and bearing from an initial geocoordinate.

Given a known starting point, expressed as lat1 and lon1; a known distance from that point, d; a known bearing from that point, b; and a known radius of the sphere over which we are travelling, r; we calculate a new geocoordinate, expressed as lat2 and lon2, thus:

lat2 = asin(sin(lat1) * cos(d/r) + cos(lat1) * sin(d/r) * cos(b))
lon2 = lon1 + atan2(sin(b) * sin(d/r) * cos(lat1), cos(d/r) - sin(lat1)*sin(lat2))
Note that we have to convert our latitude, longitude and bearing variables into radians when we make these calculations; and we need to convert the results from radians into degrees.

So here’s a function that accepts, as arguments, a starting latitude, radius, bearing and distance; and returns the latitude at the provided bearing and distance from that starting latitude.

Function CalculateLatitudeCoordinate(ByVal sglLat1 As Single, ByVal intRadius As Integer, ByVal intBearing As Integer, ByVal intDistance As Integer) As Single
	Return Math.Asin(Math.Sin(sglLat1) * Math.Cos(intDistance / intRadius) + Math.Cos(sglLat1) * Math.Sin(intDistance / intRadius) * Math.Cos(intBearing))
End Function

And here’s a function that accepts, as arguments, a starting latitude and longitude, and ending latitude, radius, bearing and distance; and returns a longitude at the provided bearing and distance from the starting coordinates.

Function CalculateLongitudeCoordinate(ByVal sglLon1 As Single, ByVal sglLat1 As Single, ByVal sglLat2 As Single, intRadius As Integer, ByVal intBearing As Integer, ByVal intDistance As Integer) As Single
	Return sglLon1 + Math.Atan2(Math.Sin(intBearing) * Math.Sin(intDistance / intRadius) * Math.Cos(sglLat1), Math.Cos(intDistance / intRadius) - Math.Sin(sglLat1) * Math.Sin(sglLat2))
End Function

Step 3: Create The Form

To implement, we need to create a form with a few elements:

<p>
	Select all ZIP Codes within 

	<asp:DropDownList runat="server" ID="ddlDistance">
		<asp:ListItem Selected="True">5</asp:ListItem>
		<asp:ListItem>10</asp:ListItem>
		<asp:ListItem>25</asp:ListItem>
		<asp:ListItem>50</asp:ListItem>
		<asp:ListItem>100</asp:ListItem>
	</asp:DropDownList>

	miles of ZIP Code

	<asp:TextBox runat="server" ID="tbZip" Columns="5" />
	<asp:RequiredFieldValidator 
		runat="server" 
		ID="rfvZip" 
		ControlToValidate="tbZip" 
		ErrorMessage="Please provide a ZIP Code" 
		CssClass="warning" 
		Display="Dynamic" 
	/>
	<asp:RegularExpressionValidator 
		runat="server" 
		ID="revZip" 
		ControlToValidate="tbZip" 
		ValidationExpression="^[0-9]{5}$" 
		ErrorMessage="Please enter a valid five-digit ZIP Code" 
		CssClass="warning" 
		Display="Dynamic" 
	/>

	<asp:Button runat="server" ID="btnZip" Text="Get ZIP Codes" />
</p>

<p><asp:Label runat="server" ID="lblStatus" Text="Status messages will appear here" /></p>

<asp:GridView 
	runat="server" 
	ID="gvZIP"
	DataSourceID = "sqlZip" 
	AutoGenerateColumns="false" 
	AllowSorting="true"
	AllowPaging = "true"
	PageSize = "20"
	HeaderStyle-BackColor="Yellow" 
	HeaderStyle-Font-Bold="true" 
	HeaderStyle-HorizontalAlign="Center"
	AlternatingRowStyle-BackColor="WhiteSmoke" 
	CellPadding="5"
>
	<Columns>
		<asp:BoundField HeaderText="City" DataField="cityname" SortExpression="cityname" />
		<asp:BoundField HeaderText="State" DataField="statecode" SortExpression="statecode" />
		<asp:BoundField HeaderText="ZIP Code" DataField="zip_code" SortExpression="zip_code" />
		<asp:BoundField HeaderText="Latitude" DataField="latitude" SortExpression="latitude" />
		<asp:BoundField HeaderText="Longitude" DataField="longitude" SortExpression="longitude" />
		<asp:BoundField HeaderText="Distance" DataField="distance" SortExpression="distance" />
	</Columns>
</asp:GridView>

<asp:SqlDataSource 
	runat="server" 
	ID="sqlZip" 
	SelectCommand="sp_get_zips_in_radius" 
	SelectCommandType="StoredProcedure" 
	ConnectionString="YOUR CONNECTION STRING"
>
	<SelectParameters>
		<asp:Parameter Name="maxlat" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="minlat" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="maxlon" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="minlon" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="startlat" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="startlon" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="radius" DbType="Int16" DefaultValue="3959" />
	</SelectParameters>
</asp:SqlDataSource>
Note that my SqlDataSource has SelectParameters; I’ll use code behind to update those parameters with the coordinates I’ll figure out, also via code behind, and bind the GridView.

Although I am going to bind my data via codebehind, and therefore could have used a SqlDataReader, DataTable or the like as my GridView’s data source, I am using a SqlDataSource because I want to be able to page and sort my results, and I am too lazy to write code behind to do all that; those features are native to a GridView bound to a SqlDataSource.

If I didn’t want to page and sort, I’d just use a SqlDataReader and bind the GridView to that.

Step 4: Get The Initial Point

We need to get, from the database, the initial geocoordinates for the user-supplied ZIP Code. We do that with a stored procedure:

CREATE PROCEDURE [dbo].[sp_get_zip_code]
	@zip_code CHAR(5)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	FROM zip_codes
	WHERE zipcode = @zip_code
END

This query should return to us the city name, state, latitude and longitude for the specified ZIP Code. As always, we want to catch any exceptions in making the query, and we want to make sure we get a record from the database (that is, we can find the starting ZIP Code in the database).

If we can’t get starting coordinates, we’ll report that.

Otherwise, we’ll output details about the starting point to our Label control, and invoke a (yet-to-be-written) subroutine to populate the GridView.

Sub GetInitialCoordinates() Handles btnZip.Click
	'This subroutine requires a Label control named lblStatus
	
	'Prepare to connect to db and execute stored procedure
	Dim objConn As New SqlConnection("YOUR CONNECTION STRING")
	Dim objCmd As New SqlCommand("sp_get_zip_code", objConn)
	objCmd.CommandType = CommandType.StoredProcedure

	'we need to supply the ZIP code as an input parameter to our stored procedure
	objCmd.Parameters.Add(New SqlParameter("zip_code", SqlDbType.Char, 5))
	objCmd.Parameters("zip_code").Value = tbZip.Text

	'sglMinLat = south, sglMaxLat = north, sglMinLon = west, sglMaxLon = east
	Dim sglMinLat As Single
	Dim sglMaxLat As Single
	Dim sglMinLon As Single
	Dim sglMaxLon As Single

	Try
		'open connection
		objConn.Open()
		'put results into datareader
		Dim objReader As SqlDataReader
		objReader = objCmd.ExecuteReader()
		If objReader.HasRows Then
			'if starting point found, calculate box points
			objReader.Read()
			sglMinLat = Rad2Deg(CalculateLatitudeCoordinate(Deg2Rad(objReader("latitude")), 3959, Deg2Rad(180), ddlDistance.SelectedValue))
			sglMaxLat = Rad2Deg(CalculateLatitudeCoordinate(Deg2Rad(objReader("latitude")), 3959, Deg2Rad(0), ddlDistance.SelectedValue))
			sglMinLon = Rad2Deg(CalculateLongitudeCoordinate(Deg2Rad(objReader("longitude")), Deg2Rad(objReader("latitude")), Deg2Rad(sglMinLat), 3959, Deg2Rad(270), ddlDistance.SelectedValue))
			sglMaxLon = Rad2Deg(CalculateLongitudeCoordinate(Deg2Rad(objReader("longitude")), Deg2Rad(objReader("latitude")), Deg2Rad(sglMinLat), 3959, Deg2Rad(90), ddlDistance.SelectedValue))
			
			'report starting point details to lblStatus
			Dim strOut As String
			strOut = "ZIP Code " & tbZip.Text & " is assigned to " & objReader("cityname") & ", " & objReader("statecode") & ".<br />"
			strOut &= "It is located at latitude " & objReader("latitude") & ", longitude " & objReader("longitude") & ".<br /><br />"
			strOut &= "At a distance of " & ddlDistance.SelectedValue & " miles, the search box coordinates are:<br />"
			strOut &= "Maximum latitude (North): " & sglMaxLat & "<br />"
			strOut &= "Miniumum latitude (South): " & sglMinLat & "<br />"
			strOut &= "Maximum longitude (East): " & sglMaxLon & "<br />"
			strOut &= "Minimum longitude (West): " & sglMinLon & "<br />"
			lblStatus.Text = strOut

			'populate gridview
			PopulateGridView(sglMinLat, sglMaxLat, sglMinLon, sglMaxLon, objReader("latitude"), objReader("longitude"))
		Else
			'starting point not found
			lblStatus.Text = "Error retrieving initial ZIP Code coordinates: No record found for " & tbZip.Text & "."
		End If
		objConn.Close()
		objCmd.Dispose()
		objConn.Dispose()
	Catch ex As Exception
		'technical problem running the query
		lblStatus.Text = "Error executing database query for initial coordinates: " & ex.Message
	End Try
End Sub

Step 5: Bind The GridView

Lastly, we need to create a stored procedure that will accept the coordinates for our search box, and return all coordinates that fall within that box.

Notice that in addition to the North, South, East and West coordinates that form the search box, I am including the latitude and longitude of the ZIP Code that acts as our starting point, as well as the radius of the Earth.

That’s because I am going to calculate, on the fly, the distance from my starting point to the ZIP Codes returned by the query.

Given two known points, expressed as lat1, lon1, lat2 and lon2; and a known radius of the sphere on which they are located, expressed as r; the distance between those points is found via this formula:

distance = acos(sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1)) * r

Again, we have to convert our latitude and longitude coordinates from degrees to radians; but note that we do not need to convert the result of the expression from radians to degrees. I will, however, round that result down to a precision of 2.

CREATE PROCEDURE [dbo].[sp_get_zips_in_radius]

@maxlat DECIMAL(9, 6),
@minlat DECIMAL(9, 6),
@maxlon DECIMAL(9, 6),
@minlon DECIMAL(9, 6),
@startlat DECIMAL(9, 6),
@startlon DECIMAL(9, 6),
@radius INT

AS
SELECT zipcode, statecode, latitude, longitude, cityname, 
ROUND(ACOS(SIN(RADIANS(@startlat)) * SIN(RADIANS(latitude)) + COS(RADIANS(@startlat)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(@startlon))) * @radius, 2) AS distance
FROM zip_codes
WHERE latitude < @maxlat AND latitude > @minlat AND longitude < @maxlon AND longitude > @minlon 
ORDER BY distance, zipcode, statecode, cityname

Now that we have the stored procedure ready to go, we just need to update the values of our SqlDataSource’s SelectParameters, then bind the GridView:

Sub PopulateGridView(ByVal sglMinLat As Single, ByVal sglMaxLat As Single, ByVal sglMinLon As Single, ByVal sglMaxLon As Single, ByVal sglStartLat As Single, ByVal sglStartLon As Single)
	sqlZip.SelectParameters("minlat").DefaultValue = sglMinLat
	sqlZip.SelectParameters("maxlat").DefaultValue = sglMaxLat
	sqlZip.SelectParameters("minlon").DefaultValue = sglMinLon
	sqlZip.SelectParameters("maxlon").DefaultValue = sglMaxLon
	sqlZip.SelectParameters("startlat").DefaultValue = sglStartLat
	sqlZip.SelectParameters("startlon").DefaultValue = sglStartLon

	gvZIP.DataBind()
End Sub

And that’s all there is to it. You can see a working demo here: http://dougv.net/demos/zip_code_distance

Code on github: https://github.com/dougvdotcom/aspnet_zip_code_distance.

I distribute code under the GNU GPL. See Copyright & Attribution for details.

All links in this post on delicious: http://delicious.com/dougvdotcom/getting-all-zip-codes-in-a-given-radius-from-a-known-point-zip-code-via-asp-net

10 Comments

  1. Hi Doug,

    Thank you very much for this very helpful article.
    If the distance needs to be in kilometers instead of miles, what changes would be necessary?
    Thanks again.

  2. Mark: To use this solution out of the box, multiply miles by 1.61 to get a good-enough conversion to kilometers: e.g., 50 miles ~ 80.5 kilometers. To go from kilometers to miles, multiply by .62: e.g., 25 kilometers ~ 15.5 miles.

    Alternatively, these same formulas will work for kilometers, provided you convert the radius of Earth to kilometers (6,371) and enter all distances in kilometers, as well.

  3. Great article, and just what I was looking for … thanks very much! I added the search-radius as a parameter to the SqlDataSource and edited the “sp_get_zips_in_radius” procedure to limit the result to that distance. I think that takes care of the extra zips that fall inside the square but outside the circle. I’m fairly new at this, so correct me if I’m wrong!

  4. Old post but i converted this to c# and for some reason my min/max lats are the same and min/max long are the same. can’t see the issue.

  5. Is distance supposed to be in miles? If I look from town to town with in 15 miles, distance records as 0.3, when its supposed to be about 5 miles.
    Any ideas why this should be so far off?

Leave a Reply

  • Check out the Commenting Guidelines before commenting, please!
  • Want to share code? Please put it into a GitHub Gist, CodePen or pastebin and link to that in your comment.
  • Just have a line or two of markup? Wrap them in an appropriate SyntaxHighlighter Evolved shortcode for your programming language, please!