1

This question already has an answer here:

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.

marked as duplicate by MappaGnosis, Fran Raga, whyzar, Hornbydd, Jochen Schwarze May 16 at 7:17

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Nope, already tried that one, not working. – user25730 May 15 at 23:00
0

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
  • So this will butcher anything but the simplest polygon. Anything with a hole will be messed up. The union trick does not reverse winding order it will only set it the same direction for all geometry objects. The object you posted is not wound to be a hole in the world and has a small area. Transforming this to geography using STAsBinay() and then reorienting the object and turning back to geometry does reverse winding order. The Starting point changes but the winding order is reversed. Just as it should be. This will work on all shapes, not just simple polygons. – Brian W. Jun 14 at 18:22

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