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.
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.
SQL Server 2008 supports two different spatial data types: GEOMETRY and GEOGRAPHY.
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:
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.
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.
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.
A MultiLineString is simply a collection of LineStrings.
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.
A MultiPolygon is a collection of Polygons.
A GeometryCollection is a collection of geometry (or geography) objects.
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.
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 [] and Tillmann Eitelberg has published a SSIS shape file source under Ms-PL on CodePlex []. 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[].
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:
and yyy can be
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
Manage research, learning and skills at IT1me. Create an account using LinkedIn to manage and organize your IT knowledge. IT1me works like a shopping cart for information -- helping you to save, discuss and share.