SQL Server 2008 Proximity Search With The Geography Data Type
by SQLDenis

Permalink 11 Feb 2009 13:27 , Categories: Data Modelling & Design, Microsoft SQL Server Tags: geography, geomapping, spatial data, sql server 2008

George and I decided to show you how you can do simple radius searches based on Zip Codes, he did the SQL 2005 and before version here: SQL Server Zipcode Latitude/Longitude proximity distance search and I will do the SQL Server 2008 version.

The first thing we need to do is load our data. There are various sources for this data, ranging from free to expensive monthly subscriptions. One source of free data is: http://download.geonames.org/export/zip/ make sure to grab the US.ZIP file for this demo. Unrar/Unzip the file and you will have a US.txt file.

Once you have downloaded your data, the next step is to import it in to your database. You can use the following script to do it.

1. CREATE TABLE ZipCodesTemp(
2.     [Country] [VARCHAR](2) NULL,
3.     [ZipCode] [VARCHAR](5) NULL,
4.     [City] [VARCHAR](200) NULL,
5.     [STATE] [VARCHAR](50) NULL,
6.     [StateAbbreviation] [VARCHAR](2) NULL,
7.     [County] [VARCHAR](50) NULL,
8.     [Unused1] [VARCHAR](5) NULL,
9.     [Unused2] [VARCHAR](1) NULL,
10.     [Latitude] [DECIMAL](8,5) NULL,
11.     [Longitude] [DECIMAL](8,5) NULL,
12.     [Unused3] [VARCHAR](1) NULL
13.     )
14.
15.
16. DECLARE @bulk_cmd VARCHAR(1000)
17. SET @bulk_cmd = ‘BULK INSERT ZipCodesTemp
18.   FROM ”C:\YourFolder\US.txt”
19.   WITH (FIELDTERMINATOR=”\t”, ROWTERMINATOR = ”’+CHAR(10)+”’)’
20.
21. EXEC(@bulk_cmd)

Code is hidden, SHOW

Now you will create this table

1. CREATE TABLE ZipCodes(
2.     [Country] [VARCHAR](2)  NULL,
3.     [ZipCode] [VARCHAR](5) NOT NULL,
4.     [City] [VARCHAR](200)  NULL,
5.     [STATE] [VARCHAR](50)  NULL,
6.     [StateAbbreviation] [VARCHAR](2)  NULL,
7.     [County] [VARCHAR](50) NULL,
8.     [Latitude] [DECIMAL](8,5) NOT NULL,
9.     [Longitude] [DECIMAL](8,5) NOT NULL,
10.     [GeogCol1] [GEOGRAPHY]  NULL,
11.     [GeogColTemp] [VARCHAR](100) NULL
12.     )

Code is hidden, SHOW

There is at least one duplicate row in this file so we will import only uniques

1. INSERT  ZipCodes (Country,ZipCode,City,STATE,StateAbbreviation,County,Latitude,Longitude)
2. SELECT DISTINCT Country,ZipCode,City,STATE,StateAbbreviation,County,Latitude,Longitude
3. FROM  ZipCodesTemp

Code is hidden, SHOW

Our next step will be to update the GeogCol1 table with something that SQL server can understand.
Here is some sample code that displays the format of this datatype

1. DECLARE @h geography;
2. SET @h = geography::STGeomFromText(‘POINT(-77.36750 38.98390)’, 4326);
3. SELECT @h

Code is hidden, SHOW

output
———————————————-
0xE6100000010C6744696FF07D4340EC51B81E855753C0

As you can see it is some binary data. This data is using the World Geodetic System 1984 (WGS 84)

To see if this is supported in your database you can run this query

1. SELECT * FROM sys.spatial_reference_systems

Code is hidden, SHOW

And yes in my database it has a spatial_reference_id of 4326

1. SELECT * FROM sys.spatial_reference_systems
2. WHERE spatial_reference_id = 4326

Code is hidden, SHOW

Here is the meta data

GEOGCS[“WGS 84″, DATUM[“World Geodetic System 1984″,
ELLIPSOID[“WGS 84″, 6378137, 298.257223563]], PRIMEM[“Greenwich”, 0],
UNIT[“Degree”, 0.0174532925199433]]

Back to our code, in order to have this data in our table

SET @h = geography::STGeomFromText(‘POINT(-77.36750 38.98390)’, 4326);

We need to do some things, first we update our temp column

1. UPDATE zipcodes
2. SET GeogColTemp= ‘POINT(‘ + CONVERT(VARCHAR(100),longitude)
3. +’ ‘ +  CONVERT(VARCHAR(100),latitude) +’)’

Code is hidden, SHOW

Now we can update out geography column

1. UPDATE zipcodes
2. SET GeogCol1 =  geography::STGeomFromText(GeogColTemp,4326)

