Introduction

This article is the fifth in a series. There are links at the bottom of the article to the other parts.

In the last article we examined the Filestream data type. This time we look at SQL Server 2008 spatial data types, which provide a comprehensive, high-performance, and extensible data storage solution for spatial data, and enable organizations of any scale to integrate geospatial features into their applications and services.

Background– Why it all needed

As the technology advancing, the development of location-aware application is becoming popular and these applications are almost being used by all of us these days. For example, a customer wants to know details about a hospital/restaurant in a near vicinity of his address, the distance between given two places, events happening in the nearby area or you want to do a visual analysis on location data etc. This integration of geospatial information into applications becomes more prevalent, application developers will increasingly require database systems that can store and manipulate spatial data.

With the introduction of the geography and geometry data types, SQL Server 2008 provides a comprehensive, high-performance, and extensible data storage solution for spatial data, and enables organizations of any scale to integrate geospatial features into their applications and services.

Spatial Data Type – taking its place

SQL Server 2008 provides support for geographical data through the inclusion of new spatial data types, which you can use to store and manipulate location-based information. These native data types come in the form of two new data types: GEOGRAPHY and GEOMETRY. These two new data types support the two primary areas of spatial model/data: geodetic model and planar model. Geodetic model/data is sometimes called round earth because it assumes a roughly spherical model of the world using industry standard ellipsoid such as WGS84, the projection used by Global Position System (GPS) applications.

Geodetic Model – Image source www.microsoft.com

Planar model assumes a flat projection and is therefore sometimes called flat earth. Data is stored as points, lines, and polygons on a flat surface as shown in the image below.

Planar Model – Image source www.microsoft.com

The new spatial capabilities also include importing geographic data in a variety of industry standard formats and the features conform to the Open Geospatial Consortium (OGC) standards. In other words, the OGC defines different ways to represent geospatial information and SQL Server 2008 supports three such formats: Well-Known Text (WKT), Well-Known Binary (WKB), and Geography Markup Language (GML).

SQL Server 2008 provides the geography data type for geodetic spatial data, and the geometry data type for planar spatial data. Both are implemented as SQLCLR UDT, and can be used to store different kinds of geographical elements such as points, lines, and polygons. Both data types provide properties and methods that we can use to perform spatial operations such as calculating distances between locations and finding geographical features that intersect one another (such as a river that flows through a town.)

• GEOMETRY: - The geometry data type provides a storage structure for spatial data that is defined by coordinates on an arbitrary plane. This kind of data is commonly used in regional mapping systems, such as the state plane system defined by the United States government, or for maps and interior floor plans where the curvature of the Earth does not need to be taken into account. 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.
• GEOGRAPHY: - The geography data type uses the similar methods but the data type reflects the fact that we live on a curved 2D surface popularly known as the earth. It provides a storage structure for spatial data that is defined by latitude and longitude coordinates. Typical uses of this kind of data include defining roads, buildings, or geographical features as vector data that can be overlaid onto a raster-based map that takes into account the curvature of the Earth, or for calculating true great circle distances and trajectories for air transport where the distortion inherent in a planar model would cause unacceptable levels of inaccuracy.

By storing spatial data in relational tables, SQL Server 2008 makes it possible to combine spatial data with any other kind of business data; this removes the need to maintain a separate, dedicated spatial data store and enables high performance queries that do not need to combine data from multiple external sources.

Example

In this example first we will create a table to store spatial data into a column of GEOMETRY data type and insert some records, for example to hold point, linestrings, polygon.

CREATE TABLE SpatialDemo
(
   DataID           INT IDENTITY(1,1)NOT NULL,
    --Using GEOMETRY data type similar to any other system defined data type
    SpatialData     GEOMETRY
 )
 GO
 INSERT INTO SpatialDemo (SpatialData)
 VALUES
--insert a point where X = 5 and Y = 10
 ('POINT(5 10)'),
--insert a line start from X = 0 and Y = 0
--to X = 5 and Y = 5
 ('LINESTRING(0 0, 5 5)'),
