1

I am trying to do some rough conversions on existing data in SQL Server, and I've run into a fundamental problem: some values result in absolutely insane Geography values (completely different than when converting to Geometry, and larger than the world).

To demonstrate (you can play along by pasting these into SMSS), first a value that works. This:

 select geometry::STGeomFromText('POLYGON ((-117.32216660788508 34.177168197248626, 
 -117.32109372408894 34.177239206062119, -117.31997792501811 34.177345717135324,  -117.31877629517302 34.1777007560267, 
 -117.31778924291802 34.178197808468425, -117.31697385070404 34.17855284389654,  -117.31572930585527 34.178872374446435, 
 -117.31388394629543 34.179049891294419, -117.31396977727077 34.18075403145842,  -117.31435601493629 34.18128656783967, 
 -117.3142701842343 34.18224512483765,  -117.3145276765817 34.18263564480344,  -117.31478516886948 34.182884156622642, 
 -117.31602971364187 34.182884156683222, -117.31761758144738 34.182990662024359,  -117.31881921076211 34.183700691544509, 
 -117.31959168699038 34.184588218779275, -117.32049290934701 34.185227233885087,  -117.32220952286221 34.185759741650074, 
 -117.32439820560153 34.186469747642533, -117.32568566633354 34.1875347450137,  -117.32718770290016 34.186434247967654, 
 -117.32791726351087 34.187818742028931, -117.33065200748472 34.186927575007324,  -117.33057801468092 34.186682748398141, 
 -117.33062093015677 34.18636324722852, -117.33044926909629 34.185972744025044,  -117.33074967660546 34.185369235587181, 
 -117.33079259241063 34.184730222763626, -117.33023469200026 34.183452181711729,  -117.32963387708459 34.182245124812688, 
 -117.32911889359198 34.18114455809, -117.32838933249405 34.179866462235616, -117.32748811018537 34.179120898048325, 
 -117.3265010578688 34.178375326097509, -117.32499902080058 34.177700756182119, -117.32401196695326 34.177416725213575, 
 -117.32302491436702 34.177274709574604, -117.32216660788508 34.177168197248626))', 4326)

is a shape identical to

 select geography::STGeomFromText('POLYGON ((-117.32216660788508 34.177168197248626, 
 -117.32109372408894 34.177239206062119, -117.31997792501811 34.177345717135324,  -117.31877629517302 34.1777007560267, 
 -117.31778924291802 34.178197808468425, -117.31697385070404 34.17855284389654,  -117.31572930585527 34.178872374446435, 
 -117.31388394629543 34.179049891294419, -117.31396977727077 34.18075403145842,  -117.31435601493629 34.18128656783967, 
 -117.3142701842343 34.18224512483765,  -117.3145276765817 34.18263564480344,  -117.31478516886948 34.182884156622642, 
 -117.31602971364187 34.182884156683222, -117.31761758144738 34.182990662024359,  -117.31881921076211 34.183700691544509, 
 -117.31959168699038 34.184588218779275, -117.32049290934701 34.185227233885087,  -117.32220952286221 34.185759741650074, 
 -117.32439820560153 34.186469747642533, -117.32568566633354 34.1875347450137,  -117.32718770290016 34.186434247967654, 
 -117.32791726351087 34.187818742028931, -117.33065200748472 34.186927575007324,  -117.33057801468092 34.186682748398141, 
 -117.33062093015677 34.18636324722852, -117.33044926909629 34.185972744025044,  -117.33074967660546 34.185369235587181, 
 -117.33079259241063 34.184730222763626, -117.33023469200026 34.183452181711729,  -117.32963387708459 34.182245124812688, 
 -117.32911889359198 34.18114455809, -117.32838933249405 34.179866462235616, -117.32748811018537 34.179120898048325, 
 -117.3265010578688 34.178375326097509, -117.32499902080058 34.177700756182119, -117.32401196695326 34.177416725213575, 
 -117.32302491436702 34.177274709574604, -117.32216660788508 34.177168197248626))', 4326)

but whereas this is a normal shape:

