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

4. Next, let's have a look at how to query the stored data:

SELECT SalesRegionID,SalesRegionName, SalesRegionGeoLocation,Regio
nalLocationWKTxt from dbo.NewSalesRegion_Europe

5. By default, the native geographical data for Sales Europe and Sales Scotland

is not in a readable format and the same can be represented by using the
RegionalLocationWKTxt column to show well-known text (WKT) as a
readable text.

Next, let's look at GEOMETRY, we will assign a value to a column or variable using static
methods to parse the representation of data into spatial data type.

1. Use the following TSQL statement to use local variables in a batch:

Declare @geom-ex1 GEOMETRY
Declare @geom-ex2 GEOMETRY
PRINT @geomex1.ToString()
SET @geom-ex1 = geometry::STGeomFromText('LINESTRING (130 90, 30
160, 180 140)', 1)
SET @geom-ex2 = geometry::STGeomFromText
('POLYGON ((0 10, 120 0, 150 120, 0 120, 0 1))', 0)

2. Now, let's us create a demo table by inserting data and selecting inserted values as

follows:
USE AdventureWorks2011
GO;
CREATE TABLE geo_demo
(GeoID INT IDENTITY NOT NULL,
GeoName GEOMETRY)
GO;

--Insert data in GEO demo table
INSERT INTO #geom_demo (GeomCol)
VALUES ('LINESTRING (130 90, 30 160, 180 140)', 1),
('POLYGON ((0 10, 120 0, 150 120, 0 120, 0 1))', 0),
('POINT(10 10)')

--Select the inserted data
SELECT
GeoID,
GeoName.ToString() AS WKT,
GeoName.STLength() AS LENGTH,
GeoName.STArea() as Area
FROM geo_demo
GO