I have two sets of geometries (A and B) in SQL Server 2016. I want to determine the overlap areas of those geometries from set A that intersect set B.
Most of the time this code works:
SELECT a.geom.STIntersection(b.geom) FROM A a JOIN B b ON a.geom.STIntersects(b.geom) = 1
But in a few cases, I get the following error:
Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.Runtime.InteropServices.COMException: Error HRESULT E_FAIL has been returned from a call to a COM component. System.Runtime.InteropServices.COMException: at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo) at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode) at Microsoft.SqlServer.Types.GLNativeMethods.Combine(CombineMode combineMode, GeoData g1, GeoData g2) at Microsoft.SqlServer.Types.SqlGeometry.STIntersection(SqlGeometry other)
I have isolated the offending geometries by iterating through one at a time. It looks like the problem arises when two geometries touch but do not overlap. However, SQL shows that STTouches = 0. I suspect what is happening is that some kind of defective geometry is being created, like maybe a geometry collection?
Both geometries are valid. Adding .MakeValid() does not affect the results. Both geometries are polygons or multipolygons.
My question is, how can I prevent my query from erroring out? I am happy to just skip these cases if I can identify them somehow. I'd like to avoid iterating through all of them one by one, as this is very inefficient.