12

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?

Michael
  • 8,362
  • 6
  • 61
  • 88
Matt
  • 1,562
  • 2
  • 17
  • 27

5 Answers5

11

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
Hossein Narimani Rad
  • 31,361
  • 18
  • 86
  • 116
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 [...].

Community
  • 1
  • 1
Michael
  • 8,362
  • 6
  • 61
  • 88
  • How did you get this working on 2008. I'm getting 'Could not find method 'MakeValid' for type 'Microsoft.SqlServer.Types.SqlGeography'' – capdragon Sep 03 '15 at 20:27
  • @capdragon: Did you try `MakeValid()` on a `geometry` or a `geography`? – Michael Sep 03 '15 at 20:28
  • 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." – capdragon Sep 03 '15 at 20:34
  • 1
    Ohh I see. I'm supposed to do it on the geometry – capdragon Sep 03 '15 at 20:39
  • 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. – Zachary Ordo - GISP Nov 01 '19 at 20:10
  • @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](http://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/) with `ReorientObject()` from [my question on GIS.SE](https://gis.stackexchange.com/q/66671/20145). Lastly, if you can, do the reorientation _before save_ rather than on read. – Michael Nov 01 '19 at 22:07
1

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

Newanja
  • 651
  • 1
  • 5
  • 8
0

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.

Chris Williams
  • 1
0

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.

Andrew
  • 26,629
  • 5
  • 63
  • 86
  • Yes, I realise that. But what do I do about it? – Matt Dec 10 '10 at 20:45
  • you have to ignore the order the points are drawn, but read them in the appropriate order. – Andrew Dec 11 '10 at 11:02
  • Number of posts on stackoverflow asking that before : http://stackoverflow.com/questions/242404/sort-four-points-in-clockwise-order for example – Andrew Dec 13 '10 at 10:26