Re: Zip code radius search

From: Steve Kass (skass_at_drew.edu)
Date: 11/16/04 

Date: Tue, 16 Nov 2004 12:14:53 -0500

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

Scott Schluer wrote:

>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
>
>
>
>

Relevant Pages

  • Re: Zip code radius search
    ... > declare @lat1 as float ... > declare @lon2 as float ... > declare @Distance as Float ...
    (microsoft.public.sqlserver.programming)
  • Zip code distance
    ... I am looking to find the distance between one zip code and another. ... declared @lat and @long for the default zip code that I want all distances ... declare @long as float ...
    (microsoft.public.sqlserver.programming)
  • Re: Zip code distance
    ... > I am looking to find the distance between one zip code and another. ... > Fields Latitude, Longitude, ZipCode ... > declare @long as float ...
    (microsoft.public.sqlserver.programming)
  • Re: A Lighting Model
    ... a good point of the inverse square law is that it's ... your distance calculations. ... slowdist(int x, int y) ...
    (rec.games.roguelike.development)
  • Re: sound synthesis
    ... > the declarations and using short float helped, ... (defun mix (target-samples source-samples start sample-rate) ... (declare (float sample-rate seconds) ... (defun fm-gong (time freq) ...
    (comp.lang.lisp)
Loading