Code is hidden, SHOW

We can drop the temp column now

1. ALTER TABLE zipcodes DROP COLUMN GeogColTemp

Code is hidden, SHOW

Now we have to add a primary key, this is needed because otherwise we won’t be able to create our spatial index and the following message would be displayed

Server: Msg 12008, Level 16, State 1, Line 1
Table ‘zipcodes’ does not have a clustered primary key as required by the spatial index. Make sure that the primary key column exists on the table before creating a spatial index.

1. ALTER TABLE zipcodes ADD
2.     CONSTRAINT [PK_ZipCode] PRIMARY KEY  CLUSTERED
3.     (
4.         Zipcode,
5.         Longitude
6.     ) WITH  FILLFACTOR = 100

Code is hidden, SHOW

Create the spatial index

1. CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col1
2.    ON zipcodes(GeogCol1);

Code is hidden, SHOW

first I will show you an example to calculate the distance that you can execute

1. DECLARE @g geography;
2. DECLARE @h geography;
3. SET @h = geography::STGeomFromText(‘POINT(-77.36750 38.98390)’, 4326);
4. SET @g = geography::STGeomFromText(‘POINT(-77.36160 38.85570)’, 4326);
5. SELECT @g.STDistance(@h)/1609.344;

Code is hidden, SHOW

as you can see the distance is 8.8490611480890067

Now I want to see all the zipcode which are within 20 miles of zipcode 10028 (yes I used to live there)

Here is a way that will take a long time since it is not sargable, this will take about 2 seconds

1. SELECT h.*
2. FROM zipcodes g
3. JOIN zipcodes h ON g.zipcode <> h.zipcode
4. AND g.zipcode = ‘10028’
5. AND h.zipcode <> ‘10028’
6. WHERE g.GeogCol1.STDistance(h.GeogCol1)/1609.344 <= 20

Code is hidden, SHOW

Now we all know functions on the left side of the operator are bad, here is how this is optimized, we switch the calculation to the right side of the = sign

1. SELECT h.*
2. FROM zipcodes g
3. JOIN zipcodes h ON g.zipcode <> h.zipcode
4. AND g.zipcode = ‘10028’
5. AND h.zipcode <> ‘10028’
6. WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(20 * 1609.344)

Code is hidden, SHOW

that ran in between 15 and 60 milliseconds

To find everything between 10 and 20 miles you can use this

1. SELECT h.*
2. FROM zipcodes g
3. JOIN zipcodes h ON g.zipcode <> h.zipcode
4. AND g.zipcode = ‘10028’
5. AND h.zipcode <> ‘10028’
6. WHERE g.GeogCol1.STDistance(h.GeogCol1)<=(20 * 1609.344)
7. AND g.GeogCol1.STDistance(h.GeogCol1)>= (10 * 1609.344)

Code is hidden, SHOW

As you can see doing stuff like this on SQL Server 2008 is fairly easy because of the geograpy data type

via: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-2008-proximity-search-with-th

and http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-zipcode-latitude-longitude-pr

C:\>cscript C:\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1
C:\>cd windows\microsoft.net\framework\v2.0.50727
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis.exe -i
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>NET STOP W3SVC /y & NET START W3SVC

ERRORS:
Microsoft OLE DB Provider for ODBC Drivers error ‘80004005’
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Run C:\Windows\SysWOW64\odbcad32.exe to setup DSN

Restore & Backup IIS 7

> %windir%\system32\inetsrv\appcmd.exe add backup “My Backup Name”

to restore that backup, run this command:

> %windir%\system32\inetsrv\appcmd.exe restore backup “My Backup Name”

to delete a backup, run this command:

> %windir%\system32\inetsrv\appcmd.exe delete backup “My Backup Name”

