SQL 2012 ZipCode Radius Search

SQL 2012 ZipCode Radius Search

SQL 2012 ZipCode Radius Search

In building my MySQL Store Locator, I figured that it may be a good idea to do the same thing for MS SQL.   Since I have 2012 installed, I built this particularly for that version, and am unsure if it will work in previous versions.  I do know that the Geo datatypes have been severly refined and improved for 2012, so maybe you can keep that in mind when you try this out.

Similar situation, I needed something like this built for a ‘Store Locator’, this time for a .Net site I was building.  I have since decided to add it into my personal webservices which you can see in action here: My Zip Code Service

All I’m going to show you is what I did, table, view, and stored procedure codes, and let you figure out the rest on your own.

First and foremost is the table structure, you will notice that I have a column in here ‘Location’ that looks way different than the others.  It’s a calculated column containing the Geographical DT representation of the Lat and Lon columns.   Make sure you download the latest ZipCode table from here before proceeding…  you’ll need it to populate this table with all the US Zip Codes you will need for this.

Without any further ado…

(Side Notes: I have created some indices on the view: Clustered/Unique on the zipID Column, Non-Clustered on Latitude and Longitude columns, and Non-Clustered on ZipCode column)

Table: [Storage].[ZipCodes]

/****** Object:  Table [Storage].[ZipCodes]    Script Date: 6/13/2013 8:08:01 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [Storage].[ZipCodes](
	[zipID] [bigint] IDENTITY(1,1) NOT NULL,
	[ZipCode] [varchar](10) NULL,
	[Latitude] [float] NULL,
	[Longitude] [float] NULL,
	[City] [varchar](255) NULL,
	[State] [varchar](2) NULL,
	[County] [varchar](255) NULL,
	[ZipType] [varchar](255) NULL,
	[Location]  AS ([geography]::Point([Latitude],[Longitude],(4326))) PERSISTED,
 CONSTRAINT [PK_zip_codes] PRIMARY KEY CLUSTERED 
(
	[zipID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

View: [Selects].[ZipCodes]

/****** Object:  View [Selects].[ZipCodes]    Script Date: 6/13/2013 8:08:14 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [Selects].[ZipCodes] WITH SCHEMABINDING
AS
SELECT        zipID, ZipCode, Latitude, Longitude, City, State, County, ZipType, Location
FROM            Storage.ZipCodes

GO

Stored Procedure: [List].[ZipSearch]

/****** Object:  StoredProcedure [List].[ZipSearch]    Script Date: 6/13/2013 8:08:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:			o7th Web Design .:. Kevin C. Pirnie
-- Create Date:		05/29/2013
-- Name:			List.ZipSearch
-- =============================================
CREATE PROCEDURE [List].[ZipSearch]

	@ZipCode	VarChar(10),
	@RadiusMile	Float

AS
BEGIN
	SET NOCOUNT ON;

	Declare @lat Float;
	Declare @lon Float;
	Declare @point Geography;

	Select @lat = Latitude, @lon = Longitude FROM Selects.ZipCodes Where ZipCode = @ZipCode;

	Set @point = geography::Point(@lat, @lon, 4326);

	Select ZipID, ZipCode, City, State, County, (@point.STDistance([Location]) * 0.000621371192) As Mileage
		   From Selects.ZipCodes
	Where @point.STDistance([Location]) <= (@RadiusMile * 1609.344)
	Order By Mileage;	   

END

GO

Enjoy and Happy Programming!

Categories


Let Us Help

Get You Online

Contact Us Today

Important Cookie Information
Our website uses cookies. By continuing to browse the site you are agreeing to our use of cookies. For more details about cookies and their use, please see our Cookie Policy.