4

I've decide to use sql server on Azure for my gis database.

I'll be dealing with spatial Geography not Geometry.

While inserting some country boundaries using the spatial data in SQL Server, some rings were correctly inserted (the inner part of the ring was selected),

enter image description here

while some other excluded the inner part of the ring but selected the rest of the world. enter image description here

I know that the orientation of the rings matter, I'm using WKT standard since it is used in SQL Server. ...and yes, I know that the polygon's orientation check, counter-clockwise or clockwise, can be done by using the trapezoid rule to calculate the area. I what to know which rules apply to MS Sql Server.

Trapezoid rule, in relation the area result:

  • if area<0 (the orientation is counter-clockwise)
  • if area>0 (the orientation is clockwise)

According to Wikipedia to define a WKT polygon:

https://en.wikipedia.org/wiki/Well-known_text

...It's useful to note that the OGC standard definition requires a polygon to be topologically closed. It also states that if the exterior linear ring of a polygon is defined in a counter clockwise direction it will be seen from the "top". Any interior linear rings should be defined in opposite fashion compared to the exterior ring, in this case, clockwise...

I've followed the above rule to create my polygons, multi-polygons and polygons with holes (inner rings), but I've encountered some inconsistent results.

To test the inconsistency, I've created some polygons and multi-polygons:

"1-POLYGON - COUNTER-CLOCKWISE"

DECLARE @geog1 geography;                --COUNTER-CLOCKWISE
SET @geog1 = geography::STPolyFromText(
'POLYGON((-1 -1,-2 -1,-2 -2,-1 -2,-1 -1))'
, 4326)
SELECT @geog1

enter image description here

"2-POLYGON - COUNTER-CLOCKWISE" <<(WRONG, REST OF WORLD SELECTED)>>

DECLARE @geog1 geography;                --CLOCKWISE (*WRONG the whole wolrd is selected)
SET @geog1 = geography::STPolyFromText(
'POLYGON((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))'
, 4326)
SELECT @geog1

enter image description here

When dealing with multi polygon and inner rings/shapes the left hand rule (counter clockwise) does not apply, it seems that what ever orientation works. In the following examples I create a multi-polygon and for each example I've reversed the orientation of a polygon.

"3-MULTI POLYGON"

--Polygon 1: COUNTER-CLOCKWISE(LHR), Polygon 2: COUNTER-CLOCKWISE(LHR), Polygon 3: CLOCKWISE(RHR)
DECLARE @g3 geography = 'MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))';
SELECT @g3 

"4-MULTI POLYGON"

--Polygon 1: COUNTER-CLOCKWISE(LHR), Polygon 2: COUNTER-CLOCKWISE(LHR), *Polygon 3: COUNTER-CLOCKWISE(LHR)
DECLARE @g3 geography = 'MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-2 -1,-2 -2,-1 -2,-1 -1)))';
SELECT @g3 

"5-MULTI POLYGON"

--Polygon 1: COUNTER-CLOCKWISE(LHR), *Polygon 2: CLOCKWISE(RHR), *Polygon 3: COUNTER-CLOCKWISE(LHR)
DECLARE @g3 geography = 'MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,1 2,2 2,2 1,1 1)), ((-1 -1,-2 -1,-2 -2,-1 -2,-1 -1)))';
SELECT @g3 

"6-MULTI POLYGON"

--*Polygon 1: CLOCKWISE(RHR), *Polygon 2: CLOCKWISE(RHR), *Polygon 3: COUNTER-CLOCKWISE(LHR)
DECLARE @g3 geography = 'MULTIPOLYGON(((0 0,0 4,4 4,4 0,0 0),(1 1,1 2,2 2,2 1,1 1)), ((-1 -1,-2 -1,-2 -2,-1 -2,-1 -1)))';
SELECT @g3 

All the multi-polygons work, this is the result

enter image description here

In the following example I've create a polygon with an inner ring, in example 7 I've inverted the orientation of both the external and internal ring. The orientation rule seems to be ignored also when dealing with polygons containing an inner ring.

"6 POLYGON"

DECLARE @geog1 geography;                --
SET @geog1 = geography::STPolyFromText(
'POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1,1 2,2 2,2 1,1 1))'
, 4326)
SELECT @geog1

"7 POLYGON"

DECLARE @geog1 geography;                --
SET @geog1 = geography::STPolyFromText(
'POLYGON((0 0,0 4,4 4,4 0,0 0),(1 1,2 1,2 2,1 2,1 1))'
, 4326)
SELECT @geog1

enter image description here

QUESTION?

  1. WKT rules are only applied to polygons without inner ring and are also not applied to multi-polygons?
  2. How come when creating multi-polygons the WKT rules don't apply, is it because MS Server automatically fixes the orientation issue?
  3. Is there something else I need to know in order to be enlighten by this mystery?
  4. Does all this also apply to geometry spatial data?
  • Left hand rule (also known as right hand rule when you interact with KML) is not the same as counter-clockwise. Interior rings under LHR should be clockwise. The area of each ring can be calculated to determine its area, and that sign is used to determine whether it is interior. Furthermore, all interior rings should immediately follow their parent exterior ring. All RDBMS implementations are free to repair incorrect geometry as they see fit, but I'd suggest it is unwise to push that envelope -- If you know the correct orientation, you should populate the geometries appropriately. – Vince Jun 29 '18 at 0:20
  • Something to read about polygons springer.com/%3FSGWID=5-102-45-124048-p32597622. – user30184 Jun 29 '18 at 6:42
  • If you look at the documentation for Polygons in SQL Server, it makes no mention of orientation of the rings. The documentation for STPolyFromText also make no mention of orientation. – Ian Turton Jun 29 '18 at 7:46
  • @IanTurton Spatial Data Types Overview, from microsoft, states that for -geography- "In an ellipsoidal system, a polygon has no meaning, or is ambiguous, without an orientation". Thus according to my examples (1 and 2) orientation makes sense. docs.microsoft.com/en-us/sql/relational-databases/spatial/… – Luther Jun 29 '18 at 8:46
  • 1
    @Luther - sorry I wasn't implying it didn't make sense, just that the docs didn't help you decide which one should apply – Ian Turton Jun 29 '18 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.