Zip code radius search

[Scott Schluer]

I need to perform a search for all zip codes within "x" miles of a given zip
code using Microsoft SQL Server 2000. Ideally, this would be a User Defined
Function that would accept two parameters: an origin zip code and a radius
(as an integer value). I have a table called ZipCodes that, among other
things, contains all USA zip codes along with the corresponding latitiude
and longitude values.

How would I write a function, or where would I find an algorithm that would
return all of the zipcodes within an "x" mile radius? The UDF would be
called from a stored procedure (i.e. SELECT * FROM Properties WHERE
property_zip IN (...list of zip codes returned by UDF here...).

Any ideas?

Thanks!

Scott

[Steve Kass]

Scott,

Here is an example showing the formula for the distance between two
points, given latitude and longitude:

declare @lat1 as float
declare @lon1 as float
declare @lat2 as float
declare @lon2 as float

set @lat1 = radians(26.7174)
set @lon1 = radians(-80.0695)
set @lat2 = radians(26.5956)
set @lon2 = radians(-80.1302)

declare @Distance as Float
set @Distance =
3963 * acos(sin(@lat1) * sin(@lat2)
+ cos(@lat1) * cos(@lat2) * cos(@lon2 - @lon1))
select @EquationResult as [Miles Apart]


So given (@lat1,@lon2) from one zip code, you will want something like

select ZipCode from yourTable
where 3963 * acos(sin(@lat1) * sin(ZipLat)
+ cos(@lat1) * cos(ZipLat) * cos(ZipLon - @lon1)) <= @miles

Steve Kass
Drew University

Post by Scott Schluer
I need to perform a search for all zip codes within "x" miles of a given zip
code using Microsoft SQL Server 2000. Ideally, this would be a User Defined
Function that would accept two parameters: an origin zip code and a radius
(as an integer value). I have a table called ZipCodes that, among other
things, contains all USA zip codes along with the corresponding latitiude
and longitude values.
How would I write a function, or where would I find an algorithm that would
return all of the zipcodes within an "x" mile radius? The UDF would be
called from a stored procedure (i.e. SELECT * FROM Properties WHERE
property_zip IN (...list of zip codes returned by UDF here...).
Any ideas?
Thanks!
Scott

[Mike Labosh]

Wow, that's pretty badass! When I read the OP, I was thinking "yeah,
right!"

[Aaron [SQL Server MVP]]

http://www.aspfaq.com/2527
--
http://www.aspfaq.com/
(Reverse address to reply.)

Post by Scott Schluer
I need to perform a search for all zip codes within "x" miles of a given zip
code using Microsoft SQL Server 2000. Ideally, this would be a User Defined
Function that would accept two parameters: an origin zip code and a radius
(as an integer value). I have a table called ZipCodes that, among other
things, contains all USA zip codes along with the corresponding latitiude
and longitude values.
How would I write a function, or where would I find an algorithm that would
return all of the zipcodes within an "x" mile radius? The UDF would be
called from a stored procedure (i.e. SELECT * FROM Properties WHERE
property_zip IN (...list of zip codes returned by UDF here...).
Any ideas?
Thanks!
Scott