A helper function for ring-orientation in the SQL Server 2008 geography data type

Folks have always had trouble with the fact that ring orientation is required with spatial instances if you're using SQL Server 2008's geography data type, but not with the geometry data type. For an explanation of the need for this, reference Isaac Kunen's blog entry here and Ed Katibah's blog entry (link in next paragraph).

In Ed's blog entry, he provides a neat way to fix spatial instances that have the wrong ring orientation for geography by using the geometry type and calling a method that forces the required ring orientation for geography. You should read his entry from yesterday for background.

I thought this was such a cool mechanism that I coded up a SQLCLR UDF that encapsulates this behavior. It accepts almost any WKT and produces a "proper" geography type, regardless of the ring orientation in the WKT. The code is included with this post. The function is called GeographyFromAnyWKT. Well, almost any. There are certain edge conditions (such as a ring that actually would exceed a single hemisphere) that will produce and error, but at least you shouldn't have to worry about ring orientation. Thanks Ed!

As an aside, although this is a Visual Studio SQLCLR autodeploy database project, the function can't be autodeployed because it returns a SqlGeograhy type and this is not covered by the autodeployer code. So I've included a deploy script and a couple of test cases in the project. Although I define the UDF as RETURNS NULL ON NULL INPUT, I've also included (redundant) null checking in the function itself, just in case you want to change the function a bit. Enjoy!

GeographyValidator.zip (96.73 KB)

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.