?
Solved

Calculate Miles from 2 zipcodes

Posted on 2009-02-23
1
Medium Priority
?
309 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:mercybthomas74
1 Comment
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 23713152
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

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question