facebook  linkedin  Twitter  skype  Rss googlePlus

TSQL Code Snip: Searching for Locations within a Radius of a Zipcode

Aug 17 2005
6524

In a previous article we provided a code snippet in ASP on how to conduct a search based on two criteria from the user, a postal code, and a miles for searching within a radius. In this article we provide the same functionality, but all within TSQL.

This solution provides some benefits over the previous version, instead of running the calculation in the ASP code, and sending a request for a long list of matching zipcodes, we do the entire calculation in the stored procedure. This reduces the amount of requests and traffic needed to sent over the network from our ASP application.

ALTER PROCEDURE dbo.GetLocations
(
@ZipCode char(11),
@Miles int
)
AS
/* SET NOCOUNT ON */
Declare @HighLatitude float
Declare @LowLatitude float
Declare @HighLongitude float
Declare @LowLongitude float
Declare @StartLatitude float
Declare @StartLongitude float
Declare @LatitudeRange float
Declare @LongitudeRange float
SELECT @StartLatitude = Latitude, @StartLongitude = Longitude FROM POSTALCODES WHERE POSTALCODE = @Zipcode
Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)
Set @LatitudeRange = @Miles / 69.045454545454545454545454545455
Set @LowLatitude = @StartLatitude - @LatitudeRange
Set @HighLatitude = @StartLatitude + @LatitudeRange
Set @LowLongitude = @StartLongitude - @LongitudeRange
Set @HighLongitude = @StartLongitude + @LongitudeRange
SELECT * FROM CompanyLocations WHERE PostalCode IN(SELECT PostalCode FROM PostalCodes WHERE (Latitude <= @HighLatitude AND Latitude >= @LowLatitude)  AND (Longitude <= @HighLongitude AND Longitude >= @LowLongitude))
RETURN

Granted this is more of grid search and may return some records that are outside of the parameters by a few miles. However, performance is high, rather than doing a record by record calculation for accuracy. In applications where a high amount of accuracy is needed, we do not recommend this code.

Zipcodes for both US and Canada are available on the Web that will provide you with latitude and longitude values that correspond to the postal codes for under $100. You can search on the Web for these vendors.

About the Author, Patrick Santry

Patrick Santry, has two decades of experience in enabling businesses to take advantage of the digital landscape. A well rounded experience in technology, and business is what sets me apart from the rest of the pack. When it comes to an overall digital strategy my experience is impressive.

BS in Computer Information Systems. Four time recipient of the Microsoft MVP Award, and author of several books and magazine articles on digital technologies.


blog comments powered by Disqus

 

Thank you for visiting our site, before you leave, please visit some of the areas or information you may have missed.

Popular Articles