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 ServiceAll 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 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!