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. Â 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 table 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)
/****** Object: Table [Storage].[ZipCodes] Script Date: 6/13/2013 8:08:01 AM ******/ SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_PADDING ON GOCREATE 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]GOSET ANSI_PADDING OFF GO
/****** Object: View [Selects].[ZipCodes] Script Date: 6/13/2013 8:08:14 AM ******/ SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOCREATE VIEW [Selects].[ZipCodes] WITH SCHEMABINDING AS SELECT zipID, ZipCode, Latitude, Longitude, City, State, County, ZipType, Location FROM Storage.ZipCodesGO
Stored Procedure: [List].[ZipSearch]
/****** Object: StoredProcedure [List].[ZipSearch] Script Date: 6/13/2013 8:08:28 AM ******/ SET ANSI_NULLS ON GOSET 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 FloatAS 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; ENDGO
Enjoy and Happy Programming!