Investigating the new Spatial Types in SQL Server 2008 - Part 2

bennie — Wed, 08 Oct 2008 19:32:51 GMT

Introduction

In the previous part of this series, we talked about the importance of spatial data in our everyday lives. We talked about vector and raster data, and we explained that the current version of SQL Server 2008 focuses on two-dimensional vector data. Next, we introduced the new geography and geometry data types in SQL server 2008, and we took a quick detour into some important object-oriented principles.

In this article, we start out by taking a look at the class hierarchies for both the geometry and the geography data types in SQL Server 2008. We will focus on the concrete classes in the class diagram which are:

Point.
LineString
Polygon

We will also take a look at the collection classes in the object hierarchy, which allow us to store multiple instances of each type.

Once we have a good grasp on the geometry and geography class hierarchy we will dive into the details of each concrete class. We will start our investigation by looking into the details of the Point and LineString class, and we will write a T-SQL script that highlights the most important features. In the process we will take a look at the three different data formats that can be used to represent spatial data:

The Well-Known-Binary (WKB) format
The Well-Known-Text (WKT) format
The Geography Markup Language (GML) data format.

All of these formats are published as a standard by the Open Geospatial Consortium (OGC), which is the leading standards body for geospatial and location based services.

The Geometry class hierarchy

Overview

A class diagram showing the Geometry class and all of its sub-classes is shown below (note that the Geography class diagram is basically identical, with the Geography class at the root of the tree):

The darker-blue shaded classes are abstract base classes, so they cannot be directly instantiated in the database engine. The light-blue shaded classes are the concrete classes that we can use directly in our T-SQL code:

Point
LineString
Polygon

Each of these single-instance classes has a collection equivalent that can contain multiple instances:

MultiPoint
MultiLineString
MultiPolygon

The GeomCollection class is an additional collection class that can contain a mixture of instances of any type (Points, LineStrings and Polygons).

Supported Data Formats

All of the classes in the geometry and the geography class hierarchy can be represented by three different formats:

The Well-Known Binary (WKB) format. This is a byte-stream (binary) representation for a geography instance. This format is the preferred serialization format for those applications that need to store geospatial information in a compact, self-contained format.
The Well-Known Text (WKT) format. This is a compact, easy to read representation. Because of its user-friendly format it is the most commonly used representation for ad-hoc geospatial queries.
The Geography Markup Language (GML) format. This is an XML-based representation, which is best suited for including geospatial information in an XML document. This format is very useful for those applications that need to exchange geospatial information by means of an XML Web service.

All of the above formats are published as standards by the OGC. In the code samples that follow, we will be showing multiple examples of each format.

The Point Class

Basic Representation

A Point is a zero-dimensional object which represents a single, exact location. It always contains an X and Y coordinate, and can optionally can contain a Z (elevation) value, and a M (measure) value. The measure value is an arbitrary floating point measurement value that you can associate with the Point instance.

The sample T-SQL script (GeometryPoint.sql)illustrates some of the capabilities of the Point class. Throughout all of our demos, we will be working with a very simple table called GeometryDemo, which allows us to associate a Geometry instance with an integer ID:

   1: CREATE TABLE GeometryDemo

   2: (

   3:     ID    INT NOT NULL,

   4:     Value GEOMETRY NOT NULL

   5: );

In the following code sample, we insert a Point instance, and then perform a select to retrieve the point instance in a number of formats:

   1: -- Insert a simple Point with an

   2: -- X and Y coordinate

   3: INSERT INTO GeometryDemo (ID, Value)

   4: VALUES    (1, 'POINT(50 65)');

5:

   6: -- Get the X and Y coordinates and

   7: -- the most relevant methods

   8: SELECT ID, Value.ToString() AS WKT,

   9:            Value.STX AS [X Coordinate],

  10:            Value.STY AS [Y Coordinate],

  11:            Value.STAsBinary() as [Binary Representation],

  12:            Value.STasText() as [Text Representation]

  13: FROM GeometryDemo

  14: WHERE ID = 1;

