SQL Server 2008 – Spatial data (Geometry, Geography models / data types)

The world has more than one dimension. Locations on a map have at least 2 dimensions that define where they are. Many applications need to be able to allow users to filter data based on location. Databases historically provide indexes that enable fast retrieval of data in a single linear dimension, either via direct lookups or through range scans. Filtering on two dimensions – x and y, or longitude and latitude – works but is generally limited to squares and generally doesn’t perform well with large data sets, as the indexes are used sequentially rather than in parallel.

The spatial data type support has been added to SQL Server 2008 allows us to model two types of spatial data: Geometry (that is, describing a planar (flat) system such as a building floor plan), and Geography (that is, describing an elliptical system such as the Earth). The difference is that the Geometry type is for use with flat surfaces and the Geography type is for modeling an elliptical surface, such as the Earth. We will generally focus on the Geometry type and specify when the Geography methods differ.

Geometry and Geography both deal with up to two dimensions. Whilst the types support holding information about two additional dimensions (z and m) these are not used in any calculations, they just provide additional information about the spatial instance which could be used by the user.

Spatial data types are implemented using the CLR functionality in SQL Server. This approach hides the complexity of spatial calculations by providing a couple of types on which there are methods that perform the calculations.

Spatial data is very powerful and can be used in many scenarios, and not only in location-aware services. One of the obstacles to understand spatial data is working in more than one dimension