1

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

Banana Polygon

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.

3
  • This does not help you in any way, but it is possible to make PostGIS to accept ESRI style polygons by using a flag postgis.net/docs/ST_IsValidDetail.html. It still cannot convert polygons between the two models.
    – user30184
    Sep 2 '21 at 5:48
  • Thanks @user30184. You're correct in that your comment doesn't help my current situation regarding SQL Server / Azure SQL DB, but I've never noticed that ESRI flag before in PostGIS's ST_IsValidDetail, so I appreciate you leaving the note (and I'm sure someone else in the future will as well)
    – pixelpete
    Sep 2 '21 at 6:18
  • The rule for inversions actually follows from the self-intersection rule, since the rings don't cross each other, and only touch at a single point. The shapefile format does not enforce any rules, but that doesn't mean that there are no invalid geometries.
    – Vince
    Sep 2 '21 at 12:33
0

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

5
  • Thanks Simon, this is an excellent answer, as expected based on your impressive collection of work. We are exporting from an Azure SQL DB to shp using ogr2ogr. Although I'm not certain of the downstream application, I strongly suspect the customer is using ElasticSearch and is having issues with the load after converting from shp to geojson. You make a good point about even identifying the self-intersecting polygons. In this case, the customer had identified them and I had also identified them separately in PostGIS. However, identification and fixing directly in Azure is the goal.
    – pixelpete
    Sep 6 '21 at 2:52
  • 1
    Good point re: OGC SFS Part 1 and the addition of CW/CCW rules. It looks the following verbiage was added in v1.2.1 to section 6.1.11: The exterior boundary LinearRing defines the “top” of the surface which is the side of the surface from which the exterior boundary appears to traverse the boundary in a counter clockwise direction. The interior LinearRings will have the opposite orientation, and appear as clockwise when viewed from the “top”.
    – pixelpete
    Sep 6 '21 at 3:10
  • 1
    (continued) The Azure / MSSQL Spatial Data Types Overview doc indicate that the "geometry data type both conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0 and is compliant with SQL MM (ISO standard)". The same page says "The ring orientation of a polygon is not an important factor in the planar system. The OGC Simple Features for SQL Specification doesn't dictate a ring ordering, and SQL Server doesn't enforce ring ordering."
    – pixelpete
    Sep 6 '21 at 3:33
  • My investigation into ogr2ogr when trying to write Geography polygons showed that it always wrote CW which meant all Geography polygons were invalid. I then went on to try and write CCW polygons into a shapefile using FME and it wrote CW. So, in the end I used FME to write CCW polygons to GeoJSON and then used ogr2ogr to load into SQL Server Spatial Geography. Over half a day lost to trying to work out what was going on.... sometimes standards don't seem to be worth the paper they are written on. Sep 6 '21 at 5:09
  • Since you are writing shp using ogr2ogr I would check to see what it does when you export a polygon with CCW ordering. I would love to be wrong, but I suspect it will reverse the ordering in the shapefile. If so, you would probably get more joy writing GeoJSON directly as I would bet ogr2ogr doesn't worry what it is writing (the GeoJSON standard doesn't specify ordering). Sep 6 '21 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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