PHP Framework PHP4 PHP5 MVC Multiple DB’s ORM DB Objects Templates Caching Validation Ajax Auth Module Modules EDP
Akelos Visit Akelos download url talk Akelos at forum Akelos Support PHP4 Akelos Support PHP5 Akelos Support MVC Akelos Support Multiple DB Akelos Support ORM Akelos Support DB Objects Akelos Support Templates Akelos Support Caching Akelos Support Validation Akelos Support Ajax Akelos Support Auth Module Akelos Support Modules -
ash.MVC Visit ash.MVC download url  - ash.MVC Support PHP5 ash.MVC Support MVC - - ash.MVC Support DB Objects ash.MVC Support Templates - ash.MVC Support Validation - ash.MVC Support Auth Module ash.MVC Support Modules -
CakePHP Visit CakePHP download url talk CakePHP at forum CakePHP Support PHP4 CakePHP Support PHP5 CakePHP Support MVC CakePHP Support Multiple DB CakePHP Support ORM CakePHP Support DB Objects - CakePHP Support Caching CakePHP Support Validation CakePHP Support Ajax CakePHP Support Auth Module CakePHP Support Modules -
CodeIgniter Visit CodeIgniter download url talk CodeIgniter at forum CodeIgniter Support PHP4 CodeIgniter Support PHP5 CodeIgniter Support MVC CodeIgniter Support Multiple DB - CodeIgniter Support DB Objects CodeIgniter Support Templates CodeIgniter Support Caching CodeIgniter Support Validation - - - -
DIY Visit DIY download url  - DIY Support PHP5 DIY Support MVC - DIY Support ORM DIY Support DB Objects DIY Support Templates DIY Support Caching - DIY Support Ajax - - -
eZ Components Visit eZ Components download url  - eZ Components Support PHP5 - eZ Components Support Multiple DB - eZ Components Support DB Objects eZ Components Support Templates eZ Components Support Caching eZ Components Support Validation - - - -
Fusebox Visit Fusebox download url  Fusebox Support PHP4 Fusebox Support PHP5 Fusebox Support MVC Fusebox Support Multiple DB - - - Fusebox Support Caching - Fusebox Support Ajax - Fusebox Support Modules -
PHP on TRAX Visit PHP on TRAX download url  - PHP on TRAX Support PHP5 PHP on TRAX Support MVC PHP on TRAX Support Multiple DB PHP on TRAX Support ORM PHP on TRAX Support DB Objects - - PHP on TRAX Support Validation PHP on TRAX Support Ajax - PHP on TRAX Support Modules -
PHPDevShell Visit PHPDevShell download url  - PHPDevShell Support PHP5 - - - - PHPDevShell Support Templates - - PHPDevShell Support Ajax PHPDevShell Support Auth Module PHPDevShell Support Modules -
PhpOpenbiz Visit PhpOpenbiz download url  - PhpOpenbiz Support PHP5 PhpOpenbiz Support MVC PhpOpenbiz Support Multiple DB PhpOpenbiz Support ORM PhpOpenbiz Support DB Objects PhpOpenbiz Support Templates - PhpOpenbiz Support Validation PhpOpenbiz Support Ajax PhpOpenbiz Support Auth Module - -
Prado Visit Prado download url talk Prado at forum - Prado Support PHP5 Prado Support MVC Prado Support Multiple DB Prado Support ORM Prado Support DB Objects Prado Support Templates Prado Support Caching Prado Support Validation Prado Support Ajax Prado Support Auth Module Prado Support Modules Prado Support EDP
QPHP Visit QPHP download url  QPHP Support PHP4 QPHP Support PHP5 QPHP Support MVC QPHP Support Multiple DB - QPHP Support DB Objects QPHP Support Templates - QPHP Support Validation QPHP Support Ajax QPHP Support Auth Module QPHP Support Modules QPHP Support EDP
Seagull Visit Seagull download url  Seagull Support PHP4 Seagull Support PHP5 Seagull Support MVC Seagull Support Multiple DB Seagull Support ORM Seagull Support DB Objects Seagull Support Templates Seagull Support Caching Seagull Support Validation Seagull Support Ajax Seagull Support Auth Module Seagull Support Modules -
Symfony Visit Symfony download url talk Symfony at forum - Symfony Support PHP5 Symfony Support MVC Symfony Support Multiple DB Symfony Support ORM Symfony Support DB Objects - Symfony Support Caching Symfony Support Validation Symfony Support Ajax Symfony Support Auth Module Symfony Support Modules -
WACT Visit WACT download url talk WACT at forum WACT Support PHP4 WACT Support PHP5 WACT Support MVC WACT Support Multiple DB - WACT Support DB Objects WACT Support Templates - WACT Support Validation - - WACT Support Modules -
WASP Visit WASP download url  - WASP Support PHP5 WASP Support MVC - - WASP Support DB Objects WASP Support Templates - WASP Support Validation WASP Support Ajax WASP Support Auth Module WASP Support Modules -
Yii Visit Yii download url talk Yii at forum - Yii Support PHP5 Yii Support MVC Yii Support Multiple DB Yii Support ORM Yii Support DB Objects Yii Support Templates Yii Support Caching Yii Support Validation Yii Support Ajax Yii Support Auth Module Yii Support Modules Yii Support EDP
Zend Visit Zend download url talk Zend at forum - Zend Support PHP5 Zend Support MVC Zend Support Multiple DB Zend Support ORM Zend Support DB Objects - Zend Support Caching Zend Support Validation - Zend Support Auth Module Zend Support Modules -
ZooP Visit ZooP download url  ZooP Support PHP4 ZooP Support PHP5 ZooP Support MVC ZooP Support Multiple DB - ZooP Support DB Objects ZooP Support Templates ZooP Support Caching ZooP Support Validation ZooP Support Ajax ZooP Support Auth Module - -

 

via: http://www.phpframeworks.com