Geospatial Data in SQL Server 2008
Introduction
The spatial data type in SQL server 2008 allow you to store geospatial data which deals with the location of a point, line polygon... in a three dimensional space.
SQL Server 2008 supports two spatial data types:
- Geometry : Stores data in projected planar surfaces
- Geography : Stores data in an ellipsoidal model
Spatial Data Types
- Point: Discrete location on the surface of the planet, represented by an x-y coordinate pair and may additionally have an elevation Z and a measure M.
- LineString: A LineString is a collection of points. However this differs from the Multipoint object, as the points are in sequence and the LineString object also represents the line segments connecting the points.
- MultiLineString: A MultiLineString is simply a collection of LineStrings.
- Polygon: A Polygon is a collection of points representing a two dimensional surface. A Polygon may consist of a exterior ring and a number of interior rings. For a Polygon object to be a valid instance the interior rings cannot cross one another.
- MultiPolygon: A MultiPolygon is a collection of Polygons.
- GeometryCollection: A GeometryCollection is a collection of geometry (or geography) objects.
Geospatial SQL functions
STRelate: The following example uses STRelate() to test two geometry instances for spatial disjoint using an explicit DE-9IM pattern.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(5 5)', 0);
SELECT @g.STRelate(@h, 'FF*FF****');
STDisjoint: The following example uses STDisjoint() to test two geometry instances for spatial disjoint.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STDisjoint(@h);
STIntersects: The following example uses STIntersects() to determine if two geometry instances intersect each other.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STIntersects(@h);
STTouches: The following example uses STTouches() to test two geometry instances to see if they touch.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STTouches(@h);
STCrosses: The following example uses STCrosses() to test two geometry instances to see if they cross.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0)', 0);
SET @h = geometry::STGeomFromText('LINESTRING(0 0, 2 2)', 0);
SELECT @g.STCrosses(@h);
STWithin: The following example uses STWithin() to test two geometry instances to see if the first instance is completely within the second instance.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STWithin(@h);
STContains: The following example uses STContains() to test two geometry instances to see if the first instance contains the second instance.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STContains(@h);
STOverlaps: The following example uses STOverlaps() to test two geometry instances for overlap.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STOverlaps(@h);
STBuffer: The following example creates a LineString geography instance. It then uses STBuffer() to return the region within 1 meter of the instance.
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SELECT @g.STBuffer(1).ToString();
STConvexHull: The following example uses STConvexHull() to find the convex hull of a non-convex Polygon geometry instance.
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 1 1, 2 2, 2 0, 0 0))', 0);
SELECT @g.STConvexHull().ToString();
STIntersection: The following example uses STIntersection() to compute the intersection of two polygons.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STIntersection(@h).ToString();
STUnion: The following example uses STUnion() to compute the union of two Polygon instances.
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STUnion(@h).ToString();
STGeomFromText: The following example uses STGeomeFromText() to create a geometry instance.
SET @g = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0);
SELECT @g.ToString();
STPointFromText: The following example uses STPointFromText() to create a geography instance.
SET @g = geography::STPointFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.ToString();
STLineFromText: The following example uses STLineFromText() to create a geometry instance.
SET @g = geometry::STLineFromText('LINESTRING (100 100, 200 200)', 0);
SELECT @g.ToString();
STPolyFromText: The following example uses STPolyFromText() to create a geometry instance.
SET @g = geometry::STPolyFromText('POLYGON ((5 5, 10 5, 10 10, 5 5))', 0);
SELECT @g.ToString();
References:
http://msdn.microsoft.com/en-us/library/bb933876.aspx