Solved

# Calculate Miles from 2 zipcodes

Posted on 2009-02-23
294 Views
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(

)

from

from USZIPData f) f

cross join

from USZIPData t) t

where

f.ZIP  =  @FromZIP

and t.ZIP  =  @ToZIP

RETURN @Miles

END
``````
0
Question by:mercybthomas74
1 Comment

LVL 57

Accepted Solution

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

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

--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

## Featured Post

### Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.