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.

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.