Geometry Validity and Method Access

Introduction

All database spatial types that I have worked with have constructors based on Well Known Text (WKT).

When constructing a geometry all those types check the WKT’s structure.

So, for example, the polygon below is wrong for two reasons:

• 1. It does not have its exterior ring correctly defined (eg “POLYGON((…))” ;
• 2. It does not have enough vertices for the exterior ring ie 2 not 4+.

SQL Server Spatial’s geometry::STGeomFromText() constructor will report errors until the WKT is correctly defined.

select geometry::STGeomFromText('POLYGON (0 0,1 1)',0);

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": 
System.FormatException: 24142: Expected "(" at position 9. The input has "0".
System.FormatException: 
   at Microsoft.SqlServer.Types.WellKnownTextReader.RecognizeToken(Char token)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseLineStringText()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePolygonText()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid).

However, geometry objects also have descriptive validity, that is, do the coordinates describe a valid geometry?

For example, duplicate vertices or, the subject of this article, spikes in linestrings, and loops in polygon rings.

All the spatial type systems validate the structural/syntactic element of the WKT but, as will be seen, only Oracle and PostGIS do not validate the description of the WKT on construction of the generated geometry object.

In short, you can construct a descriptively invalid object in Oracle and PostGIS but not with SQL Server Spatial.

And, additionally, all the main access methods (eg ST_GeometryN) on such a geometry work for Oracle and PostGIS but not for SQL Server Spatial.

Data

An invalid polygon used in all the tests is as shown:

Here are the tests of this polygon in Oracle Spatial, PostgreSQL\PostgiS and SQL Server Spatial.

SQLServer Spatial 2017

Methods that work on Invalid Geometries

 WITH DATA AS (
   SELECT geometry::STGeomFromText('POLYGON((0 0,10 0,10 10.01,10.01 10,0 10,0 0))',0) AS poly
 )
 SELECT a.poly.STIsValid()         AS isValid,
        a.poly.STIsEmpty()         AS isEmpty,
        a.poly.IsValidDetailed()   AS isVD,
        a.poly.STSrid              AS srid,
        a.poly.HasZ                AS hZ,
        a.poly.HasM                AS hM,
        round(a.poly.STLength(),5) AS LENGTH,
        a.poly.STAsText()          AS wkt
   FROM DATA AS a;

Methods that don’t work on Invalid Geometries

 WITH DATA AS (
   SELECT geometry::STGeomFromText('POLYGON((0 0,10 0,10 10.01,10.01 10,0 10,0 0))',0) AS poly
 )
 SELECT a.poly.STIsSimple()        AS isSimple,
        a.poly.STIsClosed()        AS isClosed,
        a.poly.STNumGeometries()   AS numgeoms,
        a.poly.STNumPoints()       AS pnts,
        a.poly.STNumInteriorRing() AS numIinterorRing,
        a.poly.STExteriorRing()    AS ExternalRing,
        a.poly.STEnvelope()        AS envelope,
        a.poly.STDimension()       AS dim,
        a.poly.STStartPoint()      AS startPt,
        a.poly.STBoundary()        AS boundary,
        a.poly.STArea()            AS area
   FROM DATA AS a;

All produce the following error message.

Msg 6522, Level 16, State 1, Line 12
A .NET Framework error occurred during execution of user-defined routine or aggregate geometry:
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.SqlGeometry.ThrowIfInvalid()
at Microsoft.SqlServer.Types.SqlGeometry.STIsSimple()
*or ST_IsClosed() etc */

PostGIS

For PostGIS, all the equivalent methods return a value.

 WITH DATA AS (
   SELECT ST_GeomFromText('POLYGON((0 0,10 0,10 10.01,10.01 10,0 10,0 0))',0) AS poly
 )
 SELECT ST_IsValid(a.poly)                                        AS isValid,
        ST_IsEmpty(a.poly)                                        AS isEmpty,
        ST_Dimension(a.poly)                                      AS dim,
        ST_Srid(a.poly)                                           AS srid,
        CASE WHEN ST_Zmflag(a.poly) IN (2,3) THEN 1 ELSE 0 END    AS hasZ,
        CASE WHEN ST_Zmflag(a.poly) IN (1,3) THEN 1 ELSE 0 END    AS hasM,
        round(ST_Length(ST_ExteriorRing(a.poly))::NUMERIC,5::INT) AS LENGTH,
        round(ST_Area(a.poly)::NUMERIC,5::INT)                    AS area,
        ST_NumGeometries(a.poly)                                  AS numgeoms,
        ST_NumPoints(ST_ExteriorRing(a.poly))                     AS numPnts,
        ST_NumInteriorRing(a.poly)                                AS numInteriorRings,
        ST_AsText(ST_StartPoint(ST_ExteriorRing(a.poly)))         AS startPt,
        ST_AsText(ST_EndPoint(ST_ExteriorRing(a.poly)))           AS EndPt,
        ST_AsText(ST_Envelope(a.poly))                            AS envelope,
        ST_AsText(ST_ExteriorRing(a.poly))                        AS eRing,
        ST_AsText(ST_Boundary(a.poly))                            AS boundary
   FROM DATA a;

The results are:

Note that for PostGIS, the ExteriorRing of the polygon had to be selected to get some properties eg start point etc. But at least one can select the exterior ring which one cannot do from an invalid geometry in SQL Server Spatial.

Oracle SDO_GEOMETRY

For Oracle, all the equivalent methods return a value.

This is because Oracle applies no constraints or checks on the validity of an object.

Due to its open SDO_GEOMETRY storage structure, all elements (eg coordinates) of a geometry instance are accessible.

It does provide functions to check if an object is valid, after which correction can occur using available tools.

