SQLGeography in SQL Server 2012 - Polygon must start on correct position?

I’ve been working with some simple spatial data lately. In one use-case, the user marks a polygon by using an iPad or iPhone in field and walks around storing points (coordinates), which after at least three points, forms an area (the first coordinates are also used as the last to close it). I’m using the builtin spatial funtionality in SQL Server 2012. According to SqlGeography.STIsValid and SqlGeography.IsValidDetailed, the surface was a valid polygon, but when using e.g SqlGeography.STContains and checking if a point was contained by the polygon, it said yes for one that wasn’t, and no for one that was. My first thought was that I had mixed up the values or mapped Longitude, Latitude wrong. But it worked correctly some times. Before coming to the error let’s set up a use-case

EDIT#1: It might actually be the case that it’s not the start point that is the issue, but the direction. If you draw it counter-clockwise it works. I’m no GIS guy and I probably miss a bunch of things here, but as a dev, I expect Microsoft to try and take care of this kind of stuff or at least make the API give a hint about it.

EDIT#2: I’ve written a followup on this post, explaining the issue and the “left hand rule”. Again feel free to educate me.

Use-case “Castle of Wertheim”

There’s a town in Germany, called Wertheim. In that town, there is a castle and I’ve used Bing Maps to draw a polygon around it.

The coordinates has been exported and made appropriate for use with SqlGeograpy.STPolyFromText, like this:

POLYGON((Longitude Latitude, ..., Longitude Latitude))

Now let’s just have a quick look at how the polygon will be treated in SQL Server 2012. Declare a geography variable representing the shape I drew on Bing maps.

declare @g geography
set @g = geography::STPolyFromText('POLYGON((9.520413279533372 49.75894717649063, 9.520086050033555 49.758562511155105, 9.519426226615892 49.75856770935565, 9.519155323505387 49.758881333091104, 9.51935380697249 49.75917069683788, 9.519538879394517 49.75930238276165, 9.519844651222215 49.75922614358621, 9.520131647586808 49.759229609005885, 9.5204722881317 49.759349165832695, 9.520630538463578 49.75934396771592, 9.52069222927092 49.759262530480356, 9.520689547061906 49.75921401461543, 9.520770013332353 49.759172429549714, 9.520866572856889 49.75913777530104, 9.520893394947038 49.75907713030626, 9.52084511518477 49.75901648523564, 9.520732462406144 49.75899222718616, 9.520528614521012 49.75899569262254, 9.520413279533372 49.75894717649063))', 4236)

If we just select it, Management Studio will render the surface for us:

select @g

By default it outputs this view:

Click on the “Spatial result” tab and you will see the rendered surface:

Something went wrong indeed. As it turns out, it seems that where you start drawing your polygon (your start position) is of great importance. So, for me. The user draw a valid polygon but he just started out on the wrong point.

Fixing it

Lets use some other functions. First: SqlGeography.ReorientObject and then: SqlGeography.STAsText. ReorientObject and in our case make it start on the correct point. By dumping it out as text we can easily take the first Longitude and Laitude pair and map them on Bing Maps. Note! You need to switch places, otherwise you will end up in Africa somewhere. Latitude should come first. As we can see the correct start point is the one furthest to the left (west).

select @g.ReorientObject().STAsText()

ouputs:

POLYGON ((9.5191553235053874 49.758881333091104, 9.5194262266158916 49.758567709355653, 9.5200860500335551 49.758562511155105, 9.520413279533372 49.758947176490629, 9.5205286145210124 49.758995692622541, 9.5207324624061442 49.758992227186162, 9.52084511518477 49.759016485235641, 9.5208933949470378 49.75907713030626, 9.5208665728568889 49.759137775301042, 9.5207700133323527 49.759172429549714, 9.520689547061906 49.759214014615431, 9.52069222927092 49.759262530480356, 9.5206305384635783 49.759343967715921, 9.5204722881317 49.759349165832695, 9.5201316475868083 49.759229609005885, 9.5198446512222148 49.759226143586211, 9.519538879394517 49.759302382761653, 9.51935380697249 49.759170696837877, 9.5191553235053874 49.758881333091104))

which gives us a first point with, Latitude, Longitude (note that places are switched):

49.758881333091104 9.5191553235053874

and on the map:

So ReorientObjects looks nifty. It kind of inverts the shape. From docs:

Returns a geography instance with interchanged interior regions and exterior regions

But this isn’t something we can use if the user did draw it correctly. Lucky us there’s another function we can use: SqlGeography.EnvelopeAngle, from docs:

For instances with angles greater than 90 degrees, 180 degrees will be returned.

So if we try:

select case when @g.EnvelopeAngle() > 90 then @g.ReorientObject() else @g end

we will now see it render correctly:

Fixing it in C#

I needed this to be fixed in C#, since the user-drawn polygons are stored using ADO.Net. I put together a small factory that will assist me with it:

public static class GeographyFactory
{
    public static SqlGeography CreatePolygon(Coordinates[] coordinates, int srid)
    {
        var list = coordinates.Distinct().ToList();

        var b = new SqlGeographyBuilder();
        b.SetSrid(srid);
        b.BeginGeography(OpenGisGeographyType.Polygon);
        b.BeginFigure(list[0].Latitude, list[0].Longitude);

        for (var i = 1; i < list.Count; i++)
            b.AddLine(list[i].Latitude, list[i].Longitude);

        b.AddLine(list[0].Latitude, list[0].Longitude);
        b.EndFigure();
        b.EndGeography();

        return b.ConstructedGeography.EnvelopeAngle() > 90 
            ? b.ConstructedGeography.ReorientObject() 
            : b.ConstructedGeography;
    }
}

In theory I guess you could re-order your coordinates instead and start with the one lying most to the left (west).

//Daniel