Spatial Data Management in SQL Server 2008

Spatial Data Types

Since the Community Technology Preview (CTP) 5 which was released in November 2007, SQL Server 2008 supports two spatial data types: geometry and geography. Both data types are implemented as .NET Common Language Runtime (CLR) data types. The geometry data type is compliant with the Simple Features for SQL Specification, Version 1.1.0 according to the Open Geospatial Consortium (OGC) and supports planar or Euclidean (flat-earth) data. It is best used if you describe positions in coordinate systems which have the same scale in X- and Y-direction, such as the OSGB36 coordinate system mentioned before. The geography data type, on the other side, stores ellipsoidal (round-earth) data, such as latitude and longitude coordinates; it is thus a natural fit for the requirements of Virtual Earth.

The difference becomes immediately clear if we have a look at the map. In the example below we see the different effects of a calling the STBuffer-function on a geometry…

SELECT Geometry.STAsText() 
  FROM Test01 
  WHERE id=1 
UNION 
SELECT Geometry.STBuffer(0.0001).STAsText() 
  FROM Test01 
  WHERE id=1

…and a geography…

SELECT Geography.STAsText() 
  FROM Test01 
  WHERE id=1 
UNION 
SELECT Geography.STBuffer(20).STAsText() 
  FROM Test01 
  WHERE id=1

...data type. The first thing you notice is, that we have to use the same units as used in the spatial reference system for the geometry data type, i.e. we have to enter the size of the buffer in decimal degrees. That is something that doesn’t make sense to most users. On the other side the geography data type takes the size of the buffer in meters. Since the scale for WGS84 coordinate systems is different in X- and Y-directions and furthermore even different depending on the latitude, you see a distortion when using ellipsoidal data such as WGS84 with the geometry data type:

Coming back to the units of our data, we see that a result in degrees is most of the times not reasonable when it comes to measuring distances and areas. If we use the geometry data type to measure the area of a building like this:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((
    51.461408935417026 -0.9266281127929573,
    51.46091427889742 -0.9263437986373904, 
    51.46108473546569 -0.9255471825599749, 
    51.46157939013779 -0.9258341789245446,
    51.461408935417026 -0.9266281127929573))', 4326);
SELECT @g.STArea();

We get an unusable result of 4.420787637913E-07 “square degrees”. However, if we use the geography data type:

DECLARE @g geography;
SET @g = geography::STGeomFromText('POLYGON((
    51.461408935417026 -0.9266281127929573,
    51.46091427889742 -0.9263437986373904, 
    51.46108473546569 -0.9255471825599749, 
    51.46157939013779 -0.9258341789245446,
    51.461408935417026 -0.9266281127929573))', 4326);
SELECT @g.STArea();

e get a perfectly reasonable result of 3418.3 square meters.

The bottom line: It makes more sense, in most cases, to use the geography data type if you use Virtual Earth.

Please note: not all spatial functions are supported for both geography and geometry data types. For further reference have a look at the documentation.

Supported Geometries

SQL Server supports all geometries as defined in the OGC Simple Feature Specification:

On the other side Virtual Earth only supports Points, Lines and Polygons. Thus we need to split all multi-geometries into their components. Fortunately SQL Server 2008 supports us here with the necessary spatial-functions, e.g. if we have a MULTIPOLYGON we can retrieve the individual POLYGONs which are part of it:

SELECT GeomCol1.STAsText() FROM FeatureDemo WHERE ID=6;
SELECT GeomCol1.STGeometryN(1).STAsText() FROM FeatureDemo WHERE ID=6

Here is the result of these sample queries:

MULTIPOLYGON (((...)), (()))
POLYGON ((...))