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?

Toro

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 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

Bankhead

Excelent answer. You just saved my day. – Melina

@Pabloker glad to be helpful. You may want to see this too link – Bankhead

If you have existing entries the following query helps: UPDATE foo_table SET bar_column = bar_column.ReorientObject() WHERE bar_column.EnvelopeAngle() > 90 – Curule

Answer 2

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 [...].

Milton

How did you get this working on 2008. I'm getting 'Could not find method 'MakeValid' for type 'Microsoft.SqlServer.Types.SqlGeography'' – Ampere

@capdragon: Did you try MakeValid() on a geometry or a geography? – Milton

Geography. I'm in a similar situation as you. I have Geometry, projected and converted to Geography in a separate feature class but now I get errors of "Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation." – Ampere

Ohh I see. I'm supposed to do it on the geometry – Ampere

How do I do this with a variable selection? For example, my POLYGON((X Y, X Y, X Y)) string is in a table. I'm not going to be manually declaring each polygon. – Singsong

@ZacharyOrdo-GISP Is your polygon a string or a geometry object. If the latter, you can perhaps CAST(foo_column.MakeValid().StUnion(foo_column.STStartPoint()).STAsText() AS GEOGRAPHY). Secondly, if you are on any version of SQL Server above 2008, use the link with ReorientObject() from my question on GIS.SE. Lastly, if you can, do the reorientation before save rather than on read. – Milton

Answer 3

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 :)

Frederiksen

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.

Fogbow

Yes, I realise that. But what do I do about it? – Toro

you have to ignore the order the points are drawn, but read them in the appropriate order. – Fogbow

Number of posts on stackoverflow asking that before : #242904 for example – Fogbow

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.

Precontract