SQL Spatial polygon inside out

Question

I am allowing users to draw a polygon in Silverlight by clicking to draw. Then I loop through the points, convert them to longitude and latitude and then save to SQL (in a geography column).

The problem is that because of the world being round and all that, it only works if the user draws clockwise. Otherwise it tries to make the polygon right round the world and fails.

So how do I do this correctly? Do I have to work out which way they are drawing, and if so how?

Answer 1

You can check, if the result of the EnvelopeAngle() method for the geography was 180, then use the ReorientObject() function to correct it.

Here is the sample:

--A CW polygon
DECLARE @G3 GEOGRAPHY = 'POLYGON ((45 45, 44 45, 44 46, 45 46, 45 45))';    
SELECT @G3.EnvelopeAngle();                --180
SELECT @G3.ReorientObject().STAsText();    --POLYGON ((44 46, 44 45, 45 45, 45 46, 44 46))

EDIT as stated in the comments you can may correct current geometries, using a simple update command (in the case you are sure they are not correct):

UPDATE foo_table SET bar_column = bar_column.ReorientObject() 
    WHERE bar_column.EnvelopeAngle() > 90

Answer 2

If you are tied to RTM version of SqlServer 2008 you can always use sqlspatial tools from codeplex that is freely distributable and from that library just use makevalid method.

If you have time to play with CTP1 of SqlServer Denali you can just pickup new spatial types that can accept objects larger than a hemisphere and that have ReorientObject method to - Reorient Object if needed :)

Answer 3

I asked a similar question recently at the GIS StackExchange. I believe I have found a SQL-only solution, which is reproduced below:

Eventually found the answer at Spatial Ed's Blog.

SQL demonstrating the transform:

DECLARE @geom GEOMETRY = 'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))';
DECLARE @geog GEOGRAPHY = @geom.MakeValid().STUnion(@geom.STStartPoint()).STAsText()

And excerpt from Ed's post:

The key to this behavior is the the STUnion() method. Since this is an OGC-based method, working on the entire geometry for a given feature, it forces polygons into the orientation required for the method - which just happens to be the one used for the Geography type [...]. This method illustrated is quite efficient, keeping overhead small [...].

Answer 4

That is a common concept within geospatial geography data types, a polygon is defined by a number of vertices and the edges between those vertices. However, you have to be able to distinguish between what is inside and outside of the polygon. This is done by the system assuming that one side of the edge will always be defining the inside (Different standards use left side or right side)

In one direction you have drawn a small circle, in the other direction you have drawn a sphere that encompasses the entire world, except for a small circle. The latter would tend to break geographic limits and raise an exception.

If you consider trying to draw a doughnut, you have 2 polygons and have to have the points in a clockwise / anti-clockwise pattern, to define the 'hole' within the centre.

Answer 5

Left hand rule governs this... as you 'walk' the perimeter of your polygon, your left hand must always be inside... so things should 'appear' to be digitized counter-clockwise. this hold true for donuts and polys with holes as well.

if you keep your left hand 'inside' the polygon area you are interested in, they will be digitized in a clockwise fashion.

A simple way to determine which one is correct is to always take the one with the SMALLER area... in just about any workflow I can thing of, there are no polygons that would be digitized that are larger than half the world...

The workflow would go like this: have your users create their polygons, create another polygon with the opposite orientation (ReorientObject () in SQL Server) and then compare their areas... Logically, the smallest is correct.

Just another way to solve this.