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.

Wertheim, Castle, polygon

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:

ManagementStudio01

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

ManagementStudio02

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:

Wertheim, Castle, polygon-Correct start

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:

ManagementStudio03

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

Category:
ADO.NET, Development, SQL-Server
Tags:

Join the conversation! 6 Comments

  1. [...] is a follow up on my post that I wrote this weekend: “SQLGeography in SQL Server 2012 – Polygon must start on correct position?“, you probably want to go and skim it through and then come back. But basically after my [...]

    Reply
  2. Hello. Thanks for sharing your founding. But the problem you mentioned has got noting to do with the start point of the polygon. The actual issue is the clock wise or counter clock wise status of the polygon.

    Reply
    • Hi,

      Yes you are right and that is in the post as well. See the red edits with links to posts where I explain the left hand rule. I hope I got it right in that post.

      Cheers,

      //Daniel

      Reply
  3. after 20 mins of google search i find the DLL for SqlGeography Class, but i cant find the reference for Coordinates Class.

    Could you tell me how reference it or paste the code of the project.

    Reply
  4. forget my last question. i checked your github THANKS! :D

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: