Implementing Spatial Data in SQL Server 2008

In order to store and perform operations with spatial data, SQL Server 2008 provides two new datatypes - geography and geometry.

• The geometry datatype is used for any spatial data that lies in a flat plane. This might be, for instance, data the describes positions on a map, a plan of a building, or a local survey. The geometry datatype can also be used to store abstract or non-geospatial geometrical data, such as the shape of a component in a manufacturing process. The location of any point in the geometry datatype is described using x and y (and, optionally, z) coordinate values.
• The geography datatype is specifically used to store spatial data that relates to positions on the Earth's surface, which must be described using coordinates of latitude and longitude. Operations on geography data account for the curvature of the Earth by using the ellipsoid model associated with the SRID of each item of data.

The following figure illustrates the location of the point POINT(40 30), using each datatype:

So, which datatype should you use? There are advantages and disadvantages associated with each type and, having chosen one type, you cannot easily convert to the other. There are a number of factors to consider:

• Do you have existing projected or geodetic data that you want to incorporate in your database?
• Do you require specific functionality that is only available using the methods of one type (e.g. STCrosses(), STContains(), STCentroid() are only available for the geometry datatype)
• Do you need your application to meet accepted industry standards (Simple Features for SQL v1.1.0)?
• How accurate do you need the results of any spatial operations on your data to be?
• How important is the speed of obtaining results?

In this chapter, I'll examine a number of different factors to take into consideration when choosing a design for a spatial database in SQL Server 2008.