SQL Code, Web

Find All Zip or Postal Codes Within a Certain Distance

This stored procedure will allow you to incorporate proximity searches into your web application. This is the perfect solution to retrieve businesses or otherwise within a certain distance of a specific zip/postal code, and calculate the distance in miles as well. This code takes into account the curvature of the earth in performing the distance calculation. It is assumed that you're storing the geocode for each address in a database.

Using a stored procedure is much faster than filtering and sorting through the results from the web application with PHP or .Net. With this SQL code, you can create a local search engine, like Google Local. If you need the C# code to look up the geocode (longitude/latitude), you can find the code here.

By default the code works like this:

  • You call the stored procedure, supplying the start location (zip/postal code), and the distance in miles to search within.
  • The longitude and latitude are looked up for that zip code from a table of states or geocodes.
  • The fancy calculations are performed with Sine, Cosine, and the curvature of the earth. I don't remember the math that makes this work, I just know that it works.
  • The appropriate records are returned, with the calculated miles.

For my ASP.Net web application, I needed to modify this slightly. I'm not hosting a huge database of all existing zip/postal codes, I perform lookups each time. So I modified the code to take the longitude and latitude as a parameter, as I've already retrieved them.


CREATE Procedure sp_ZipPostalCode_Range
 (
  @ZipCode Numeric(5, 0) = Null,
  @Miles Float
 )
As
 set nocount on
 
 Declare @Latitude Float(10)
 Declare @Longitude Float(10)

 Select @Latitude = Latitude, @Longitude = Longitude From State Where Zip = @ZipCode
 
 Select
  Zip
  Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *
  Sin(Latitude/57.2958) +
  Cos(@Latitude/57.2958) *
  Cos(Latitude/57.2958) *
  Cos(Longitude/57.2958 - @Longitude/57.2958))) As miles
 From
  State
 Where
  Longitude Is Not Null
  And Latitude Is Not Null
  And @Miles >=
   (
   3958.75 * ACos(Sin(@Latitude/57.2958) *
   Sin(Latitude/57.2958) +
   Cos(@Latitude/57.2958) *
   Cos(Latitude/57.2958) *
   Cos(Longitude/57.2958 - @Longitude/57.2958))
   )
  And Zip != @ZipCode
 Group by zip
 Order by miles
 return
Share

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image