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