In lines 3 and 4, we use the WKT format to insert a Point with an X coordinate of 50 and a Y coordinate of 65. The WKT format for a Point simply requires you to to specify the X and Y coordinates. Note that you should NOT use a comma separator between the two, which might seem counterintuitive at first.

In lines 8 through 14 we select the instance that we just inserted. Since Value is a geometry class instance, we need to use the dot (".") notation to access properties and invoke methods on the object:

In line 8 we invoke the ToString() method. This method will return the Well-Known Text (WKT) representation of the instance.
In lines 9 and 10 we invoke the STX and STY properties to get the X and Y coordinates of our Point.
In line 11 we invoke the STAsBinary() method to get the binary representation of the Point.
Finally, in line 12 we invoke the STAsText() method, which will again return the WKT format of the Point.

One important note: although TSQL by itself is not case sensitive, the CLR methods and properties on the spatial data types are, so make sure that you spell the property and method names correctly! For example if you would spell "Value.STAsText()" as "Value.STasText()" you would get the following error message:

   1: Msg 6506, Level 16, State 10, Line 34

   2: Could not find method 'STasText' for type 'Microsoft.SqlServer.Types.SqlGeometry' in assembly 'Microsoft.SqlServer.Types'

The output of the select query is shown below:

From the above output, we see that the WKT representation for the Point is returned by both the ToString() and the STAsText() methods. We also see the binary WKB representation and the X and Y coordinates of the Point instance.

Using the Z and M coordinates

As we mentioned earlier, a Point can optionally contain a Z (Elevation) coordinate and a M (Measurement) value. Note that both the Z and M values should be floats. Below is a T-SQL example:

   1: -- Insert a Point with an X, Y, Z and

   2: -- M coordinate

   3: INSERT INTO GeometryDemo (ID, Value)

   4: VALUES (2, 'POINT(55 70 100.2 50)');

5:

   6: SELECT ID, Value.ToString() AS WKT,

   7:            Value.STX AS [X Coordinate],

   8:            Value.STY AS [Y Coordinate],

   9:            Value.Z AS [Elevation],

  10:            Value.M AS [Measure]

  11: FROM GeometryDemo

  12: WHERE ID = 2;

As you can see, we simply added the Z and M values in the WKT format. The output of the select query is shown below:

OGC-compliant methods and Microsoft Extensions

The attentive observer probably noticed that some method and properties have an ST prefix (such as STX, STY and STAsText()), while other properties and methods do NOT have this prefix (for example: Z, M and ToString()). All methods and properties that are prefixed with ST are OGC-compliant methods, while the other methods and properties are Microsoft extensions to the OGC standard.

In the documentation you will notice that the OGC standard methods and the Microsoft extensions are cleanly separated:

In the above diagram we also see sections for static methods (both OGC and Microsoft extensions). We will take a look at static methods in a later section of this post.

Geography Markup Language (GML)

The only format that we have not worked with so far is the Geographic Markup Language (GML), let's write a query to retrieve both points as GML:

   1: --    Select both Points as GML

   2: --  Note that we are NOT retrieving the Z and M

   3: --  coordinates of the second point

   4: --  because they are NOT OGC compliant!

   5: SELECT ID, Value.AsGml() as [GML]

   6: FROM GeometryDemo;

The AsGml() method can be used to retrieve a geometry instance in GML format.

The output of the query is shown below:

The standard XML namespace for GML will have the URN: http://www.opengis.net/gml.

There is also one important note that we can make from the above output: the Z and M coordinates that we used for the second point are NOT returned in the GML! That is because they are proprietary Microsoft extensions to the OGC standard.

Using the static Point methods

The methods and properties we have used so far were invoked on an instance of the Point class, so we used the "dot" (.) notation:

instanceName.Property

-or-

instanceName.MethodCall(... method arguments here ...)

Instance properties and methods are always tied to a particular object instance, in this case a Point instance. Besides instance methods, the spatial data types in SQL Server 2008 also use static methods and properties. Static methods and properties are tied to the class itself, and can thus be invoked without having an instance around. You use the "::" notation in combination with the class name to invoke a static method or property in T-SQL:

