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)
/****** 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
/****** 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!