SQL Server 2008 Spatial Data Types

  • Article

Continuing my series of notes following the TechNet event last Thursday evening,I wanted to explain that in SQL Server 2008 there are two new data types for storing spatial information; geometry and geography.  Unfortunately on Thursday I didn't have the final release of CTP5 so my demo didn't have the geography data type and I used latitude and longitude coordinates in a geometry data type with a Spatial Reference ID of 4326 to indicate that they were in fact geographical data.   

To explain to those that weren't there...

The Geometry data type supports a flat 2D surface with XY coordinates for points.  Points can be on lines, on line string and mark the edges of polygons.  There are then methods like STintersects , STarea, STDistance, STTouch which work as you'd expect; the naming convention conforming to standards from the Open Geospatial Consortium (OGC).

The Geography data type uses the same methods but the data type reflects the fact the we live on a curved 2D surface popularly known as the earth.  The st* functions are used against both new data types but the answers will be different as a result of the curvature.

One of the odd things about these spatial data types is the need for the aforementioned Spatial Reference IDs (SRID). Both geometry and geography data types have two parts, the coordinates of the object and the SRID indicated in orange below:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT (3 4)', 0);

DECLARE @g geography;
SET @g = geography::STGeomFromText('POINT (3 4)', 4326);

The numbering convention for each type is set by the European Petroleum Survey Group (EPSG) standard. This standard dictates that the SRID of any geometry data is 0 and so this is the default for the geometry data type in SQL server. For Geography the default of SRID is 4326 to indicate that the data conforms to the WGS 84 standard for curved earth data. 

Note that if the SRIDs of two pieces of spatial data don't match then the various spatial methods in SQL server 2008 will return null.

One puzzling thing to leave you with on spatial data is hemispheres:

  • If you try and stuff a polygon into SQL server 2008 that is larger than a hemisphere you'll get and ArgumentException error.
  • If the result of one of the methods returns a result that is larger than a hemisphere then the method will return NULL.

I am guessing that this is also something to do with the standards?

Technorati Tags: SQL Sevrer 2008,SQL,Spatial,OGC,Open Geospatial Consortium