className::Property

-or-

className::MethodCall()

One frequent usage pattern for a static method is the Factory Pattern. The Factory Pattern returns a new instance of the class through a static method. This pattern is used in both geospatial types in SQL Server 2008. Below is a code example from the GeometryPoint.sql T-SQL script:

   1: /* =========================================================================

   2:    ======= Using the Static methods to create a Point Instance =============

   3:    ========================================================================= */

   4: DECLARE @Pnt1 Geometry, @Pnt2 Geometry, @Pnt3 Geometry, @Pnt4 Geometry, @Pnt5 Geometry

   5: DECLARE @xmlSnippet xml;

6:

   7: --    1. Create a Point from an X and Y coordinate and an SRID

   8: SET @Pnt1 = geometry::Point(30, 20, 0);

9:

  10: -- 2. Create a Point using the Parse method

  11: SET @Pnt2 = geometry::Parse('POINT( 90 23 56000 231)');

12:

  13: -- 3. Create a Point using the OGC STPointFromText method

  14: -- Notice the SRID at the end

  15: SET @Pnt3 = geometry::STPointFromText('POINT(20 25)', 0)

16:

  17: -- 4. Create a Point from GML

  18: set @xmlSnippet =

  19:     '<Point xmlns="http://www.opengis.net/gml"> <pos>34 23</pos> </Point>';

  20: SET @Pnt4 = geometry::GeomFromGml(@xmlSnippet, 0);

21:

  22: -- 5. Null Point

  23: SET @Pnt5 = geometry::[Null];

24:

  25: -- Now select all points

  26: SELECT @Pnt1.ToString() AS [From Arguments],

  27:        @Pnt2.ToString() AS [From Parse],

  28:        @Pnt3.ToString() as [From STPointFromText],

  29:        @Pnt4.ToString() AS [From Xml],

  30:        @Pnt5 AS [Null Point];

Following is a discussion of each static method:

In line 8, we use the static geometry::Point() method to create a Point instance by passing in the X, Y, Z and M coordinates.
In line 11, we use the geometry::Parse() method to create a Point instance from Well-Known Text (WKT).
In line 15, we use the geometry::STPointFromText() method to create a Point instance from Well-Known Text. The is the OGC equivalent of the geometry::Parse() method.
In line 18, we have an example of how to create a Point instance from a Geographic Markup Language (GML) snippet, using the static geometry::GeomFromGml() method.
Finally, in line 23 we create a NULL Point instance with the static geometry::[Null] property.

The output of the select statement is shown below:

As we can conclude from the above output, the expected Point instances were created by means of the static methods.

The Spatial Reference Identifier

In the previous code sample, you might have noticed the "0" after the @xmlSnippet parameter:

   1: set @xmlSnippet =

   2:     '<Point xmlns="http://www.opengis.net/gml"> <pos>34 23</pos> </Point>';

   3: SET @Pnt4 = geometry::GeomFromGml(@xmlSnippet, 0);

This argument is the SRID, or the Spatial Reference Identifier. The SRID corresponds to a spatial reference system based upon the specific ellipsoid used for either flat-earth or round-earth mapping. Different projection systems exist, some of you might be familiar with the Mercator Projection that was used to create the maps that were in most classrooms in recent times. A good discussion of map projections can be found at: http://maps.unomaha.edu/Peterson/gis/notes/MapProjCoord.html.

Each projection is uniquely defined by its own SRID. You can use any projection you want, but you need to be aware that when you are comparing spatial instances using any of the methods (such as STIntersection()) you need to make sure that all objects have the same SRID otherwise you will get a run-time error!

That concludes part 2 of this series. In part three, we take a look at the LineString and the Polygon class, and we will use some visualization tools such as GeoQuery, Spatial Viewer, and SQL Server Management Studio's very own "Spatial Tab" to get a visual representation of our spatial data!

Bennie's Weblog : SQL Server