Link to home
Start Free TrialLog in
Avatar of mercybthomas74
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
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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial