Polygon node order will not correct [duplicate]

Question

I've got a few polygons (they were previously one multipolygon) which seem to insist on having an anticlockwise node order. As such, when running through various operations in SQL, they throw out the following error:

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.

No matter what I try, they seem to remain in an anticlockwise order (which I'm getting by looking at the coordinates when I use the following):

SELECT
    CAST(
        Geom AS VARCHAR(MAX)
    )
FROM dbo.Geometries
WHERE id = 1

I've tried using various conversions and operations, including converting to geography:

SELECT
    GEOGRAPHY::STGeomFromText
        (CAST
            (Geom AS VARCHAR(MAX))
    ,4283)
FROM dbo.Geometries
WHERE id = 1

(which, when interrogated, reveals the nodes in the same order)

And ReorientObject() too, which strangely results in a null:

SELECT
    GEOGRAPHY::STGeomFromText
        (CAST
            (Geom AS VARCHAR(MAX))
    ,4283).ReorientObject()
FROM dbo.Geometries
WHERE id = 1

I've even tried some really weird stuff (like getting the shape's envelope, then the difference between the shape and the envelope, then the difference between the envelope and this new shape), but everything is coming back with the coordinates in the wrong direction. Not to mention that I've also redrawn the shape manually too, in the right direction.

Does anyone have any idea what may be going on, or if there may be a way to reverse the coordinates from an extracted WKT? Shape as WKT below:

POLYGON ((140.31513731241938 -37.361175421343845, 140.31692577675838 -37.361112938712338, 140.31651306045254 -37.3603397028184, 140.31579569995952 -37.359285256895539, 140.3157269163172 -37.35910563021951, 140.31563847294319 -37.359089989244993, 140.31513731241938 -37.361175421343845))

EDIT - should point out that these are all pretty simple shapes with few nodes - certainly less than 30 per shape. However, I'd love it if there were a reliable method of correcting them, for standard polygons, multipolygons, and polygons/multipolygons with rings.

EDIT - also tried buffering, ST_Union with the first point, and MakeValid.

EDIT - have also tried the solution suggested in the possible duplicate, that still leaves it in the current anticlockwise order.

Answer 1

Okay, after everything I tried wasn't working, I decided to rewrite the geometry from scratch - or more rather have a query that would do it.

Below is some pretty crude code which will take a simple POLYGON shape and reverse the order of the nodes. I wrote a "placeholdergeom" more as a test, I guess it's not needed any more. The code may also be good if anyone needs to reverse the order of comma-separated values in a single field.

declare @id int = 1;

declare @countnodes int;

set @countnodes = 
(select len(cast(geom as varchar(max))) from base.geometries where id=@id)
- 
(select len(replace(cast(geom as varchar(max)),',','')) from base.geometries where id=@id) + 1

declare @geom varchar(max);
declare @geomrev varchar(max);
declare @geomdest varchar(max) = '';

set @geom = 
(select cast(geom as varchar(max)) from base.geometries where id=@id)

--strip @geom - THIS ONLY WORKS FOR POLYGONS (no multipolygons, no rings)

set @geom = replace(@geom,'POLYGON','')
set @geom = replace(@geom,'(','')
set @geom = replace(@geom,')','')
set @geom = ltrim(rtrim(@geom))

declare @localnode int = 0;
declare @nodelength int;

while @localnode < @countnodes
begin
    set @geomrev = reverse(@geom)
    set @nodelength = charindex(',',@geomrev)
    set @geomdest = concat(@geomdest,right(@geom,@nodelength))
    set @geom = left(@geom,len(@geom)-@nodelength)
    set @localnode = @localnode + 1
end

set @geomdest = concat(@geomdest,', ',@geom)

set @geomdest = concat('POLYGON ((',right(@geomdest,len(@geomdest)-2),'))')

declare @placeholdergeom varchar(max) = 'POLYGON ((2 2, 3 2, 3 3, 2 3, 2 2))';

update base.geometries set geom = geometry::STGeomFromText(@placeholdergeom,4283) where id=@id

update base.geometries set geom = geometry::STGeomFromText(@geomdest,4283) where id=@id