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;
| isValid | isEmpty | isValidDetailed | srid | hZ | hM | length | wkt |
|---|---|---|---|---|---|---|---|
| 0 | 0 | 24404: Not valid because polygon ring (1) intersects itself or some other ring. | 0 | 0 | 0 | 40.03414 | POLYGON ((0 0, 10 0, 10 11, 11 10, 0 10, 0 0)) |
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:
| isvalid | isempty | dim | srid | hasz | hasm | length | area | numGeoms | numPnts | numinteriorrings | startpt | endpt | Envelope | ExteriorRing | Boundary |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| False | False | 2 | 0 | 0 | 0 | 40.03414 | 99.99995 | 1 | 6 | 0 | POINT (0 0) | POINT (0 0) | POLYGON ((0 0,0 11,11 11,11 0,0 0)) | LINESTRING (0 0,10 0,10 11,11 10,0 10,0 0) | LINESTRING (0 0,10 0,10 11,11 10,0 10,0 0) |
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:
| isValid | isEmpty | isValidDetailed | srid | hZ | hM | length | wkt |
|---|---|---|---|---|---|---|---|
| 0 | 0 | 24404: Not valid because polygon ring (1) intersects itself or some other ring. | 0 | 0 | 0 | 40.03414 | POLYGON ((0 0, 10 0, 10 11, 11 10, 0 10, 0 0)) |
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
| isValid | wktR | isValidR |
|---|---|---|
| 1 | LINESTRING (0 0, 1 0, 1.001 1, 1 0, 2 0) | 24413: Not valid because of two overlapping edges in curve (1). |
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.