2

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.

  • Did you try Reducing the number of points ? – Kirk Kuykendall Feb 11 at 23:17
  • Could you try to contact Microsoft for opening a bug report? I don't see anything wrong in what you are doing. – user30184 Feb 12 at 6:39
  • @KirkKuykendall I haven't tried simplifying. That may very well work, but I don't really have the option of doing that with production data, so it would have to be done every time I want to run this query, which unfortunately won't be sustainable. – AJSmyth Feb 12 at 17:39
  • Did you try busting the multipolygons into polygons? – Kirk Kuykendall Feb 12 at 22:41

Your Answer

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

Browse other questions tagged or ask your own question.