Improving Availability and enhancing Programmability - Microsoft SQL Server 2008 R2 Administration - page 252

For more on OGC and the WKT/WKB/GML XML transport formats, review
http://www.opengeospatial.org/standards/sfa .

Getting ready

Every edition of SQL Server 2008 R2 supports the spatial data services, and the only
exception for SQL Express edition is, there is no graphical interface to view the results.
Ensure that the database used in this recipe has a compatibility level of 100 or 105 to
support the SPATIAL features.

The process is simple where we create a new table, insert the geo-data and select the
inserted data using the relational TSQL process.

How to do it...

The recipe steps are performed in a two-fold method, where both of the geography and
geometry data types are used. The following steps are required to design spatial data storage
methods using SQL Server 2008 R2.

1. Let us start with GEOGRAPHY, by using the following TSQL statement to create

a table:
USE AdventureWorks2008R2
GO
CREATE TABLE dbo.NewSalesRegion_Europe
(SalesRegionID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
SalesRegionName varchar(50) NOT NULL,
SalesRegionGeoLocation Geography NOT NULL,
RegionalLocationWKTxt AS SalesRegionGeoLocation.STAsText())
GO

2. The data for RegionalLocationWKTxt (Well-known text) is a calculated column

based on the SalesRegionGeoLocation column. Insert a row based on the
Europe Sales geographical area:
INSERT into dbo.NewSalesRegion_Europe (SalesRegionName,
SalesRegionGeoLocation) VALUES ('SalesEuorpe', geography::Regio
nalLocationWKTxt('POINT(-3.147583007812494 55.86298231197633)',
4326))

3. Insert a row based on the Scotland Sales geographical area:

INSERT into dbo.NewSalesRegion_Europe (SalesRegionName,
SalesRegionGeoLocation) VALUES('SalesScotland', geography::Regi
onalLocationWKTxt('POINT(-2.98212330008122324 51.8233578776760)',
3903))