Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 Spatial Data Types

SQL Server 2008 Spatial Data Types

  • Comments 5
  • Likes

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?

Comments
  • Geography & Geometry has much significance in day to day life, within that as a data now you can

  • Geography & Geometry has much significance in day to day life, within that as a data now you can

  • Having implemented the underlying computational engine I can provide some inside information on the hemisphere restriction ("I am guessing that this is also something to do with the standards").  The restriction is there because the problem is surprisingly complex, and at release time we did not have a solution that was good enough to meet our high quality bar. I hope to have one by the next release, and then the restriction will be removed.

  • CREATE TABLE SpatialTable

       ( id int IDENTITY (1,1),

       GeomCol1 geometry,

       GeomCol2 AS GeomCol1.STAsText() );

    ---------------------------------------------------------

    Msg 2715, Level 16, State 7, Line 1

    Column, parameter, or variable #2: Cannot find data type geometry.

  • Androgein I take it you are using SQL Server 2008 or later for this?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment