SQL SERVER 2008 - AdventureWorks2008 - GeoSpatial Coding

  • Article

 

"Spatial" comes from the word "space," which of course represents locations.

We use spatial data to calculate locations of things, including where things are, where things intersect, or how many things lie in a bounded area.

Of the two data types, geometry supports a "flat earth" while geography supports a "round earth." The flat earth model is good for small areas, but large areas should use the round earth model for greater accuracy.

SQL Server queries incorporating spatial data look like any other query, with the exception that the spatial types have associated internal methods we can use (i.e.: "do I intersect with this other spatial object?)

Microsoft SQL Server 2008 delivers comprehensive spatial support that enables organizations to seamlessly consume, use, and extend location-based data through spatial-enabled applications,  ultimately helping end users make better decisions.

Build spatial capabilities into your applications by using the support for spatial data in SQL Server 2008.

Use the new GEOGRAPHY data type to store geodetic spatial data and perform operations on it.

Use the new GEOGRAPHY data type to store geodetic spatial data andperform operations on it.

Use the new GEOMETRY data type to store planar spatial data and performoperations on it.

Take advantage of new spatial indexes for high-performance queries.

Use the new spatial results tab to quickly and easily see spatial query results directly from within SQL Server Management Studio

Extend spatial data capabilities by building or integrating location-enabled applications through support for spatial standards and specifications.

image

Step 1: Installing and Preparation AdventureWorks2008

 

Be sure AdventureWorks has FileStream enabled.

Go to https://msdn.microsoft.com/en-us/library/cc645923.aspx

image

Verify that FileStream is enabled.

Go to advanced properties.

image

Get Adventure Works 2008

Navigate to codeplex.com and perform download for AdventureWorks2008.

https://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040

image

This zip took 3 minutes on my home network at 473kb / sec on average.

image

Open zip file

image

In case of installation issues, download the zip file and run instawdb.sql yourself. I ended up running this file because of issues. My friend Paul Keister said I should have checked the event log for a filestream error. That could have been it. But I got it to work.  Here it is:

image

image 

We now need to create a stored procedure.

Start SQL Server Management Studio.

Click on "New Query".

Paste the Code Below.

Start Visual Studio 2008.

Add A New ASP.NET Web Site.

 

image

Go to WebSite = www.codeplex.com. Download AdventureWorks2008.

 

I assume you have already installed AdventureWorks2008.

image

 

USE [AdventureWorks2008]GO/****** Object:  StoredProcedure [dbo].[GetStores]    Script Date: 10/24/2008 08:26:33 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

ALTER PROCEDURE [dbo].[GetStores]ASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;

    -- Insert statements for procedure here    DECLARE @region geography;    SET @region = geography::STGeomFromText('POLYGON((-80.0 50.0, -90.0 50.0, -90.0 25.0, -80.0 25.0, -80.0 50.0))', 4326);    SELECT a.[BusinessEntityID]          ,a.[Name]          ,c.[AddressLine1]          ,c.[City]          ,c.[SpatialLocation]      FROM [Sales].[Store] a      JOIN [Person].[BusinessEntityAddress] b ON a.[BusinessEntityID] = b.[BusinessEntityID]      JOIN [Person].[Address] c ON b.[AddressID] = c.[AddressID]      WHERE b.[AddressTypeID] = '3'      AND c.[SpatialLocation].STIntersects(@region) = 1  END