I was recently helping someone with a project that let the user to draw shapes on the map and upload the Well Know Text for the shape into a database. To do this they were making use of the Shape Toolbox and the Well Known Text modules for Bing Maps. Using these two modules together with a web service was really easy to do. However, although the generated Well Known Text is valid as a SQL Geometry object, they may not always be a valid Geography object depending on how the user drew the shape. In an attempt to account for this we came across a unique difference in the spatial functionality between SQL Azure and SQL 2012. If we were to create a Geography object using a Well Known Text that is invalid, SQL 2012 would create the object but would throw an error asking use to use the MakeValid method if we tried to do any calculations against it. However in SQL Azure it throws an error when we try to create the Geography. The Geography class has the MakeValid class in both databases and in SQL 2012 we just need to use that to fix our Geography and be on our way. However, reading through the release notes for spatial functionality in SQL Azure we find this:
In this release of SQL Azure, invalid objects are not allowed in the Geography type. Consequently, the MakeValid() method, while present and operational for the Geography type, cannot do anything more than operate over an already valid object – effectively a no-op.
What this means is that even though the MakeValid method is available it is pretty much useless to us as we can’t create invalid geographies.
To test this out for your self, try running the following SQL script against both database. SQL 2012 will work and return a value that indicates that the geography is invalid. SQL Azure with throw an error message on the first line.
So what can we do about this?
What makes a Geography Valid?
Before we dive into a solution we need to first understand what makes a geography object valid. The following is the basic criteria that is required:
- No part of the shape should overlap with itself.
- For Polygon & MultiPolygon objects:
- Exterior rings must be ordered in a counter clockwise direction.
- Inner rings must be ordered in a clockwise direction.
- Rings must have the same start and end coordinates.
Solution 1 – SQL Spatial .NET Library
All the spatial functionality that is in SQL is available as a .NET library which you can use in your code. One solution to our problem is to make the Well Known Text valid in our web service before passing it to the database. This is fairly easy to do. For information on how to use the SQL Spatial .NET library in a web service take a look at this blog post I wrote. By doing this we will be using the same logic that is in SQL 2012 which allows us to create an invalid geography and then use the MakeValid method to fix it.
Solution 2 – Custom Stored Procedures
The first solution will work for a lot of situations, unfortunately the person I was helping had a Node.js backend and couldn’t make use of the .NET spatial library. So, I needed a different solution. I have written a lot of code in the past that helps to make Geographies valid in .NET in my Spatial Toolbox project, so this got me wondering how hard it would be to reuse this code logic in a Store Procedure. After a bit of work I ended up with 3 stored procedures.
I called the first stored procedure IsCCW. This method takes a Polygon ring or LineString from a Geometry object and checks to see if the coordinates are in a counter-clockwise direction. Here is the SQL script for this.
I called the second stored procedure CreatePolygonFromRing. This method creates a valid Polygon Geometry out of a ring. Here is the SQL script for this.
I called the third stored procedure MakeValidGeographyFromGeometry. This method takes a Geometry object and if it is a polygon uses the exterior ring to create a simple polygon, it then loops through each inner ring of the polygon and creates a simple polygon out of it and then uses it with the STDifference method to cut a hole in the main polygon. This fixes all the ring orientation issues. It then returns a valid Geography object or null if it is can’t be made valid. Here is the SQL script for this.
At this point we have all that we need to make our Geography valid. If we go back to the original example that worked in SQL 2012 but threw an error in SQL Azure we can updated as follows to make use of these stored procedures:
This now works in both databases.