Spatial Data - Delivering Location based Intelligence

I have been on and off blogging recently due to my part time course at the Uni which has kept me busy.. By the way I did look through most of the new features on Katmai and actually did a small lunch time session at work on Katmai, one of the topics I did speak in details was spatial data support on Katmai.

SQL Server 2008 provides the geography (for geodetic spatial data) and the geometry(for planar spatial data) data types. Both are .NET CLR types, just like your Hierarchy data type which I will post about in another blog soon. The presence of the CLR in SQL Server provides an avenue for some really new and exciting features in SQL Server. When the CLR was introduced on the Server in Yukon I did think it is a bit too much to be present in a database server, but then now that some of the new features in Katmai have panned out as a result of that it does all seem to make sense now.

The geography data type provides a storage structure for spatial data which is ellipsoidal in nature, and is represented 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, basically any round earth data.

The geometry data type provides a storage structure for spatial data which is planar or in other words Euclidean in nature. This kind of data is commonly used for maps and interior floor plans where the curvature of the Earth does not need to be taken into account.

To create data that is of the type geometry or geography we could create instances using input in the format called Well Known Text (WKT) or Well Known Binary(WKB). Well WKB is the format specified by the Open Geospatial Consortium that permits the geometry data to be exchanged between the client and the SQL server. The geometry data type provides properties and methods that are aligned with the Open Geospatial Consortium (OGC) Simple Features Specification for SQL and enable you to perform operations on geometric data that produce industry-standard behavior.

SQL Server 2008 increases the maximum size for CLR types in the database from the 8000 bytes limit that was imposed in SQL Server 2005 to two gigabytes (2 GB), which makes it possible to store extremely complex spatial data elements, such as polygons, which are defined by a large number of points. 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. Performance of queries against spatial data is further enhanced by the inclusion of spatial index support in SQL Server 2008.

Both spatial data types in SQL Server 2008 provide a comprehensive set of instance and static methods that you can use to perform queries and operations on spatial data. If we explore these two data types we find that Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, GeometryCollection are all instances of spatial data types recognized by both geometry and geography data types.

To construct instances of a point, Multipoint etc. from WKT format Katmai provides functions as following
Point instance from WKT input use STPointFromText
MultiPoint instance from WKT input use STMPointFromText
LineString instance from WKT input use STLineFromText
MultiLineString instance from WKT input use STMLineFromText
Polygon instance from WKT input use STPolyFromText
MultiPolygon instance from WKT input use STMPolyFromText
GeometryCollection instance from WKT input STGeomCollFromText


To construct instances from WKB format Katmai provides the following functions

Point instance from WKB input use STPointFromWKB
MultiPoint instance from WKB input use STMPointFromWKB.
LineString instance from WKB input use STLineFromWKB.
MultiLineString instance from WKB input use STMLineFromWKB
Polygon instance from WKB input use STPolyFromWKB
MultiPolygon instance from WKB input use STMPolyFromWKB.
GeometryCollection instance from WKB input use STGeomCollFromWKB

There are also methods which allow developers to compare or determine the relationship between geometrical instances such as the ones listed below

STEquals determines if two instances comprise the same point set
STDisjoint determines if two instances are disjoint
STIntersects determines if two instances intersect.
STTouches determines if two instances touch.
STOverlaps determines if two instances overlap.
STCrosses determines if two instances cross .
STWithin determines if one instance is within another .
STContains determines if one instance contains another.
STRelate determines if two instances are spatially related
STDistance determines the shortest distance between points in two geometries

Equivalent set of functions and methods are available for Geographical types as well .

To Create and add Spatial Data

USE Adventureworks
GO

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL DROP TABLE dbo.SpatialTable;
GO

CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
GO

To create a Geography instance from WKT format

DECLARE @g geography;
SET @g = geography::STGeomFromText('LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326);
SELECT @g.ToString();

To create a Geometry instance from WKB format

DECLARE @g geometry;
SET @g = geometry::STGeomFromWKB(0x010200000003000000000000000000594000000000000059400000000000003440000000000080664000000000008066400000000000806640, 0);
SELECT @g.STAsText();

-- Point from WKB
SET @g = geometry::STPointFromWKB(0x010100000000000000000059400000000000005940, 0);
SELECT @g.STAsText();

To create a Geometry instance from WKT format

-- Creating an instance with WKT
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0);
SELECT @g.ToString();

-- Parse is also useful to convert data from WKT
SET @g = geometry::Parse('LINESTRING (100 100, 20 180, 180 180)');
SELECT @g.ToString();

-- Point instance from WKT
SET @g = geometry::STPointFromText('POINT (100 100)', 0);
SELECT @g.ToString();

Determining if the instances intersect

USE AdventureWorks
GO

DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;

SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;
SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();

Sample code

As the integration of geospatial information into applications becomes more prevalent, we will require database systems that can store and manipulate spatial data. With the introduction of the spatial types, SQL Server 2008 provides a data storage solution for spatial data, and enables organizations of any scale to integrate geospatial features into their applications and services.