--insert a line start from X = 0 and Y = 0 to X = 5 and Y = 5 and then to X = 7 and Y = 3
 ('LINESTRING(0 0, 5 5, 7 3)'),
--insert a polygon with triangular shape
 ('POLYGON((0 0, 8 5, 8 5, 10 0, 0 0))')
GO
--Note

--A space is used to separate the coordinates of a point. A comma is used to separate points within a more complex type. 
--Parentheses are used to group points into a single shape.

Now we select the inserted records using SELECT statement. Here you will notice a cool new tab “Spatial Results” appears to show spatial data graphically, you can select to show the graph with grid as well as you can zoom-in and zoom-out.

SELECT DataID, SpatialData FROM SpatialDemo
GO

As I discussed before spatial data types have been implemented as SQLCLR UDT, and hence it utilizes the capabilities of object oriented programming. What I mean here is, like any object oriented programming language, it has static methods as well instance methods to work with it. For example, STGeomFromText is a static method which is used to return a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation augmented with any Z (elevation) and M (measure) values carried by the instance whereas <instance>.STbuffer is an instance method used to return a geography object that represents the union of all points whose distance from a geography instance is less than or equal to a specified value. Apart from these two methods there are couple of more static and instance methods whose details you can find in BOL or MSDN.

--Example of using Instance methods
SELECT DataID, SpatialData.ToString()AS WKT, SpatialData.STLength()AS LENGTH,
SpatialData.STBuffer(0.1)AS SpatialData
FROM SpatialDemo
GO

 --Example of using Static methods
 SELECT geometry::STGeomFromText('POLYGON ((300 0, 150 0, 100 100, 300 150, 300 0))', 0)
 GO
 SELECT geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
 GO
 --Example of using variable of spatial data type
 DECLARE @Line GEOMETRY
 SET @Line = 'LINESTRING (0 0, 5 5, 7 3)'
 SELECT @Line.STBuffer(8)
GO

You might me wondering what the second parameter is being passed to static method as zero. It is a Spatial Reference Identifier (SRID). The SRID corresponds to a spatial reference system (defines the unit of measure and the dimensions of the world being represented) based on the specific ellipsoid used for either flat-earth mapping or round-earth mapping. A spatial column can contain objects with different SRIDs. However, only spatial instances with the same SRID can be used when performing operations with SQL Server spatial data methods on your data. More details on SRID can be found here.

Performance of queries against spatial data is further enhanced by the inclusion of spatial index support in SQL Server 2008. You can index spatial data with an adaptive multi-level grid index that is integrated into the SQL Server database engine. The SQL Server query optimizer makes cost-based decisions on which indexes to use for a given query, and because spatial indexes are an integral part of the database engine, SQL Server can make cost-based decisions about whether or not to use a particular spatial index, just like any other index.

One interesting thing to note here is, the new GEOMETRY and GEOGRAPHY data types have been introduced to SQL Server 2008 via a SQLCLR UDT (SQL Common Language Runtime User Defined Type), or in other words, these data type have been coded as a C# class using the .NET Framework though it behaves and works in similar way as any other system defined type, you don’t need to install it separately or set the CLR Enabled configuration to TRUE before using it.

References

SQL Server Books Online - http://msdn.microsoft.com/en-us/library/bb933790.aspx

Conclusion

To develop location-aware application, you need to have data storage solution for spatial data. SQL Server 2008 provides support for geographical data through the inclusion of new spatial data types, which you can use to store and manipulate location-based information. These native data types come in the form of two new data types viz. GEOGRAPHY data type for geodetic spatial data, and the GEOMETRY data type for planar spatial data.

This article is the fourth in a series. The other articles in the series are:

• SQL Server 2008 T-SQL Enhancements Part - I (Intellisense)
• SQL Server 2008 T-SQL Enhancements Part - II (UDTs and TVPs)
• SQL Server 2008 T-SQL Enhancements Part - III (HierarchyID and Large UDTs)
• SQL Server 2008 T-SQL Enhancements Part - IV (Filestream)
• SQL Server 2008 T-SQL Enhancements Part - V (Spatial)