Spatial data in SQL Server 2008

Instantiating the types

The Geometry and Geography data types are implemented as System Defined Types written in .NET. They are automatically installed with the server, and are available for use by any SQL Server 2008 database. For this post, I will use the Geometry type to demonstrate capability (the primary difference between the two types is how distance and area is calculated).

Any variable, parameter, or table column can be declared as type Geometry. Notice that the type name is not case sensitive.

DECLARE @g Geometry

As mentioned before, Geometry by itself is abstract. In order to do something interesting, you need to instantiate the type as one of the concrete subclasses of the Geometry type. Adhering to the OGC standards, you can use the STGeomFromText() static method to parse data provided in valid WKT syntax, and then your variable will take on the characteristics of the defined subclass (i.e., it will be instantiated as one of the concrete types). Note that in SQL syntax, CLR method names are case sensitive, so stgeomfromtext() will not work.

Now, in C# and VB.NET, it is common to invoke static methods of a type using a dot notation, as in geometry.STGeomFromText(). However, this is not the case in T-SQL, because that particular syntax implies that you're calling a User Defined Function belonging to the "geometry" schema. Instead, when calling a static method belonging to a type, you separate the type name and the method name using two colons (::).

For example, to use STGeomFromText() to create a LineString, you would do the following:

DECLARE @g Geometry
SET @g = Geometry::STGeomFromText('LINESTRING(0 0, 10 10, 21 2)',
                                  0)

You might be wondering, "What's that weird zero at the end all about?" In Part 1, I touched on the concept of a Spatial Reference System that defines things such as the unit of measure and the dimensions of the world being represented, etc. The zero in this method call is the Spatial Reference ID (SRID) parameter, and it is required that the SRID being used is declared alongside any piece of spatial information.

SQL Server 2008 will not perform calculations on pieces of spatial information that belong to separate Spatial Reference Systems (because one system may use centimeters, and another may use miles, and SQL Server simply does not have the means to automatically convert units). For the Geometry type, it is common to just use zero for the SRID when all of your data is from the same Spatial Reference System (Geography uses 4326 as the default, to be explained later).

Note: A linebreak was inserted between the parameters in order to make it obvious to the eye. There is no requirement in T-SQL that requires the linebreak.

Since we were declaring a LineString specifically, we could have also used a static method that only accepts valid LineStrings as input:

DECLARE @g Geometry
SET @g = Geometry::STLineFromText('LINESTRING(0 0, 10 10, 21 2)',
                                  0)

If we tried to supply something that was not valid WKT for a LineString, like POINT(0 0),then a .NET FormatException would have been thrown:

Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24142: Expected LINESTRING at position 0. The input has POINT(0 0).
System.FormatException:
   at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeToken(String token)
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseLineStringTaggedText()
   at Microsoft.SqlServer.Types.OpenGisWktReader.ReadLineString()
   at Microsoft.SqlServer.Types.SqlGeometry.STLineFromText(SqlChars lineStringTaggedText, Int32 srid)

The entire list of OGC standard static methods on the Geometry type include: STGeomFromText, STPointFromText, STLineFromText, STPolyFromText, STMPointFromText, STMLineFromText, STMPolyFromText, STGeomCollFromText, STGeomFromWKB, STPointFromWKB, STLineFromWKB, STPolyFromWKB, STMPointFromWKB, STMLineFromWKB, STMPolyFromWKB, and STGeomCollFromWKB. Note: The "FromWKB" methods are the "Well-Known Binary" equivalents to the "FromText" methods, and accept a specially crafted array of bytes as the input. Using binary representations for initialization improves performance, but is much harder for humans to work with and comprehend.

There is one other trick to be aware of when initializing a spatial data type. By contract, a UDT in SQLCLR must support serialization to and from a string. That is, a UDT must implement a ToString() method and a Parse(string) method that are called implicitly when a conversion is required, but these two methods can also be explicitly invoked. It just so happens that the string format used by the Geometry type is WKT (actually, the Parse() method is identical to STGeomFromText() with an implicit SRID of zero).

All of the following are functionally equivalent:

DECLARE @g Geometry

SET @g = Geometry::STGeomFromText('LINESTRING(0 0, 10 10, 21 2)',
                                  0)
SET @g = Geometry::Parse('LINESTRING(0 0, 10 10, 21 2)')
SET @g = 'LINESTRING(0 0, 10 10, 21 2)'

Note: The third example implicitly invokes the Parse(string) method.

Working with a UDT Instance

Once you have created an instance, then you can use a dot notation to access instance properties and methods:

DECLARE @g Geometry
SET @g = Geometry::STLineFromText('LINESTRING(0 0, 10 10, 21 2)',
                                  0)

PRINT @g.STLength() -- Result: 27.7436

Up to this point, my example code has been declaring and instantiating spatial data as variables within a batch. But, columns in a table can also be declared as spatial, and queries can access instance properties and methods:

CREATE TABLE #demo
(
    ID    INT IDENTITY(1,1) NOT NULL,
    G     GEOMETRY
)

INSERT INTO #demo (G)
VALUES    ('LINESTRING(0 0, 10 10, 21 2)'),
          ('LINESTRING(1 1, 11 11, 22 3)'),
          ('POINT(5 5)')

SELECT ID, G.ToString() AS WKT, G.STLength() AS LENGTH
FROM #demo

DROP TABLE #demo

Results:

Instance methods are the exciting part of using the spatial data types in SQL Server 2008.