Kevin Pirnie' Articles

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)

Table: [Storage].[ZipCodes]

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

View: [Selects].[ZipCodes]

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

Kevin Pirnie

20+ Years of PC and server maintenance & over 15+ years of web development/design experience; you can rest assured that I take every measure possible to ensure your computers are running to their peak potentials. I treat them as if they were mine, and I am quite a stickler about keeping my machines up to date and optimized to run as well as they can.