Determine Distance Between Any Two Locations

,

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

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

Rate

5 (4)

Share

Share

Rate

5 (4)