6

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.

Badly Drawn Polygon

So my specific questions are:

  1. Am I using MakeValid correctly? I thought MakeValid() would resolve this kind of thing...
  2. Obviously I have no control over the drawing skills of my client so if MakeValid doesn't help for badly drawn polygons, is there some other way of er... making this valid?

Many thanks.

ChrisCurrie
  • 1,589
  • 6
  • 15
  • 36
  • 1
    Should it be `geometry::STGeomFromText` instead of `geography::STGeomFromText`? Also, the `LINESTRING` value seems to missing a closing parenthesis. – Jeff Ogata Sep 25 '14 at 14:36
  • Thanks @adrift. Yes the parenthesis is the issue here. It appears there is something in the code that creates the query. Something about this polygon... some kind of truncation is occurring forcing the last parenthesis to be cut off. That explains why the other zones did not suffer. It appears I am on a different hunt now. – ChrisCurrie Sep 25 '14 at 16:18
  • OK, I think the problem is the rogue polygon has been saved to the database in an invalid state. The MakeValid in this query doesn't change the fact it is querying against an invalid zone. I have deduced that I must ensure the zone is in a valid state before it is inserted into the database using MakeValid on the insert first. – ChrisCurrie Sep 25 '14 at 16:56

1 Answers1

12

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())
NoWar
  • 36,338
  • 80
  • 323
  • 498
ChrisCurrie
  • 1,589
  • 6
  • 15
  • 36