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
  • What does the relationship between these two polygons look like? Part of the multipolygon is both inside and outside of the polygon? You may want to see if exploding the shape produces the same error. You will at least isolate the part of the multipoly causing you issues. select geom.STGeometryN(numbers.numbers) from geomtable a join numbers on a.geom.STNumGeometries()<= numbers.number – Brian W. Jun 14 at 17:56

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.