I will use my T_GEOMETRY object type as it is more descriptive and has all the methods we need under one roof.

 WITH DATA AS (
   SELECT T_GEOMETRY(SDO_GEOMETRY('POLYGON((0 0,10 0,10 10.01,10.01 10,0 10,0 0))',NULL),0.0005,5,1) AS poly
     FROM DUAL
 )
 SELECT a.poly.ST_IsValid()                  AS isValid,
        a.poly.ST_IsValidContext()           AS isValidContext,
        a.poly.ST_IsEmpty()                  AS isEmpty,
        a.poly.ST_Dimension()                AS dim,
        a.poly.ST_Srid()                     AS srid,
        a.poly.ST_HasZ()                     AS hZ,
        a.poly.ST_HasM()                     AS hM,
        a.poly.ST_Length(p_round=>1)         AS LENGTH,
        a.poly.ST_Area()                     AS area,
        a.poly.ST_NumGeometries()            AS numgeoms,
        a.poly.ST_NumPoints()                AS numPnts,
        a.poly.ST_NumInteriorRing()          AS numInteriorRings,
        a.poly.ST_StartPoint().ST_AsText()   AS startPt,
        a.poly.ST_EndPoint().ST_AsText()     AS endPt,
        a.poly.ST_Envelope().ST_AsText()     AS envelope,
        a.poly.ST_ExteriorRing().ST_AsText() AS eRing,
        a.poly.ST_Boundary().ST_AsText()     AS boundary
   FROM DATA a;

The results are:

Importance

Of what importance is any of this?

Geometry Representation and Error Correction

The problem with spatial types that do not allow API access to elements, such as coordinate points, when the geometry is invalid is that they make it difficult for code to be developed to correct data errors specific to particular disciplines.

One situation that is particularly annoying is when processing GPS linear data to remove spikes.

Another situation is where one wishes to reduce the precision of a geometry (round ordinate values) and the reduction makes the geometry “invalid”.

Here is an example of where a geometry is rounded and becomes invalid.

 WITH DATA AS (
   SELECT geometry::STGeomFromText('LINESTRING(0 0,1 0,1.001 1,1.0001 0,2 0)',0) AS line
 )
 SELECT a.line.STIsValid() AS isValid,
        [dbo].[STRound](a.line,3,3,1,2).STAsText()        AS wktR,
        [dbo].[STRound](a.line,3,3,1,2).IsValidDetailed() AS isValidR
   FROM DATA AS a
 GO

Remember, IsValidDetails() may be executed against an invalid geometry whereas if we had executed STNumPoints() the query would have errored out.

The only way I have found to fix these geometries is to use WKT processing.

WKT Processing

How does one generate WKT that is invalid?

In one contract, the WKT was generated from Latitude/Longitude (or XY) columns by native SQL such as follows:

with data as (
select 147 as longitude, -43 as latitude
union all
select 147.1 as longitude, -43.3 as latitude
union all
select 147.12 as longitude, -43.345 as latitude
union all
select 147.13 as longitude, -43.567 as latitude
)
select 'LINESTRING(' +
          STRING_AGG(
              CAST(longitude as varchar(50)) + 
              ' ' + 
              CAST(latitude as varchar(50)),
              ','
          ) + ')' as wkt
  from data as a
GO

wkt 
LINESTRING(147.00 -43.000,147.10 -43.300,147.12 -43.345,147.13 -43.567)

Before passing to geometry::STGeomFromText(), the WKT was processed to remove spikes by the following function.

CREATE FUNCTION [dbo].[STRemoveSpikesByWKT]
(
  @p_linestring      varchar(max),
  @p_srid            int   = 0,
  @p_angle_threshold float = 0.5,
  @p_round_xy        int   = 3,
  @p_round_z         int   = 2,
  @p_round_m         int   = 2 
)
Returns varchar(max)

The STRemoveSpikesByWKT function removes spikes and unnecessary points that lie on straight line between adjacent points.

This function tests rolling 3 point line segments to detect spikes.

Spikes are detected where the subtended angle between the segments is less than a user provided threshold value (degrees), @p_angle_threshold, the mid point is removed.

In the following we have two spikes, one that is simple, and one that is not (two adjacent segments are identical but reversed).

with data as (
 select 'Spike with gap: Simple' as tGeom, 
        'LINESTRING(0 0,1 0,2 0,2.125 0,2.15 5.0,2.3 0,3 0)' as geomWKT
 union all
 select 'Spike with no gap: Not Simple' as tGeom, 
        'LINESTRING(0 0,1 0,2 0,2.125 0,2.125 5.0,2.125 0,3 0)' as geomWKT
)
select tGeom,
       a.geomWKT,
       [dbo].[STRemoveSpikesByWKT](
         /* @p_linestring      */ a.geomWKT,
         /* @srid              */   0,
         /* @p_angle_threshold */ 0.0,
         /* @p_round_xy        */   3,
         /* @p_round_z         */   0,
         /* @p_round_m         */   0
       ) as rsWKT,
       geometry::STGeomFromText(
         [dbo].[STRemoveSpikesByWKT](
         /* @p_linestring      */ a.geomWKT,
         /* @srid              */   0,
         /* @p_angle_threshold */ 0.0,
         /* @p_round_xy        */   3,
         /* @p_round_z         */   0,
         /* @p_round_m         */   0
       ),0) as geomWKT
  from data as a
GO

Before processing, the ‘Spike with no gap: Not Simple’ geometry could not be passed to geometry::STGeomFromText().

After spike removal it could.

The ‘Spike with gap: Simple’ geometry, while it could be instantiated as a geometry, is an example of the processing needed to identify and remove spikes from data like GPS points that need to be converted into LineStrings.

Conclusion

The power of using WKT for cleaning geometric data before geometry construction was demonstrated via powerful examples.

I hope this article is of interest to someone.