# 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"
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"
AlternatingRowStyle-BackColor="WhiteSmoke"
>
<Columns>
<asp:BoundField HeaderText="ZIP Code" DataField="zip_code" SortExpression="zip_code" />
</Columns>
</asp:GridView>

<asp:SqlDataSource
runat="server"
ID="sqlZip"
SelectCommandType="StoredProcedure"
>
<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" />
</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("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()
'if starting point found, calculate box points

'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
Else
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),

AS
SELECT zipcode, statecode, latitude, longitude, cityname,
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.

Update, 26 June 2018: I no longer offer demos of retired .NET Frameworks, or ASP.NET WebForms, at dougv.net.

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

1. susith says:

Thank you!

2. Mark says:

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.

1. Doug Vanderweide says:

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. Lisa says:

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!

1. Doug Vanderweide says:

@Lisa: That should work fine. Thanks for your suggestion!

4. Jai says:

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.

1. Doug Vanderweide says:

@Jai: I’m not sure what you are saying because I can’t see your code. If you want to throw up a pastebin or gist, I can take a look.

5. Julie says:

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?

1. Doug Vanderweide says:

@Julie: If you use the code as written, then the distance is in miles.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

• 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!