# 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
``````
Asked:
###### Who is Participating?

SQL Server DBA & Architect, EE Solution GuideCommented:
Try this one below:
Might be some logical mistakes in your function

This function allows you to calculate the distance in miles or kilometers between any two points on the earth. I created to calculate the distance between two zip codes but is can be used for any two coordinates for which latitude and longitude is known.

NOTE: A database for the coordinates of any US postal code can be obtained for free from Source Forge in CSV format. The URL is

http://prdownloads.sourceforge.net/zips/zips.csv.zip?download

Referred from http://www.sqlservercentral.com/scripts/Miscellaneous/31673/

Posted the entire thing over here since you may not have a login id for that site.
``````IF EXISTS (SELECT *
FROM   sysobjects
WHERE  name = N'CalcDistanceBetweenGeoLocations')
DROP FUNCTION dbo.CalcDistanceBetweenGeoLocations
GO
/**************************************************************************
DESCRIPTION: Gets the distance in miles or kilometers between two points on the earth

PARAMETERS:
@LatitudeA		- Latitude of first point
@LongitudeA		- Longitude of first point
@LatitudeB		- Latitude of second point
@LongitudeB		- Longitude of second point
@InKilometers		- 1 if return distance in kilometers
0 if return distance in miles

RETURNS:
Float value of distance between two points in miles or kilometers

USAGE:		DECLARE 	@LatitudeA 	FLOAT,
@LongitudeA 	FLOAT,
@LatitudeB 	FLOAT,
@LongitudeB 	FLOAT
SELECT @LatitudeA = latitude, @LongitudeA = longitude FROM zip_code WHERE zip_code = '92121'
SELECT @LatitudeB= latitude, @LongitudeB = longitude FROM zip_code WHERE zip_code = '92008'
SELECT dbo.CalcDistanceBetweenGeoLocations(@LatitudeA, @LongitudeA,
@LatitudeB, @LongitudeB,
0) as [miles]
SELECT dbo.CalcDistanceBetweenGeoLocations(@LatitudeA, @LongitudeA,
@LatitudeB, @LongitudeB,
1) as [kilometers]

AUTHOR:	Karen Gayda

DATE: 	04/12/2006

MODIFICATION HISTORY:
WHO		DATE		DESCRIPTION
---		----------	---------------------------------------------------

***************************************************************************/
CREATE FUNCTION dbo.CalcDistanceBetweenGeoLocations
(@LatitudeA 	FLOAT = NULL,
@LongitudeA 	FLOAT = NULL,
@LatitudeB 	FLOAT = NULL,
@LongitudeB 	FLOAT = NULL,
@InKilometers	BIT = 0
)
RETURNS FLOAT
AS
BEGIN

DECLARE @Distance FLOAT

SET @Distance = (SIN(RADIANS(@LatitudeA)) *
SIN(RADIANS(@LatitudeB)) +
COS(RADIANS(@LatitudeA)) *
COS(RADIANS(@LatitudeB)) *
COS(RADIANS(@LongitudeA - @LongitudeB)))

--Get distance in miles
SET @Distance = (DEGREES(ACOS(@Distance))) * 69.09

--If specified, convert to kilometers
IF @InKilometers = 1
SET @Distance = @Distance * 1.609344

RETURN @Distance

END
GO
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.