select geometry::STGeomFromText('POLYGON ((-119.04875492369416 34.164161218753534, 
 -119.04647152992771 34.164653707860808, 
 -119.04651509053122 34.165394459240041, 
 -119.04248252847063 34.166478499131578, 
 -119.04172574590484 34.164564154335864, 
 -119.03917374684349 34.165235750493039, 
 -119.0380544383415 34.162236094366847, 
 -119.04002439477063 34.16165399645466, 
 -119.03894991831106 34.158296126228663, 
 -119.04136758708498 34.157624530071487,
 -119.04687457606995 34.159012443868363, 
 -119.04875492369416 34.164161218753534))', 4326)

This covers the entire earth (and the binary data is COMPLETELY different):

select geography::STGeomFromText('POLYGON ((-119.04875492369416 34.164161218753534, 
 -119.04647152992771 34.164653707860808, 
 -119.04651509053122 34.165394459240041, 
 -119.04248252847063 34.166478499131578, 
 -119.04172574590484 34.164564154335864, 
 -119.03917374684349 34.165235750493039, 
 -119.0380544383415 34.162236094366847, 
 -119.04002439477063 34.16165399645466, 
 -119.03894991831106 34.158296126228663, 
 -119.04136758708498 34.157624530071487, 
 -119.04687457606995 34.159012443868363, 
 -119.04875492369416 34.164161218753534))', 4326)

Any ideas?

2
  • 3
    If in doubt, knock off a paren pair and change 'POLYGON' to 'LINESTRING' and look at the direction, The rings are oriented with right hand rule.(exterior clockwise), but should be left hand rule (exterior counter-clockwise). My guess is: Geometry ignores the error, but Geography knows where the data boundary is, so it makes an inverted shape, as requested. What happens if you convert back to WKT?
    – Vince
    Jul 21, 2016 at 2:18
  • D'oh! Yep, you are completely right. Reversing the order fixed it. I can do that manually for these four, but do you know of an automated way? I might run into this in the future. Also, please post this as an answer so I can credit you.
    – Stu
    Jul 21, 2016 at 15:25

1 Answer 1

2

Sometimes the key to untangling polygon topology issues can be to use the same vertices to form LINESTRINGs, and render those lines with an arrowhead to view ring orientation.

Here's the first ring: enter image description here

And here's the second: enter image description here

The difference is orientation -- the shapes which draw properly in both representations have "left-hand rule" orientation (e.g., when walking the perimeter of the shape with arms extended, the left hand would be inside the shape). The shape that draws "incorrectly" as a Geography uses right-hand rule.

Geography may actually be trying to do you a favor, by assuming you know left-hand rule is required, and therefore generating the inverse (all the globe except the specified ring), while geometry just generates an invalid shape. Then again, Geography is not allowed to occupy more than one hemisphere, so maybe I'm giving Geography too much credit.

There are a number of ways to detect ring orientation in a programming environment, the easiest of which uses the Trapezoid Rule (also known as the Shoelace Formula) to calculate the area of a shape, and if the area is negative, then the shape is inverted (there's two different ways to calculate the delta, so validate it on a simple polygon of known orientation before using it on unknown shapes).

You may be able to use SQL-Server's MakeValid() request to force correct orientation to a Geometry before casting to Geography, but I wasn't able to get this to work on the first try.

4
  • MakeValid() doesn't do anything. I wound up fixing the data by writing a little C# app to reverse the order of the points in the polygon, and all is well.
    – Stu
    Jul 21, 2016 at 21:56
  • postgis has a ForceRHR function.Other spatial dbs should have something similar.
    – nickves
    Jul 21, 2016 at 22:32
  • Esri's ST_GEOMETRY implementations all correct the exterior ring (and all the others as well), without need for a validation request. MakeValid is the only repair function Microsoft provides, but it doesn't meet the need. While there "should" be a way to repair this geometry, it doesn't appear that there is (I haven't found one, at least)
    – Vince
    Jul 21, 2016 at 23:05
  • Oops! There's a built-in function for this: msdn.microsoft.com/en-us/library/ff929311.aspx
    – Stu
    Jul 22, 2016 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.