Zip Code Radius Search

,

Enter the starting zip code and the number of miles for a radius search and the SP will return all the zip codes within the number of miles specified.

The data file can be found at http://www.census.gov/tiger/tms/gazetteer/zips.txt

The record layout van be found at http://www.census.gov/tiger/tms/gazetteer/zip90r.txt

/*
Returns zip codes within specified range.
*/
CREATE Procedure sp_ZipCode_Range
	(
		@ZipCode Numeric(5, 0) = Null,
		@Miles Float
	)
As
	set nocount on
	
	Declare @Latitude Float(10)
	Declare @Longitude Float(10)

	-- Lookup longitude, latitude for zip codes
	Select @Latitude = Latitude, @Longitude = Longitude From State Where Zip = @ZipCode
	
	Select
		Zip, Zip_Name,
--		 Zip_Name,
		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, zip_name
	Order by miles
	return
GO

Rate

Share

Share

Rate