Why I should have paid more attention in geometry class: Embedding the new SQL Server Spatial CLR types and functionality directly into your own .NET applications.
In SQL Server 2008 we introduced the ability to store spatial objects as columns within a database through the Geometry and Geography types as well as the ability to perform efficient query operations against those columns through the use of a new Spatial index.
These data types were implemented as CLR (.NET) types hosted by SQL Server and a great benefit of this approach is that enabled the SQL team to make these same .NET assemblies are available for download and distribution “outside of SQL Server” for use in any .NET application.
You can download the SQL CLR Types here.
Once you have downloaded and installed the SQL types on your development machine, you can make them available to your .NET applications by adding a reference to the assembly within Visual Studio your project.
Let’s try it with a simple “hello world” console application that will create a couple of simple geometric shapes and calculate some simple geometric operations like area and intersections.
From Visual Studio, select File->New Project and select the Console Application project type.
Once your new project is loaded, you will need to add a reference to the SQL Server types assembly. Right click on the References node in your Solution Explorer and Choose Add New Reference.
Find the Microsoft.SqlServer.Types .NET component and add it to your project.
Now that the project is set up to use the Spatial data types, let’s try out a simple set of spatial operations.
1: using Microsoft.SqlServer.Types;
2:
3: class Program
4: {
5: static void Main(string[] args)
6: {
7: // Build a geometry object from Well Known TextSqlGeometry
8: a = SqlGeometry.Parse("POLYGON ((0 0, 10 0, 10 10, 0 0))");
9:
10: // Calculate and display its area
11: System.Console.WriteLine("Area of A = " + a.STArea().Value.ToString());
12:
13: // Create a buffered version of the original
14: SqlGeometry b = a.STBuffer(1);
15:
16: // Calculate and display its area
17: System.Console.WriteLine("Area of B = " + b.STArea().Value.ToString());
18:
19: // Create the shape that represents the difference of the two shapes
20: SqlGeometry c = b.STDifference(a);
21:
22: // Show its area
23: System.Console.WriteLine("Area of B-A = " + c.STArea().Value.ToString());Console.ReadLine();
24: }
25: }
We start by creating a very simple shape, a triangle (line 8) by converting a text representation of the shape into a SqlGeometry object. The text representation used by the SQL Spatial type library is WKT (well known text).
Next we buffer the triangle (line 14) by one unit all around, which means “grow” the shape around its border by one unit. Buffering can be useful when used in conjunction with other operators to find out if shapes border each other or to create more complex shapes that represent shapes with holes.
Now we create a more complex shape, by geometrically “subtracting” the original triangle from the buffered triangle (line 20), resulting in a shape that represents the one unit border around the original shape.
We can now verify that these operations resulted in shapes that are in fact complements of each other by calculating the areas of the individual shapes and comparing the area of the complex border shape with the areas of the original triangle and its buffered version (lines 11, 17 and 23).
A question you may be asking is can one start from a non-textual representation of a shape? Can I build the shapes in code?
Yes you can in fact create shapes “by hand” using builder objects (SqlGeometryBuilder and SqlGeographyBuilder) exposed by both the SqlGeometry and the SqlGeography data types.
Here is a simple example that builds a triangle by hand:
1: class Program
2: {
3: static void Main(string[] args)
4: {
5: // Create instance of a geometry builder
6: SqlGeometryBuilder geoBuilder = new SqlGeometryBuilder();
7:
8: // Spatial objects have a reference ID / reference system
9: // which identifies the type of projection (or lack of)
10: // used, for example WGS84 - SRID = 4326
11: geoBuilder.SetSrid(4326);
12:
13: // Building a simple polygon
14: geoBuilder.BeginGeometry(OpenGisGeometryType.Polygon);
15:
16: // Start the shape at 0,0 and add each line segment for the
17: // triangle (0,0 - 0,10) - (0,10 - 10,10) - (10,10 - 0,0)
18: geoBuilder.BeginFigure(0, 0);
19: geoBuilder.AddLine(0, 10);
20: geoBuilder.AddLine(10, 10);
21: geoBuilder.AddLine(0, 0);
22: geoBuilder.EndFigure();
23:
24: // We're done, this polygon has one figure.
25: geoBuilder.EndGeometry();
26:
27: // retrieve the constructed geometry
28: SqlGeometry geo = geoBuilder.ConstructedGeometry;
29:
30: Console.WriteLine("The new shape's WKT:" + geo.ToString());
31: Console.WriteLine("and it's area: " + geo.STArea().Value.ToString());
32:
33: // Wait
34: Console.ReadLine();
35: }
You are probably thinking to yourself, that’s neat, it had been a long time since I had thought about geometry, but Olivier how do I use this in my day job?
Agreed, these were very simple examples intended to show you how to get started.
You now have at your disposal a set of industry standard operations as well as extension methods that allow for complex calculations and operations against complex geometric shapes and a similar set of operations for geographic shapes.
So while calculating the area of a triangle seems pretty simple, what if that shape was a state and you were asked to calculate the area of a state, excluding all water areas? Remember that Minnesota is the land of 10,000 lakes.
![minnesota-satellite-image-m[1]](https://geology.com/satellite/minnesota-satellite-image-m.jpg "minnesota-satellite-image-m[1]")
The spatial library can handle complex shapes made up of 100’s of 1000’s of vertices, including shapes that have “holes”.
What if you were collecting the locations for the current instances of the bird flu reported across different regions and wanted to quickly calculate an aggregate shape that encompasses those points for clustering analysis (ConvexHull)?
What if you wanted to efficiently compare the current location for trucks within your fleet against a known “good” path for a route to better track fuel utilization or compliance (STIntersects)?
These are some of the types of problems that the SQL Server Spatial types can assist with.
Can I extend the existing SqlGeometry and SqlGeography types?
The classes are sealed, but you can use the new .NET extension methods to make it look like you have extended the existing data types.
In my next blog posting I’ll show some examples of using this extension mechanism to provide integration with Bing Maps Web Services as well as provide ways to aggregate and disaggregate complex shapes.
So what can I do with this new knowledge?
With the basics out of the way there are a series of business problems that you could look to leverage the libraries and SQL Server for:
- Location based services like asset tracking and employee tracking. For example having mobile devices or laptops report their location through posts against a .NET web service or Windows Azure Web Service application and using the library to quickly calculate the proximity or relationships between assets and employees.
- Combine the SQL CLR types with other Microsoft services like Bing Maps and Mappoint web services to perform Geocoding and analysis within your .NETapplication.
- Analysis of real estate opportunities by combining in-house data, commercial data and census or government data.
- Building extensions and utilities to make working with spatial data easier and more efficient.
- Many of the spatial goodies and tools that have been made available on CodePlex were written using these builder APIs and the .NET types. You can download and try these add-ons from CodePlex here.
- Modeling and analysis of floor plans
- <Your App Here> – Let me know what unique applications you find for using the new SQL Server Spatial Types in your own application.
Have a great week,
Olivier