Can a self-intersecting banana polygon be made OGC-compliant in an Azure SQL Database using spatial functions?

Question

I'm an experienced Postgres / PostGIS user fiddling with SQL Server / Azure SQL Database spatial capabilities for the first time.

Banana Polygon

For the banana polygon pictured above, I believe there are two primary ways to represent it (ignoring winding order):

1. A single outer ring (crescent/banana) that self-intersects at a single point
2. An outer ring (square) and inner ring (triangle hole) that touch at one point

It is my understanding that only form 2 is OGC compliant as rings cannot self-intersect.

It seems to me that an Azure SQL Database considers both of the above forms as a valid geometry.

My question is this: can form 1 be converted to form 2 in Azure SQL Database using spatial functions?

MakeValid() doesn't seem to alter form 1 since it is already considered valid. (Whereas in PostGIS, form 1 is considered invalid and ST_MakeValid will convert it to form 2).

My purpose for converting from form 1 to form 2 is to provide OGC-compliant polygons to downstream applications after exporting to shapefile. (While the ESRI shapefile format also specifies that rings should not self-intersect, it does not actually enforce that.)

Form 1 - IsValidDetailed returns Valid

SELECT ( geometry::STGeomFromText('POLYGON ((-80.3 25.9, -80.3 25.8, -80.2 25.8, -80.2 25.9, -80.25 25.9, -80.225 25.85, -80.275 25.85, -80.25 25.9, -80.3 25.9))', 0 ) ).IsValidDetailed()
--24400: Valid

Form 2 - IsValidDetailed returns Valid

SELECT ( geometry::STGeomFromText('POLYGON ((-80.3 25.9, -80.3 25.8, -80.2 25.8, -80.2 25.9, -80.3 25.9), (-80.25 25.9, -80.225 25.85, -80.275 25.85, -80.25 25.9))', 0 ) ).IsValidDetailed()
--24400: Valid

I used SRID 0 here, but as expected I get the same results with 4326, 4269, etc.

I also get the same results (as expected) with MULTIPOLYGON instead of POLYGON.

As expected, I get the same results regardless of winding order, as winding order is meaningless for geometry (but not geography) in Azure SQL Database

MakeValid does not alter form 1 at all

SELECT ( geometry::STGeomFromText('POLYGON ((-80.3 25.9, -80.3 25.8, -80.2 25.8, -80.2 25.9, -80.25 25.9, -80.225 25.85, -80.275 25.85, -80.25 25.9, -80.3 25.9))', 0 ) ).MakeValid().STAsText()
-- >> shifted for comparison       POLYGON ((-80.3 25.9, -80.3 25.8, -80.2 25.8, -80.2 25.9, -80.25 25.9, -80.225 25.85, -80.275 25.85, -80.25 25.9, -80.3 25.9))

Buffering by 0 also does not alter form 1 at all

SELECT ( geometry::STGeomFromText('POLYGON ((-80.3 25.9, -80.3 25.8, -80.2 25.8, -80.2 25.9, -80.25 25.9, -80.225 25.85, -80.275 25.85, -80.25 25.9, -80.3 25.9))', 0 ) ).STBuffer(0.0).STAsText()
-- >> shifted for comparison       POLYGON ((-80.3 25.9, -80.3 25.8, -80.2 25.8, -80.2 25.9, -80.25 25.9, -80.225 25.85, -80.275 25.85, -80.25 25.9, -80.3 25.9))

Also, while we use ogr2ogr to export these polygons to shapefiles and an ogr2ogr solution is available here - How to to validate geometries using ogr2ogr - I'm looking for an in-database solution using spatial functions.

Answer 1

All that you say is true, SQL Server Spatial supports both types of polygons regardless as to "standards" compliance.

As you indicate MakeValid (and STIsValid) will process both and return the same object. Yes, MakeValid works out it doesn't need to do anything to a valid geometry!

My purpose for converting from form 1 to form 2 is to provide OGC-compliant polygons to downstream applications after exporting to shapefile.

If an acceptable solution (see below) can be found, how are you going to identify the banana polygons in the first place? One suggestion is to get the exterior ring as a linestring and check if it is simple (STIsSimple).

Which applications are you trying to support?

An observation: qGIS, FME and others all expect (and write) CW ordered polygons in shapefiles. SQL Server Spatial supports CW and CCW but mandates CCW only for Geography but not Geometry. (If I remember correctly, OGC SFS 1.1 allowed CW and CCW but, I think, OGC SFS 1.2 requires CCW ordering.)

A solution to fixing the banana polygon in Oracle is to compute a self union: however this does not work in SQL Server Spatial.

The following solution to your problem involves use of some of the TSQL tools I make available via my website (http://www.spdba.com.au). The idea is to STBuffer the polygon by a tiny amount and then reverse buffer the result using the same tiny amount but as a negative number. Because of the way SQL Server Spatial does the buffering, and my STRound works, you will end up with a polygon with duplicate vertices which can be removed using my STRemoveDuplicatePoints function.

    with data as (
      select geometry::STGeomFromText('POLYGON((0 0, 0 10,5 10, 2.5 5,7.5 5, 5 10,10 10, 10 0, 0 0))',0) as banana
    )
    select a.banana.STIsValid()       as iValid,
           a.banana.IsValidDetailed() as ivDetailed,
           a.banana.STGeometryN(1).STExteriorRing().STIsSimple() as isSimple,
           dbo.STRemoveDuplicatePoints(
             dbo.STRound(a.banana.STBuffer(0.0001).STBuffer(-0.0001),
                         3,3,1,1),
             3,1,1).STAsText() as bBanana
      from data as a;

iValid ivDetailed   isSimple bBanana
1      24400: Valid 0        POLYGON ((0 0, 10 0, 10 10, 5 10, 0 10, 0 0), (2.5 5, 5 10, 7.5 5, 2.5 5))

Also, in the TSQL functions is a function called STIsCCW which determines if a polygon's exterior ring is CCW (1) or CW (0). If the latter, and you want to write CCW polygons to the shapefiles, you can then STReverse the polygon rings before writing. (But as I indicated above, check what the application creation the shapefile does to the polygons as you may have your CCW work undone!)

HTH Simon