I have an application where the user draws zones and later I check if a polyline crosses them.
All of a sudden the application crashed out with the error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
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.SqlGeography.STIntersects(SqlGeography other)
I was surprised at the message 'Use MakeValid' as I AM using 'MakeValid' as shown below:
Select ZonePolygonId, ZoneName, isHome FROM dbo.SpatialZonePolygons
WHERE Coordinates.STIntersects(geography::STGeomFromText('LINESTRING(51.15826 -0.18398, 51.15855 -0.18404, 51.15883 -0.18414, 51.15903 -0.18427, 51.15915 -0.18437, 51.15922 -0.1845, 51.15918 -0.18493, 51.15882 -0.18748, 51.15975 -0.18783, 51.15994 -0.18793, 51.16056 -0.18846, 51.16055 -0.1885, 51.16054 -0.1886, 51.16057 -0.18877, 51.16061 -0.18884, 51.16067 -0.18888, 51.16072 -0.18889, 51.16078 -0.18888, 51.16086 -0.18878, 51.1609 -0.18861, 51.16087 -0.18843, 51.16085 -0.1884, 51.16175 -0.18677, 51.16203 -0.18625, 51.16227 -0.18587, 51.16246 -0.18566, 51.16263 -0.18552, 51.16319 -0.18513, 51.16333 -0.18502, 51.16351 -0.18481, 51.16362 -0.18462, 51.16371 -0.18437, 51.1638 -0.18386, 51.1643 -0.18029, 51.16466 -0.17755, 51.16466 -0.17715, 51.16458 -0.17674, 51.16441 -0.17635, 51.16414 -0.17593, 51.16386 -0.17558, 51.16367 -0.17538, 51.16369 -0.17534, 51.16372 -0.17524, 51.16371 -0.17514, 51.16369 -0.17505, 51.16365 -0.17498, 51.16359 -0.17494, 51.16354 -0.17493, 51.16351 -0.17494, 51.16348 -0.17482, 51.16346 -0.17473, 51.16341 -0.17459, 51.16278 -0.17353, 51.16262 -0.17324, 51.16255 -0.17308, 51.16254 -0.17298, 51.16256 -0.17275, 51.16282 -0.17248, 51.16305 -0.1723, 51.16321 -0.17222, 51.16334 -0.17219, 51.16347 -0.17219, 51.16367 -0.17225, 51.16385 -0.17237, 51.16403 -0.17256, 51.16427 -0.17292, 51.16459 -0.17345, 51.16476 -0.17363, 51.16665 -0.17706, 51.16728 -0.17817, 51.16728 -0.17823, 51.1673 -0.17835, 51.16734 -0.17842, 51.16741 -0.17851, 51.16747 -0.17854, 51.16756 -0.17853, 51.16765 -0.17845, 51.16771 -0.17831, 51.16772 -0.17815, 51.16771 -0.17807, 51.16776 -0.17743, 51.16798 -0.1769, 51.16831 -0.17611, 51.16848 -0.17578, 51.16881 -0.17529, 51.16925 -0.17463, 51.16976 -0.17384, 51.17095 -0.17214, 51.171 -0.17225, 51.17097 -0.17278, 51.17131 -0.1729, 51.17149 -0.17297, 51.17161 -0.17296, 51.1719 -0.17276, 51.172 -0.17265, 51.17208 -0.17246, 51.1722 -0.17178, 51.17225 -0.17151, 51.17229 -0.17143, 51.17241 -0.17132, 51.17272 -0.17129, 51.17297 -0.17124, 51.17332 -0.17118, 51.17341 -0.17118, 51.17347 -0.17124, 51.17352 -0.17136, 51.17368 -0.17199', 4326).MakeValid())>0
I later found the offending polygon (see image) has been drawn pretty badly and I guess this is causing the issue.
So my specific questions are:
Many thanks.
OK, so the issue was indeed an invalid polygon zone held in the database.
The 'Use MakeValid' in the error message triggered some wrong assumptions as I was already using MakeValid. Actually this error was triggered from an invalid Polygon zone and not the PolyLine I was using in the query.
To protect against this I have added 'MakeValid()' to the INSERT SQL Statement so that no invalid polygons can ever exist within the database again.
I have tested inserting an invalid polygon with and without 'MakeValid' within the INSERT statement and can confirm adding 'MakeValid' does resolve the issue.
INSERT INTO SpatialZonePolygons (ZoneName,Coordinates) VALUES ('ValidZone',geography::STGeomFromText('POLYGON([SqlFormattedCoordinates here])', 4326).MakeValid())