September 25, 2006 at 10:23 am
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
September 25, 2006 at 11:27 am
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
September 25, 2006 at 11:59 am
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!!
August 12, 2010 at 11:00 am
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
August 12, 2010 at 11:05 am
Never mind, scratch that. I did some searches on the Internet and found some sources.
By the way, thanks for the code below.
David
August 13, 2010 at 2:50 pm
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"
August 13, 2010 at 3:04 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply