Zip Code Radius Search

, 2003-07-24

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

Related content

What is the Maximum Page Size in SQL Server 2000?

I have always read that 8060 bytes is the maximum size. This is stated over and over again in Books Online, the MS site and numerous other sites, including this one. However a post in our forum recently questioned this. I decided to verify the problem and do a little research.

4.55 (11)

2006-05-19 (first published: )

36,462 reads

SQL Server 2000 User-Defined Functions White Paper

The User_Defined_Functions.exe file contains the User-Defined Functions white paper. The User-Defined functions white paper outlines the characteristics of the new user-defined function (UDF) feature that is introduced in Microsoft SQL Server 2000. The white paper also summarizes how you can create your own Transact-SQL functions to extend the programmability of Transact-SQL.

2001-08-31

2,233 reads

Work Around Errors in Database Web Apps

ne of the issues you face when building Web applications is handling the errors you encounter when interacting with a back-end database. I was recently working with someone to create a new Web site with SQL Server™, ActiveX® Data Objects (ADO), and ASP. Lots of little things came up that I thought were worth sharing with MIND readers, so I'll focus this column on what I learned from this experience and the solutions to many of the problems I faced.

2001-07-13

1,579 reads