From the January 2010 Issue of Prestwood eMag

DBA SQL Servers: Geolocation: Step by Step By Bryan Valencia

Posted 14 years ago on 1/16/2010 Take Away: I need to perform a search for all the (jobs, contractors, appraisers, deli's) within 50 miles of my home.  How can I do that? This tutorial will guide you through the process step-by-step and provide you with the ability to input any two zip codes and get back the distance based on latitude and longitude. The skills and tools needed for this task are simple... basic MSSQL skills  Tags: geolocation , MSSQL , Views , latitude , longitude KB102088

There are 3 steps needed to find the approximate distance between two places on Earth, and luckily it's not that hard to do.

1. Find a way to get the latitude and longitude of the two places on the globe.
2. Work the math to find the spherical distance (straight line over the sphere)
3. 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.

1. Open SQL Server Management Console.
2. Connect to the server
3. In the object explorer, select the database you want to add the geodata to (or create a new database)
4. Right-click the database name in the object explorer, and select Tasks -> Import Data.
5. Data Source: Microsoft Access,  Browse for the Downloaded zipcodes.mdb file.  Next
6. Make sure the database info is correct, and Next >
7. Copy Data from one or more tables or views. Next >
8. Select all 4 tables for import. Next>
9. Click Finish>>|
   

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.[ZIP Codes].City,

  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        

  dbo.[ZIP Codes]

  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

GO

The resulting table has it's longitude and latitude data in floating point degrees and radians, which suites our needs perfectly. 

Distance Functions

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)
   
    --convert degrees to 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 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

GO

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=0;
    SELECT @deglong1=0;
    SELECT @deglat2=0;
    SELECT @deglong2=0;
   
    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

GO
Now...

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

The result set looks a lot like this. 

Mike Prestwood

Comment 1 of 3 Great article Bryan! I made this article the featured article for this month's eMag (Jan 2010). I also moved it to the database KB topic as that seemed more appropriate. Posted 14 years ago

Bryan Valencia

Comment 2 of 3 Mike, is there any way we can attach the current version of that MDB file to this article, in case it becomes unavailable at some point?  Posted 14 years ago

Mike Prestwood

Comment 3 of 3 Hi Bryan, Yes, edit your post and click the <Save & Attach> button. Posted 14 years ago

Article Contributed By Bryan Valencia:

Bryan Valencia is the author of millions of lines of code and writes for PrestwoodBoards and Visual Studio Journey on-line magazines. Visit Profile