Explain how to store and query Spatial Data?

Submitted by: Administrator
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();
Submitted by: Administrator

Read Online SQL server 2008 Job Interview Questions And Answers