Implementing Zip Code Proximity Functions in MS SQL Server

This post is in the continuing series of articles on database functions, stored procedures, and implementing ZIP code proximity functionality.

Recently I had the opportunity to work on a project involving SQL Server. Having focused mostly on open source databases, I decided to brush up on SQL Server by going through the exercise of implementing the ZIP code proximity functions in T-SQL. The following User-Defined Functions (UDF) were developed and tested in SQL Server 2008 R2 Express, but should be compatible with older versions of SQL Server as well.

First we start with a simple table containing a list of ZIP codes, their latitude and longitude coordinates, city name, state name, and state abbreviation.

CREATE TABLE zipcodes (
   zipcode nvarchar(5) NOT NULL,
   lat float NULL,
   lon float NULL,
   city nvarchar(30) NOT NULL,
   state nvarchar(30) NOT NULL,
   state_abbrev nvarchar(2) NOT NULL
)

CalculateDistance is the function that, as it’s name suggests, calculates the distance between two points. While this function can be called directly, it’s intended to be called from the ZipProximity funtion.

CREATE FUNCTION CalculateDistance(@lat1 float, @lon1 float, @lat2 float, @lon2 float)
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN
   DECLARE @Distance float
   SET @Distance = 3959 * ACOS(SIN(RADIANS(@lat1)) * SIN(RADIANS(@lat2)) + COS(RADIANS(@lat1)) * COS(RADIANS(@lat2)) * COS((RADIANS(@lon2) - RADIANS(@lon1))));
   RETURN(@Distance);
END;

ZipProximity leverages CalculateDistance to determine which ZIP codes are within the provided radius. We iterate through all rows and return a table of ZIP codes that are within our radius. The table resembles the original zipcodes table, except that it includes a distance column that will be populated with the distance from our “home” ZIP code.

CREATE FUNCTION ZipProximity(@zip nvarchar(5), @radius integer)
RETURNS @retZipcodes TABLE
(
	zipcode nvarchar(5),
	lat float,
	lon float,
	city_name nvarchar(30),
	state_name nvarchar(30),
	state_abbrev nvarchar(2),
	distance float
)
AS
BEGIN
   DECLARE @home_lat float
   DECLARE @home_lon float

   SELECT @home_lat=lat, @home_lon=lon  FROM zipcodes WHERE zipcode = @zip;

   INSERT @retZipcodes
   SELECT zipcode, lat, lon, city, state, state_abbrev, dbo.CalculateDistance(@home_lat, @home_lon, lat, lon) AS distance
   FROM zipcodes WHERE dbo.CalculateDistance(@home_lat, @home_lon, lat, lon) < @radius ORDER BY distance;
   RETURN

END;

Using what we've just created, here's what a query might look like. We are querying all ZIP codes in a five mile radius of Cupertino, CA 95014.

SELECT * FROM dbo.ZipProximity('95014', 5);

zipcode lat          lon            city          state          state_abbrev distance
------- ------------ -------------- ------------- -------------- ------------ ----------------------
95014   37.30008     -122.09811     CUPERTINO     CALIFORNIA     CA           0
95015   37.32287     -122.05379     CUPERTINO     CALIFORNIA     CA           2.90040921217205
94024   37.34355     -122.10859     LOS ALTOS     CALIFORNIA     CA           3.05837716732744
95070   37.25798     -122.03213     SARATOGA      CALIFORNIA     CA           4.64994222426924
94087   37.34562     -122.03196     SUNNYVALE     CALIFORNIA     CA           4.80768907001485

But the advantage of returning a table is that you can query it with more advanced SQL.
Here we are only interested in the ZIP code, city, and state abbreviation for ZIP codes within a five mile radius of Cupertino, CA. but greater than a three mile radius.

SELECT zipcode, city, state_abbrev, distance FROM dbo.ZipProximity('95014', 5) WHERE distance > 3;

zipcode  city         state_abbrev  distance
-------  -----------  ------------  -----------------
94024	 LOS ALTOS    CA            3.05837716732744
95070	 SARATOGA     CA            4.64994222426924
94087	 SUNNYVALE    CA            4.80768907001485