Jason's Random Thoughts of Interest

In the previous parts of this series (Part 1, Part 2), I introduced the Geometry and Geography data types, the various subclasses (Point, LineString, Polygon, etc), and demonstrated a little bit of the Well-Known Text (WKT) syntax. These two posts were primarily informational in nature, and didn't touch SQL Server at all. Let's change that!

Instantiating the UDT

The Geometry and Geography data types are implemented as User Defined Types (UDT) 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, but the Geography type has the very same capabilities (the primary difference between the two types is how distance and area is calculated). Edit: After taking a hard look at Geography, I realized that a lot of methods that Geometry offers are not implemented. So, I was incorrect in my assertion that everything available in Geometry is also available in Geography.

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:

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:

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).

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:

Jason Follas is an Architect for Falafel Software and one of the leaders of the Northwest Ohio .NET User Group in Toledo, Ohio. For the past two decades, he has primarily used Microsoft technologies to design and build interesting solutions for his customers. Jason is also a frequent speaker at user groups and conferences, and is one of the organizers behind the Day of .NET in Ann Arbor and CodeMash.

The opinions expressed in this blog are not intended to represent those of his employer.