Fixing Geography Ring Orientation

30 Dec 2022 By Simon Greener

Introduction

SQL Server Spatial’s geography data type requires a specific ring orientation for its polygons. (This is in line with the OGC/SQLMM standards’ requirements for ring orientation.)

That ring orientation is:

  • Counter Clock Wise (CCW) for Exterior Rings
  • Clock Wise (CW) for Interior Rings

Which looks like this:

Correct Ring Orientation

Consequences of Incorrect Ring Orientation.

While ring orientation can be incorrect within a polygon without causing STIsValid to indicate (0) invalidity, trying to process a polygon with incorrectly oriented rings will result in invalid function result or thrown exceptions.

Two cases are presented demonstrating the behaviour of the STBuffer and STArea methods on two geography polygons with incorrect ring orientation.

Case 1: Simple Exterior Ring with Clock Wise orientation.

The function STIsCCW ships with the TSQL functions accessible from this website’s shop. It returns 1 if a polygon ring is CCW and 0 if CW.

with data as (
select geography::STGeomFromText('
POLYGON ((
153.08750064001225 -26.498820885928716, 
153.09098567437266 -26.499067232738476, 
153.09045461074626 -26.502002142475508, 
153.08707345078014 -26.502112025491613, 
153.08750064001225 -26.498820885928716))',4326) as geom
)
select [dbo].[STIsCCW](geometry::STGeomFromText(a.geom.STAsText(),4326)) as isCCW, 
       a.geom.STIsValid() as iValid,
       a.geom.STArea()    as area,
       a.geom.STBuffer(1) as bgeog
  from data as a;

isCCW	iValid	area		bgeog
0	1	510065621592592	0xE61000000224050000003301215CB37F3AC02FE7AFE0CC226340E345D668C37F3AC01E405A42E92263400C6588A382803AC0D84313EFE4226340AF0337CC89803AC03B0A0066C92263403301215CB37F3AC02FE7AFE0CC22634001000000010000000001000000FFFFFFFF0000000003

Note that even though the exterior ring polygon is incorrectly oriented, STIsValid reports that the polygon is correct, STArea and STBuffer do not throw errors but probably should as their results are incorrect. The area of the polygon is NOT 510065621592592 with the bGeog column being incorrect as the following image shows. (The image should be roughly the same as the exterior ring of the above image, but is presented as being full globe.)

Invalid Exterior Ring

Case 2: Polygon with Clock Wise Exterior Ring and Counter Clock Wise interior ring.

This example shows how the addition of an interior ring causes STArea/STBuffer execution to fail.

with data as (
select geography::STGeomFromText('
POLYGON ((
153.08750064001225 -26.498820885928716, 
153.09098567437266 -26.499067232738476, 
153.09045461074626 -26.502002142475508, 
153.08707345078014 -26.502112025491613, 
153.08750064001225 -26.498820885928716),(
153.0876919409080017 -26.5013199459790556, 
153.08989557348490962 -26.50138254917726144, 
153.09025032494139396 -26.49968808927912178, 
153.0880258246317851 -26.4995545357896134, 
153.0876919409080017 -26.5013199459790556))',4326) as geom 
)
select a.geom.STArea() as area,
       a.geom.STBuffer(50) 
  from data as a; 

Msg 6522, Level 16, State 1, Line 22
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.
System.ArgumentException: 
   at Microsoft.SqlServer.Types.SqlGeography.ThrowIfInvalid()
   at Microsoft.SqlServer.Types.SqlGeography.STArea()

The only way to fix these exceptions is to ensure that the orientation of the rings of the polygon are correct.

Fixing Ring Orientation

I have written a new function called STOrientRings which applies the CCW/CW rules to a (multi)polygon geometry. The function requires a geometry object because accessor functions like STInteriorRingN do not work on geography objects. (We switch between the two via use of Well Known Text – WKT.)

Here is the code:

CREATE FUNCTION [dbo].[STOrientRings]
(
  @p_polygon  geometry,
  @p_round_xy int = 3,
  @p_round_zm int = 2
)
Returns geometry
As
/****f* EDITOR/STOrientRings (2012)
 *  NAME
 *    STOrientRings -- Function which fixes the ring orientation of a (multi)polygon
 *  SYNOPSIS
 *    Function STOrientRings (
 *               @p_polygon  geometry
 *               @p_round_xy int,
 *               @p_round_xy int
 *             )
 *     Returns geometry 
 *  DESCRIPTION
 *    Function that checks and re-orients the rings of a (Multi)Polygon.
 *    Exterior Rings are set to CCW orientation; Inner Rings are set to CW orientation.
 *  INPUTS
 *    @p_polygon  (geometry) - Supplied geometry of supported type.
 *    @p_round_xy (int)      - Decimal degrees of precision to which calculated XY ordinates are rounded.
 *    @p_round_zm (int)      - Decimal degrees of precision to which calculated ZM ordinates are rounded.
 *  RESULT
 *    corrected geom (geometry) - Input geometry with Rings correctly oriented.
 *  EXAMPLES
 *    -- Polygon with invalid CW Exterior Ring and CCW Interior Ring
 *    with data as (
 *    select geography::STGeomFromText('
 *    POLYGON ((
 *    153.08750064001225 -26.498820885928716, 
 *    153.09098567437266 -26.499067232738476, 
 *    153.09045461074626 -26.502002142475508, 
 *    153.08707345078014 -26.502112025491613, 
 *    153.08750064001225 -26.498820885928716),(
 *    153.0876919409080017 -26.5013199459790556, 
 *    153.08989557348490962 -26.50138254917726144, 
 *    153.09025032494139396 -26.49968808927912178, 
 *    153.0880258246317851 -26.4995545357896134, 
 *    153.0876919409080017 -26.5013199459790556))',4326) as geom 
 *    )
 *    select geography::STGeomFromText(
 *           [dbo].[STOrientRings](
 *    		       geometry::STGeomFromText(a.geom.STAsText(),4326),
 *    			   8,8
 *    		   ).STAsText(),
 *    		   4326
 *           ).STBuffer(50).STArea() as area
 *      from data as a;
 *    GO
 *    
 *    area
 *    184181.893342495
 *
 *    -- MultiPolygon with all rings incorrectly ordered.
 *    with data as (
 *    select geography::STGeomFromText('
 *    MULTIPOLYGON (
 *    ((
 *    153.08750064001225 -26.498820885928716, 
 *    153.09098567437266 -26.499067232738476, 
 *    153.09045461074626 -26.502002142475508, 
 *    153.08707345078014 -26.502112025491613, 
 *    153.08750064001225 -26.498820885928716
 *    ),(
 *    153.0876919409080017 -26.5013199459790556, 
 *    153.08989557348490962 -26.50138254917726144, 
 *    153.09025032494139396 -26.49968808927912178, 
 *    153.0880258246317851 -26.4995545357896134, 
 *    153.0876919409080017 -26.5013199459790556
 *    )),((
 *    153.09750064001225 -26.498820885928716, 
 *    153.10098567437266 -26.499067232738476, 
 *    153.10045461074626 -26.502002142475508, 
 *    153.09707345078014 -26.502112025491613, 
 *    153.09750064001225 -26.498820885928716
 *    ),(
 *    153.0976919409080017 -26.5013199459790556, 
 *    153.09989557348490962 -26.50138254917726144, 
 *    153.10025032494139396 -26.49968808927912178, 
 *    153.0980258246317851 -26.4995545357896134, 
 *    153.0976919409080017 -26.5013199459790556))
 *    )',4326) as geom
 *    )
 *    select geography::STGeomFromText(
 *               [dbo].[STOrientRings](
 *    		       geometry::STGeomFromText(a.geom.STAsText(),4326),
 *    			   8,8
 *    		   ).STAsText(),
 *    		   4326
 *           ).STBuffer(50).STArea() as area
 *      from data as a;
 *    GO
 *    
 *    area
 *    368363.786691189
 *  AUTHOR
 *    Simon Greener
 *  HISTORY
 *    Simon Greener - December 2022 - Original TSQL Coding for SQL Server.
 *  COPYRIGHT
 *    (c) 2008-2022 by TheSpatialDBAdvisor/Simon Greener
******/
Begin
  Declare
    @v_geometryType varchar(100),
    @v_wkt          varchar(max),
    @v_dimensions   varchar(4),
    @v_round_xy     int,
    @v_round_zm     int,
    @v_i            int,
    @v_j            int,
    @v_geomN        geometry,
    @v_eRing        geometry,
    @v_iRing        geometry,
    @v_result       geometry;
  
  If ( @p_polygon is null ) 
    Return @p_polygon;

  -- Only process linear geometries.
  SET @v_GeometryType = @p_polygon.STGeometryType();
  IF ( @v_GeometryType NOT IN ('Polygon','MultiPolygon') )
    Return @p_polygon;

  SET @v_round_xy = ISNULL(@p_round_xy,3);
  SET @v_round_zm = ISNULL(@p_round_zm,2);
  SET @v_dimensions        = 'XY' + 
                              case when @p_polygon.HasZ=1 then 'Z' else '' end 
                              + 
                              case when @p_polygon.HasM=1 then 'M' else '' end;

  SET @v_i = 1;
  WHILE @v_i <= @p_polygon.STNumGeometries()
  BEGIN
    SET @v_geomN = @p_polygon.STGeometryN(@v_i)
    SET @v_eRing = @v_geomN.STExteriorRing();
    IF [dbo].[STisCCW](@v_eRing)=0
      SET @v_eRing = [dbo].[STReverse](@v_eRing,@v_round_xy,@v_round_zm);
    SET @v_eRing = geometry::STGeomFromText(REPLACE(@v_eRing.STAsText(),'LINESTRING','POLYGON(')+')',@v_eRing.STSrid);

    SET @v_j = 1;
    WHILE @v_j <= @v_geomN.STNumInteriorRing()
    BEGIN
      SET @v_iRing = @v_geomN.STInteriorRingN(@v_j);
      IF [dbo].[STisCCW](@v_iRing)=1
        SET @v_iRing = [dbo].[STReverse](@v_iRing,@v_round_xy,@v_round_zm);
      SET @v_iRing = geometry::STGeomFromText(REPLACE(@v_iRing.STAsText(),'LINESTRING','POLYGON(')+')',@v_iRing.STSrid);
      SET @v_eRing = @v_eRing.STDifference(@v_iRing);
      SET @v_j += 1;
    END;

    IF ( @v_result is null )
      SET @v_result = @v_eRing
    ELSE
      SET @v_result = @v_result.STUnion(@v_eRing);

    SET @v_i += 1;
  END;
  RETURN @v_result;
END
GO

Applying it to our polygon with a single interior ring is as follows:

with data as (
select geography::STGeomFromText('
POLYGON ((
153.08750064001225 -26.498820885928716, 
153.09098567437266 -26.499067232738476, 
153.09045461074626 -26.502002142475508, 
153.08707345078014 -26.502112025491613, 
153.08750064001225 -26.498820885928716),(
153.0876919409080017 -26.5013199459790556, 
153.08989557348490962 -26.50138254917726144, 
153.09025032494139396 -26.49968808927912178, 
153.0880258246317851 -26.4995545357896134, 
153.0876919409080017 -26.5013199459790556))',4326) as geom 
)
select geography::STGeomFromText(
           [dbo].[STOrientRings](
		       geometry::STGeomFromText(a.geom.STAsText(),4326),
			   8,8
		   ).STAsText(),
		   4326
       ).STBuffer(50).STArea() as polygon 
  from data as a;
GO

area
184181.893342495

Note that the area of the buffered polygon is correct.

Finally, we demonstrate the STOrientRings function working against a multipolygon with incorrect ring orientation.

with data as (
select geography::STGeomFromText('
MULTIPOLYGON (
((
153.08750064001225 -26.498820885928716, 
153.09098567437266 -26.499067232738476, 
153.09045461074626 -26.502002142475508, 
153.08707345078014 -26.502112025491613, 
153.08750064001225 -26.498820885928716
),(
153.0876919409080017 -26.5013199459790556, 
153.08989557348490962 -26.50138254917726144, 
153.09025032494139396 -26.49968808927912178, 
153.0880258246317851 -26.4995545357896134, 
153.0876919409080017 -26.5013199459790556
)),((
153.09750064001225 -26.498820885928716, 
153.10098567437266 -26.499067232738476, 
153.10045461074626 -26.502002142475508, 
153.09707345078014 -26.502112025491613, 
153.09750064001225 -26.498820885928716
),(
153.0976919409080017 -26.5013199459790556, 
153.09989557348490962 -26.50138254917726144, 
153.10025032494139396 -26.49968808927912178, 
153.0980258246317851 -26.4995545357896134, 
153.0976919409080017 -26.5013199459790556))
)',4326) as geom
)
select geography::STGeomFromText(
           [dbo].[STOrientRings](
		       geometry::STGeomFromText(a.geom.STAsText(),4326),
			   8,8
		   ).STAsText(),
		   4326
       ).STBuffer(50).STArea() as area  
  from data as a;
GO

area
368363.786691189

Note that the area is correct and is roughly twice that of the single polygon as the multipolygon was constructed by duplicating that polygon.

I hope this is of use to someone.

Leave a Reply

Your email address will not be published. Required fields are marked *