MCTS 70-433 Geometry Data Type

Getting Started with the geometry Data Type

The planar spatial data type, geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system.

  Registering the geometry Type

The geometry type is predefined and available in each database. You can create table columns of type geometry and operate on geometry data in the same manner as you would use other CLR types.

  Examples

The following two examples show how to add and query geometry data. The first example creates a table with an identity column and a geometry column GeomCol1. A third column renders the geometry column into its Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation, and uses the STAsText() method. Two rows are then inserted: one row contains a LineString instance of geometry, and one row contains a Polygon instance.

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL 

    DROP TABLE dbo.SpatialTable;

GO

CREATE TABLE SpatialTable 

    ( id int IDENTITY (1,1),

    GeomCol1 geometry, 

    GeomCol2 AS GeomCol1.STAsText() );

GO

INSERT INTO SpatialTable (GeomCol1)

VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

INSERT INTO SpatialTable (GeomCol1)

VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));

GO

The second example uses the STIntersection() method to return the points where the two previously inserted geometry instances intersect.

DECLARE @geom1 geometry;

DECLARE @geom2 geometry;

DECLARE @result geometry;

SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;

SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;

SELECT @result = @geom1.STIntersection(@geom2);

SELECT @result.STAsText();

http://msdn.microsoft.com/en-us/library/bb895270.aspx