SQL Geolocation

gistfile1.sql

/* Change ZipTest to whatever database you want to store the ZipCodes table in */
USE ZipTest
GO
IF EXISTS (SELECT *
				FROM sysobjects
				WHERE id = OBJECT_ID(N'ZipTempTable') 
				AND OBJECTPROPERTY(id, N'IsUserTable') = 1)				
BEGIN
	DROP TABLE ZipCodesTemp;
END
GO
IF EXISTS (SELECT *
				FROM sysobjects
				WHERE id = OBJECT_ID(N'ZipCodes')
				AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	DROP TABLE ZipCodes;
END
GO

/* Create a temporary table to import the raw US.txt data */
CREATE TABLE ZipCodesTemp(
[country_code] [VARCHAR](2) NULL, /* Country Code */
[postal_code] [VARCHAR](5) NULL,  /* Zip Code */
[place_name] [VARCHAR](200) NULL, /* City */
[admin_name1] [VARCHAR](50) NULL, /* State */
[admin_code1] [VARCHAR](2) NULL,  /* State Abbreviation */
[admin_name2] [VARCHAR](50) NULL, /* County */
[admin_code2] [VARCHAR](5) NULL,  /* Not Used */
[admin_name3] [VARCHAR](1) NULL,  /* Not Used */
[admin_code3] [VARCHAR](1) NULL,  /* Not Used */
[latitude] [DECIMAL](8,5) NULL,   /* Latitude */
[longitude] [DECIMAL](8,5) NULL,  /* Longitude */
[accuracy] [VARCHAR](1) NULL      /* Not Used */
);

CREATE TABLE ZipCodes(
[Country] [VARCHAR](2)  NULL,
[ZipCode] [VARCHAR](5) NOT NULL,
[City] [VARCHAR](200)  NOT NULL,
[State] [VARCHAR](50)  NULL,
[StateAbbreviation] [VARCHAR](2)  NULL,
[County] [VARCHAR](50) NULL,
[Latitude] [DECIMAL](8,5) NOT NULL,
[Longitude] [DECIMAL](8,5) NOT NULL,
[GeogCol1] [GEOGRAPHY]  NULL,
[GeogColTemp] [varchar](100) NULL
);

/* Bulk inserts from US.txt.  Make sure to set your path below (C:\PATH\TO\YOUR\US.txt). */
DECLARE @bulk_cmd VARCHAR(1000)
SET @bulk_cmd = 'BULK INSERT ZipCodesTemp
  FROM ''C:\PATH\TO\YOUR\US.txt''
  WITH (FIELDTERMINATOR=''\t'', ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd);

 /* Copies the necessary data from ZipCodesTemp to ZipCodes */ 
INSERT  ZipCodes (Country,ZipCode,City,State,StateAbbreviation,County,Latitude,Longitude)
SELECT DISTINCT country_code,postal_code,place_name,admin_name1,admin_code1,admin_name2,latitude,longitude
FROM  ZipCodesTemp

/* Creates a point which is used in the next part to create the final geography datatype */
UPDATE ZipCodes
SET GeogColTemp = 'POINT(' + convert(varchar(100),longitude)
+' ' +  convert(varchar(100),latitude) +')'

/* This step creates the actual geography data type we will use in our query.
	Can easily be combined with the step above.  I'm just being explicit.
	URL for STGeomFromText function @ http://msdn.microsoft.com/en-us/library/bb933823.aspx */
UPDATE ZipCodes
SET GeogCol1 =  geography::STGeomFromText(GeogColTemp,4326)

/* Removes temporary column with point data */
ALTER TABLE ZipCodes DROP COLUMN GeogColTemp

/*  Create clustered primary key */
ALTER TABLE ZipCodes ADD
    CONSTRAINT [PK_ZipCode] PRIMARY KEY  CLUSTERED
    (
        Zipcode,
        City,
        Longitude
    ) WITH  FILLFACTOR = 100

/* Creates the spatial index which is what allows all the magic to happen quickly.
	If you want to search against another table you will have to run a similar query on that table */
	CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col1
   ON ZipCodes(GeogCol1);

   /*Drops the temporary table we no longer need */
DROP TABLE ZipCodesTemp


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'GetPostcodesWithinDistance') 
		                        AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE GetPostcodesWithinDistance;
END
GO									
/* This stored procedure queries the ZipCode table and gets the 
   Geography data for the given zip code.  Then, it looks for any 
   places in the MyPlaces table that are within @miles of that zip code */
CREATE PROCEDURE [dbo].[GetPostcodesWithinDistance]
@ZipCode VARCHAR(5),
@Miles INT
AS
BEGIN
  DECLARE @home geography
  SELECT @home = GeogCol1 FROM ZipCodes
      WHERE ZipCode = @ZipCode
  SELECT * FROM MyPlaces
  WHERE MyPlaces.GeoLocation.STDistance(@home) <= (@Miles * 1609.344)
END