Using MSSQL to Calculate Map Distances

This procedure will show you how to create a SQL Server function that accepts 2 map coordinates in and calculates the surface distance between them.



First we will create the function.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Bryan Valencia
-- Create date: 12/16/2009
-- Description: takes latitude/longitude for 2 
--   places on Earth and gives the spherical distance.
-- =============================================
DROP FUNCTION Geo_Distance
GO 

CREATE FUNCTION Geo_Distance
( 
 @lat1 float,
 @long1 float,
 @lat2 float,
 @long2 float
)
RETURNS float
AS
BEGIN
 
 DECLARE @r float 
 --radius of the Earth
 --select @r=3437.74677 --(nautical miles)
 --select @r=6378.7 --(kilometers)
 SELECT @r=3963.0 --(statute miles) 
 
 --radians
 DECLARE @radlat1 float
 DECLARE @radlong1 float
 DECLARE @radlat2 float
 DECLARE @radlong2 float
 
 SELECT @radlat1 = RADIANS(@lat1)
 SELECT @radlong1 = RADIANS(@long1)
 SELECT @radlat2 = RADIANS(@lat2)
 SELECT @radlong2 = RADIANS(@long2)
 
 --calculate answer (from http://www.sqlteam.com/article/intro-to-user-defined-functions-updated)
 -- and http://www.meridianworlddata.com/Distance-Calculation.asp
 
 DECLARE @answer float
 SELECT @answer = @r * ACOS(SIN(@radlat1)*SIN(@radlat2)+COS(@radlat1)*COS(@radlat2)*cos(@radlong2 - @radlong1))
 RETURN @answer

END

GO

So now we have a function to accept two lat/long coords and return the distance.  To use it in a select, (assuming you have a data table of geodata organized by zip code, like this... Access Zip Code Database.



Import this data to SQL Server and then use this select statement.

Select distinct Z1.[ZIP Code], Z1.City, Z1.[State Code], 
 dbo.Geo_Distance(
  cast(Z2.Latitude as float),cast(Z2.Longitude as float),
  cast(Z1.Latitude as float),cast(Z1.Longitude as float)) as Distance 
 
from [ZIP Codes] Z1
left outer join [ZIP Codes] Z2 on (Z2.[ZIP Code]='94558')
order by 4

Note here that the data as presented in the Access table stores the latitudes and longitudes as text, so we need to use cast to force it to floats. This gives us a result set that looks a lot like this...