Errors converting Geometry to Geography

I am getting an error trying to convert data from a Geometry field to a geography field in a separate table.

INSERT INTO PIGeoData
        ([ID], [geo_name], [geo_wkt] ,[port_geography_binary] )
SELECT  [id], [name] ,[wkt], GEOGRAPHY::STGeomFromWKB(em_ports.geom.STAsBinary(),4326)
FROM [guest].[em_ports]
where ID < 4548 and ID not in (select ID from PIGeoData)

The error I get is this

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation. To create a larger than hemisphere geography instance, upgrade the version of SQL Server and change the database compatibility level to at least 110.
Microsoft.SqlServer.Types.GLArgumentException: 
   at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
   at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai)
   at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai)
   at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromBinary(OpenGisType type, SqlBytes wkbGeography, Int32 srid)

I get the same message if I try to convert from WKT using

      ,GEOGRAPHY::STGeomFromText(wkt,4326)

Both these formats come from the MS documentation here

But if I copy the polygon data from the wkt and paste it into a query like this

declare @sGeo  geography
declare @sWKT varchar(max)
select @sWKT = wkt from guest.em_ports where wkt like '%POLYGON ((73.50667 4.181667,73.50667 4.21,73.48 4.21,73.48 4.1783333,73.50667 4.181667,73.50667 4.181667))%'
set @sGeo = geography::STPolyFromText (@sWKT, 4326 )  
Update PIGeoData
Set PortBoundaries = @sGeo

Where wkt like '%POLYGON ((73.50667 4.181667,73.50667 4.21,73.48 4.21,73.48 4.1783333,73.50667 4.181667,73.50667 4.181667))%'

that works.

So I moved all the non-geo data to the new table and started going through record by record to see which WKT was failing:

I used this query

Update PIGeoData
Set port_geography_binary = GEOGRAPHY::STGeomFromText(geo_wkt,4326)
where port_geography_binary is null and ID = <xyz>

where xyz was individual record ids

These WKT values succeeded

POLYGON ((-135.31197 59.451653,-135.32457 59.45799,-135.32996 59.454834,-135.36717 59.455154,-135.36452 59.449005,-135.36488 59.43996,-135.36697 59.43817,-135.33139 59.438065,-135.31197 59.451653,-135.31197 59.451653))

POLYGON ((-4.524549 48.365623,-4.518855 48.361416,-4.4854136 48.367413,-4.436236 48.381382,-4.420772 48.39644,-4.431077 48.398525,-4.4376454 48.393867,-4.438626 48.38611,-4.4559207 48.390007,-4.470995 48.387226,-4.4933248 48.384468,-4.499816 48.38401,-4.512855 48.3754,-4.524549 48.365623,-4.524549 48.365623))

These WKT values failed

POLYGON ((-8.788489 37.773106,-8.989748 37.785244,-9.11148 37.93065,-9.01401 38.13953,-8.993956 38.30128,-9.266149 38.264282,-9.382366 38.33244,-9.435615 38.54836,-9.656681 38.602306,-9.683701 38.883057,-9.1720295 39.00796,-8.444215 39.550682,-8.213643 39.355015,-8.537656 38.037514,-8.712016 37.782127,-8.788489 37.773106))

POLYGON ((-119.71587 34.396824,-119.69837 34.410378,-119.67453 34.41837,-119.62994 34.420082,-119.63012 34.380177,-119.62986 34.3551,-119.71534 34.355022,-119.71587 34.396824,-119.71587 34.396824))

There is nothing obvious to me in the data. Can anyone help with why these records and data are failing?

TIA

Solution

The relevant part of the error message is "A common reason for this error is that a polygon has the wrong ring orientation."

The polygons that have failed are in clockwise order.

To convert them to counter-clockwise order, you can use something like this:

DECLARE @t VARCHAR(MAX)='POLYGON ((-119.71587 34.396824,-119.69837 34.410378,-119.67453 34.41837,-119.62994 34.420082,-119.63012 34.380177,-119.62986 34.3551,-119.71534 34.355022,-119.71587 34.396824,-119.71587 34.396824))'

DECLARE @x XML=REPLACE(REPLACE(REPLACE(@t,'POLYGON ((','<root><p>'),'))','</p></root>'),',','</p><p>')
DECLARE @r VARCHAR(MAX)='POLYGON (('+STUFF((
    SELECT ','+q.Point
    FROM (
        SELECT n.value('.','varchar(50)') AS Point, ROW_NUMBER() OVER (ORDER BY t.n) AS Position
        FROM @x.nodes('/root/p') t(n)
    ) q ORDER BY q.Position DESC
    FOR XML PATH(''), TYPE
).value('.','VARCHAR(MAX)'),1,1,'')+'))'

DECLARE @g GEOGRAPHY=GEOGRAPHY::STGeomFromText(@r,4326)
SELECT @g, @g.ToString()

Later edit:

There is a convention that says that a polygon should always be represented in counter-clockwise order. Imagine that you have a polygon in the shape of the equator; without this convention it would not be clear if the polygon represents the northern hemisphere or the southern hemisphere. See Spatial Data Types Overview in the Microsoft SQL Server documentation for details.

Additionally, there is a limitation in SQL Server when the compatibility level is 100 or below that each geography instance must fit inside a single hemisphere. If you are using SQL Server 2012 or later and you choose to use at least compatibility level 110, you can avoid the error message, but the polygon would represent the entire area that is outside of what you would normally think that the polygon represents.

If you use compatibility level is 100 or below, you could use a TRY/CATCH to detect the error and if it happens you should try reversing the polygon.

If you use compatibility level 110 or later, you can try to use STArea() to check if the polygon has a surface which is much bigger or much smaller than one hemisphere. If the area approaches 510100000000000 square meters (which approximately the area of the entire earth) then you should reverse the polygon.