Describe how to store and query Spatial Data
Spatial data is stored by using Geometry and Geography data types that are introduced in SQL Server 2008.
Geometry data type is created as follows:CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO
The data into the geometry data column is persisted by using the following INSERT command
INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));
The data in the geometry data columns is queried by using the following DECLARE and SELECT statements:
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();
