There are 3 steps needed to find the approximate distance between two places on Earth, and luckily it's not that hard to do.
Find a way to get the latitude and longitude of the two places on the globe.
Work the math to find the spherical distance (straight line over the sphere)
Return the distance to the calling function.
Latitude and Longitude
Getting latitude and longitude can be done in many ways. You can prompt the user for them, but few of us know our home coordinates, let alone the coordinates of some random place we want to find.
Luckily, every address on Earth has a latitude and longitude associated with it. Unluckily, I have yet to see a good source of this data.
For my purposes, I chose to resolve the location of the end points by U.S. Zip Code. I found a reasonably small database containing this information here. This Access database is a set of tables that can be used for our purposes.
To upload this data from Access to MSSQL, you don't even need Access installed. Here is the step-by-step.
Open SQL Server Management Console.
Connect to the server
In the object explorer, select the database you want to add the geodata to (or create a new database)
Right-click the database name in the object explorer, and select Tasks -> Import Data.
Data Source: Microsoft Access, Browse for the Downloaded zipcodes.mdb file. Next
Make sure the database info is correct, and Next >
Copy Data from one or more tables or views. Next >
Select all 4 tables for import. Next>
Now the data is now added to your SQL Server database in 4 tables.
Now you have the data (with no indexes or relationships) I recommend adding a primary key to the first column of each table.
The good news for us is that all the data we need is in the ZipCodes Table. The bad news is that the latitude/longitude is stored as nvarchar data, which is not what we need for our calculations.
We could just convert the data in place, but I wanted to leave my options open in case the data is updated regularly, so I chose to create a view instead. This also helps because the longitude and latitude data is in degrees, and the trig functions in SQL Server need radians. So I created this view.
CREATE VIEW [dbo].[Geolocations] AS SELECT
dbo.[ZIP Codes].[ZIP Code] AS ZIP,
dbo.States.[State Abbreviation] AS st,
dbo.States.[State Name] AS state, CAST(dbo.[ZIP Codes].Latitude AS float) AS deg_latitude,
CAST(dbo.[ZIP Codes].Longitude AS float) AS deg_longitude,
RADIANS(CAST(dbo.[ZIP Codes].Latitude AS float)) AS rad_latitude,
RADIANS(CAST(dbo.[ZIP Codes].Longitude AS float)) AS rad_longitude, dbo.Class.Description AS [ZIP Class] FROM
LEFT OUTER JOIN dbo.States ON dbo.[ZIP Codes].[State Code] = dbo.States.[State Code]
LEFT OUTER JOIN dbo.Class ON dbo.[ZIP Codes].Class = dbo.Class.Class
The resulting table has it's longitude and latitude data in floating point degrees and radians, which suites our needs perfectly.
The distance function we need to calculate the direct route from point A to point B on a sphere is not simple. However, it is readily available, here.
CREATE FUNCTION [dbo].[Geo_Distance] ( @lat1 float, @long1 float, @lat2 float, @long2 float ) RETURNS float AS BEGIN --Bryan Valencia --12/23/2009 --MSSQL function to calculate distance from 2 lat/long pairs.
--accepts data for two coordinates in DEGREES, and --returns the distance between these two points on the globe
DECLARE @r float --radius of the Earth --select @r=3437.74677 --(nautical miles) --select @r=6378.7 --(kilometers) SELECT @r=3963.0 --(statute miles)
--calculate the distance across the curvature of the Earth. DECLARE @answer float SELECT @answer = @r * ACOS(SIN(@radlat1)*SIN(@radlat2)+COS(@radlat1)*COS(@radlat2)*cos(@radlong2 - @radlong1)) RETURN @answer END
This function accepts the values and calculates the distance, and is not bound to any tables. Next, we will need a function that accepts two zip codes, looks up the geolocation of each, and passes the coordinates to this function.
CREATE function [dbo].[zip_distance] ( @zip1 varchar(15), @zip2 varchar(15) ) returns float as BEGIN DECLARE @deglat1 float DECLARE @deglong1 float DECLARE @deglat2 float DECLARE @deglong2 float
SELECT @deglat1 = ( SELECT avg(deg_latitude) FROM dbo.Geolocations g where g.ZIP = @zip1 ) SELECT @deglat2 = ( SELECT avg(deg_latitude) FROM dbo.Geolocations g where g.ZIP = @zip2 ) SELECT @deglong1 = ( SELECT avg(deg_longitude) FROM dbo.Geolocations g where g.ZIP = @zip1 ) SELECT @deglong2 = ( SELECT avg(deg_longitude) FROM dbo.Geolocations g where g.ZIP = @zip2 ) declare @answer float select @answer= (dbo.Geo_Distance(@deglat1,@deglong1,@deglat2,@deglong2)) return @answer END
To use this function just construct your SQL like so...
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
I selected my home town of Napa, California as a starting point for the distance calculations, and this SQL will give the locations of all zip codes ordered by distance from Napa. Smaller towns that share a zip code with another town may not be listed. Note how calling the Geo_Distance function works - the results are in statute miles (because that's the units we used for the size of the planet) as the crow flies (not driving, walking, or swimming miles).