thecfguy

A Unique Developer

Finding Zip code list within specified miles

 

I was just working around to get list zip code within 25 miles of radius for given address. I have database table for Zip Codes with below columns.

  • "zip" - Store zip code
  • "zip_latitude" - Store latitude
  • "zip_longitude" - Store longitude

 

I have created MS SQL function which returns list of all zip codes within specified radius in which you might be interested.
SQL Function:

CREATE FUNCTION [dbo].[fun_getZipCodesByMiles](@zipCode varchar(20),  @miles DECIMAL(18,5))
RETURNS @tblZip TABLE (zip varchar(20))
AS
BEGIN
    Declare @latitude DECIMAL(18,5), @longitude DECIMAL(18,5),@iLatVary DECIMAL(18,5)
    Declare @iLongVary DECIMAL(18,5)
    Declare @PI DECIMAL(18,15)SET @PI = 3.1415926535897932384626433832795 
    Select @latitude=ZIP_Latitude,@longitude = ZIP_LONGITUDE FROM zipCode where zip = @zipCode 
    SET    @iLatVary = @miles/((6076/5280) * 60)
    SET    @iLongVary = @miles/((((Cos(@latitude * @PI/180)) * 6076)/5280) * 60)
    INSERT INTO @tblZip Select zipCode.zip From ZipCode 
    Where ZIP_LONGITUDE Between (@longitude - @iLongVary) And (@longitude + @iLongVary)
    And ZIP_LATITUDE Between (@latitude - @iLatVary) And (@latitude + @iLatVary)
    RETURN 
END

How to Use:

This function will act as table and you directly inner join with any of database table to filter the records.

select * from tblStore INNER JOIN dbo.fun_getZipCodesByMiles ('07002',25) as T1 ON tblStore.zipcode = T1.zip