I am working on a personal project that needed a list of US City, State and Zip and the ability to get a list of nearby Zips by radius. For awhile there, I thought I was going to have to pay for a list of ZipCodes but thank goodness I found Gabe Summer’s blog. To pay it forward, so to speak, I have adapted Gabe’s code to SQL Server.
CityStateZip Table
CREATE TABLE [dbo].[CityStateZip](
[ZipCode] [char](5) NOT NULL,
[Latitude] [numeric](18, 6) NULL,
[Longitude] [numeric](18, 6) NULL,
[City] [varchar](50) NOT NULL,
[State] [char](2) NOT NULL,
[County] [varchar](50) NOT NULL,
[Zip_Class] [varchar](50) NOT NULL,
CONSTRAINT [PK_CityStateZip] PRIMARY KEY CLUSTERED
(
[ZipCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GetDistance Function
CREATE FUNCTION GetDistance(
@lat1 numeric (9,6),
@lon1 numeric (9,6),
@lat2 numeric (9,6),
@lon2 numeric (9,6)
) RETURNS decimal (10,5)
BEGIN
DECLARE @x decimal (20,10);
SET @x = sin( @lat1 * pi()/180 ) * sin( @lat2 * pi()/180 ) + cos(@lat1 * pi()/180 ) * cos( @lat2 * pi()/180 ) * cos( abs ( (@lon2 * pi()/180) – (@lon1 * pi()/180) ) );
SET @x = atan( ( sqrt( 1- power( @x, 2 ) ) ) / @x );
RETURN ( 1.852 * 60.0 * ((@x/pi())*180) ) / 1.609344;
END
GetNearbyZipCodes Stored Procedure
CREATE PROCEDURE usp_GetNearbyZipCodes
@zipbase varchar (6),
@range numeric (15)
AS
BEGIN
SET NOCOUNT ON
DECLARE @lat1 decimal (5,2);
DECLARE @long1 decimal (5,2);
DECLARE @rangeFactor decimal (7,6);
SET @rangeFactor = 0.014457;
SELECT @lat1=latitude, @long1=longitude
FROM CityStateZip
WHERE zipcode = @zipbase;
SELECT B.zipcode, dbo.GetDistance(@lat1,@long1,B.latitude,B.longitude) Distance
FROM CityStateZip AS B
WHERE B.latitude BETWEEN @lat1-(@range*@rangeFactor) AND @lat1+(@range*@rangeFactor)
AND B.longitude BETWEEN @long1-(@range*@rangeFactor) AND @long1+(@range*@rangeFactor)
AND dbo.GetDistance(@lat1,@long1,B.latitude,B.longitude) <= @range;
END
Enjoy!
