Find distance between zipcodes

Question

Post reply

Find distance between zipcodes

jenniferhu46

SSCrazy

Points: 2990
More actions
September 25, 2006 at 10:23 am

#115741

Hi, I have a stored procedure that works fine to find stores' addresses within 10 miles based on user's input of zipcode.
However, I want to get the distance as well. How to change this sp to get the millage distance? Thanks.
-----------------------------------------
CREATE Procedure sp_CommunityStoreList
(
@ZipCode Numeric(5, 0) = Null
   )
As
set nocount on

Declare @Latitude Float(10)
Declare @Longitude Float(10)
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From Zips_Range_Table Where Zip_Code = @ZipCode

Select
'*    ' AS Dot,'Burger King # :' AS BK,s.Store_Num +' - ' AS StoreNum,s.Store_Address+', '+s.City+', '+s.State+' '+s.Zip_Code AS Address
From
Zips_Range_Table z, InSightAmKgODS.micros.Store_Table s
Where
Longitude Is Not Null
And Latitude Is Not Null
And (
   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))   ) <=10
And z.zip_code=s.zip_code and s.co_code=1 and s.store_mgr is not null
Group by s.zip_Code, s.store_num, s.store_address,s.city,s.state
return
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 1

it's actually in your code already, u simply need to rearrange the query to get the mileage.

--it's calculated as [using a store coordinates: Longtitude = 0 Latitude = 0 as an example ]

Declare @Latitude Float(10)

Declare @Longitude Float(10)

Select @Latitude = 1

Select @Longitude = 1

select 3958.75 * ACos(Sin(@Latitude/57.2958) *

Sin(0/57.2958) +

Cos(@Latitude/57.2958) *

Cos(0/57.2958) *

Cos(0/57.2958 - @Longitude/57.2958))

--returns 97 miles which is a good number - as far as I remember from Geography course in --my midschool

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 2

it's fun guys..

now i can calculate the distance betw the city I used ot live in Russia and my curernt location in the States..u only need to know the coordinates (available for free from http://www.globalguide.org):

Declare @Latitude Float(10), @LatitudeStartPoint Float(10)

Declare @Longitude Float(10), @LongitudeStartPoint Float(10)

Select @Latitude = 56.85 --location

Select @Longitude = 60.6 --location

Select @LatitudeStartPoint = 42.55 --starting point to measure distance from

Select @LongitudeStartPoint = -75.84 --starting point to measure distance from

select 3958.75 * ACos(Sin(@Latitude/57.2958) *

Sin(@LatitudeStartPoint/57.2958) +

Cos(@Latitude/57.2958) *

Cos(@LatitudeStartPoint/57.2958) *

Cos(@LongitudeStartPoint/57.2958 - @Longitude/57.2958))

--and it's 5118 miles!!

RaiderX Say Hey Kid Points: 678 More actions · Answer 3

Hi guys, I know this is an old thread and am hoping you still are connected with SQL Server Central.

By any chance, do you know where I can get a copy of the table that has the latitude/longitude values of the zip codes?

Thanks,

David

RaiderX Say Hey Kid Points: 678 More actions · Answer 4

Never mind, scratch that. I did some searches on the Internet and found some sources.

By the way, thanks for the code below.

David

DougGifford SSCommitted Points: 1815 More actions · Answer 5

RaiderX (8/12/2010)
Never mind, scratch that. I did some searches on the Internet and found some sources.
By the way, thanks for the code below.
David

For those of us that may reference this in the future, What did you end up with as your sources?

"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"

RaiderX Say Hey Kid Points: 678 More actions · Answer 6

Of course, I should have posted it. I ended up at

http://databases.about.com/od/access/a/zipcodedatabase.htm

It was an Access database that I moved to SQL Server 2008.

It's a little old and some zip codes have changed, but for what I was doing, it was close enough. I compared a number of my results with some online calculations and mine were off by 3-4% when there was a discrepancy.

David