SQL Server Spatial Geometry Validity problem

Question

SQL Server Spatial in RDS ....

SELECT @@version
GO
Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) Jan 23 2020 21:00:04 Copyright (C) 2017 Microsoft Corporation Web Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

I have a table with points that have been buffered to single polygon multipolygons (processing done using FME). The polygons do not contain any CircularStrings.

I test the buffered points as follows:

select distinct a.geom.IsValidDetailed() as vr
  from [dbo].[vw_search_criteria] as a
 where a.search_type in (3);

24400: Valid

I then execute a access method on the actual geometry objects as follows:

select [search_criteria_pk_id],a.geom.STNumGeometries() as vr
  from [dbo].[vw_search_criteria] as a
 where a.search_type in (3)
 order by [search_criteria_pk_id];
Msg 6522, Level 16, State 1, Line 11
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.STNumGeometries()
.

Completion time: 2023-01-30T14:01:51.0891787+11:00

I then wrote a stored procedure that executed the STNumGeometries method on each individual geometry inside a TRY CATCH to discover which geometry is failing. The procedure runs through to completion without error.

I create a layer over the data in qGIS and it occasionally throws the same MakeValid error.

I then export the data into a SQL script and load it in to PostGIS. The data passes all tests.

I then load the data back into a local copy of SQL Server:

select @@version
GO
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22621: ) (Hypervisor)

The data passes all the validation tests that failed in the first (RDS) database.

I know this is difficult, but does anyone have any suggestions as to how I can get to the bottom of why the data is reporting as invalid in one database but not in another?

Simon

Answer 1

There have been limitations in older SQL Server versions regarding geometry/geography, e.g. "Left-hand" rule was strict, a geo can max. "half-earth" in size etc.

Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.

As the message says ....