Microsoft SQL Server/Geospatial Data

Introduction

Microsoft's 2008 release of SQL Server finally delivers Geospatial support to the SQL Server product suite.

This allows the storage of spatial data in SQL tables (in the form of points, lines and polygons) and a set of functions to allow the manipulation of this data. Also included are new spatial indexes to support the execution of these functions.

Example Database

This book will use an example database for the majority of its examples. The code for creating this can be found in Appendix A and should be run, as per the instructions there, in order to be able use the example code throughout this book. What follows here is a brief description of that database and the data contained within. If you wish, then you can skip this section and get straight into the interesting stuff, working out what the database is doing as you go.

Geospatial Data Types

SQL Server 2008 supports two different spatial data types: GEOMETRY and GEOGRAPHY.

• GEOMETRY - This data type stores data in projected planar surfaces.
• GEOGRAPHY - This data type stores data in an ellipsoidal model.

The Geometry Classes define a hierarchy as follows:

From the diagram we can see that there are seven types of instantiable spatial data types (in orange). Namely:

Point

A point is an object representing a single location. It always has an X and Y co-ordinate and may additionally have an elevation Z and a measure M.

MultiPoint

A MultiPoint object is a collection of points. It differs from a LineString and a Polygon as there is no implied connections between the points in the collection. Because of this the boundary of a MultiPoint object is empty.

LineString

A LineString is again a collection of points. However this differs from the Multipoint object, as the points are in sequence and the LineString object also represents the line segments connecting the points.

MultiLineString

A MultiLineString is simply a collection of LineStrings.

Polygon

A Polygon is a collection of points representing a two dimensional surface. A Polygon may consist of a exterior ring and a number of interior rings. For a Polygon object to be a valid instance the interior rings cannot cross one another.

MultiPolygon

A MultiPolygon is a collection of Polygons.

GeometryCollection

A GeometryCollection is a collection of geometry (or geography) objects.

Visually

• 
  Point
• 
  MultiPoint
• 
  LineString
• 
  MultiLineString
• 
  Polygon
• 
  MultiPolygon
• 
  GeometryCollection

Importing GeoSpatial Data into SQL Server

Data can be imported directly into SQL server from the following formats: WKT, WKB and GML. For more detail of these formats please see the appendices.

So I can't import my data from shape files?

Currently you cannot do this directly, whether this will change in future versions remains to be seen. If you have your data in shape files, or other formats then you will need to find a way to convert it. There are a number of Microsoft Spatial partners who offer tools which will allow you to do this, but obviously this will involve another license fee. Free versions I am sure will begin to appear as SQL Server 2008 releases. Currently Morten Nielsen has such a free tool posted on his blog here [[1]] and Tillmann Eitelberg has published a SSIS shape file source under Ms-PL on CodePlex [[2]]. AutoConViz (by Sugam Sharma, U Sunday Tim and Shashi Gadia) also offers the GUI based spatial format conversion (shape file format to GML) as one of its core functions[[3]].

Import Functions

Importing spatial data into SQL Server 2008 is done via means of the STxxxfromyyy set of functions, where xxx can be one of the following:

• Geom - For importing any spatial data type.
• Point - For importing point data.
• Line - For importing line data.
• Poly - For importing polygon data.

• GeomColl - For importing a Geometry Collection.
• MPoint - For importing multi point data.
• MLine - For importing multi line data.
• MPoly - For importing multi poly data.

and yyy can be

• Text - For importing data in WKT format.
• WKB - For importing data in WKB format.
• Gml - For importing data in GML format. (Note the case of Gml is important and this function lacks the leading ST e.g. GeomfromGml(.....))

Example

CREATE TABLE Districts 
( DistrictId int IDENTITY (1,1),
DistrictName nvarchar(20),
DistrictGeo geometry);
GO

CREATE TABLE Streets 
( StreetId int IDENTITY (1,1),
StreetName nvarchar(20),
StreetGeo geometry);
GO

INSERT INTO Districts (DistrictName, DistrictGeo)
VALUES ('Downtown',
geometry::STGeomFromText
('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));

INSERT INTO Districts (DistrictName, DistrictGeo)
VALUES ('Green Park',
geometry::STGeomFromText
('POLYGON ((300 0, 150 0, 150 150, 300 150, 300 0))', 0));

INSERT INTO Districts (DistrictName, DistrictGeo)
VALUES ('Harborside',
geometry::STGeomFromText
('POLYGON ((150 0, 300 0, 300 300, 150 300, 150 0))', 0));

INSERT INTO Streets (StreetName, StreetGeo)
VALUES ('First Avenue',
geometry::STGeomFromText
('LINESTRING (100 100, 20 180, 180 180)', 0))
GO

INSERT INTO Streets (StreetName, StreetGeo)
VALUES ('Mercator Street', 
geometry::STGeomFromText
('LINESTRING (300 300, 300 150, 50 51)', 0))
GO

Geospatial SQL functions

• STRelate
• STDisjoint
• STIntersects
• STTouches
• STCrosses
• STWithin
• STContains
• STOverlaps
• STBuffer
• STConvexHull
• STIntersection
• STUnion
• STGeomFromText
• STPointFromText
• STLineFromText
• STPolyFromText
• STGeomFromWKB
• STPointFromWKB
• STLineFromWKB
• STPolyFromWKB

Appendices

A. Example Database Code

B. WKT (Well Known Text) format definition

C. GML (Geographic Markup Language) format definition

References

• Directions Magazine's interview with Ed Katibah [4]
• Isaac Kunen's Blog [5]
• Microsoft Spatial Forum [6]
• Microsoft SQL Server 2008 Spatial Data Overview [7]
• Microsoft SQL Server 2008 Spatial Data Datasheet [8]
• Microsoft SQL Server 2008 Spatial Data White Paper [9]
• Microsoft Books Online [10]
• Wikipedia [11]
• AutoConViz for GUI based spatial format conversion online[12]