mercybthomas74
asked on
Calculate Miles from 2 zipcodes
I have a function for calculating the miles in SQL
When compared with PC*Miler , i get differnet answer
What is the differences, below is the code
I have a table- USZIPData with all zipcodes
As simple I tried:
select [dbo].ZIPDistance ('93905','95363')as Miles
Results:
59.1576
In Excel I tried : = Miles(93905,95336) = 97
When compared with PC*Miler , i get differnet answer
What is the differences, below is the code
I have a table- USZIPData with all zipcodes
As simple I tried:
select [dbo].ZIPDistance ('93905','95363')as Miles
Results:
59.1576
In Excel I tried : = Miles(93905,95336) = 97
CREATE FUNCTION [dbo].ZIPDistance
(
@FromZIP nvarchar(5),
@ToZIP nvarchar(5)
)
RETURNS float
AS
BEGIN
declare @Miles float
Select
@Miles =
6371/1.609344*2*atn2(
sqrt(square(sin((t.yrad-f.yrad)/2)) + cos(f.yrad)*cos(t.yrad)*square(sin((t.xrad-f.xrad)/2))),
sqrt(1-(square(sin((t.yrad-f.yrad)/2)) + cos(f.yrad)*cos(t.yrad)*square(sin((t.xrad-f.xrad)/2))))
)
from
(select f.*, radians(pointx) xrad, radians(pointy) yrad
from USZIPData f) f
cross join
(select t.*, radians(pointx) xrad, radians(pointy) yrad
from USZIPData t) t
where
f.ZIP = @FromZIP
and t.ZIP = @ToZIP
RETURN @Miles
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.