0

Edit - I provided the wtk of the poly I had made valid. Updated with the invalid string. Sorry this seems to have turned by mind to mush.

I have a python process using that cuts an input dataset by a quad tree grid so that there are no polygons with more than 400 vertices. It then pushes the records into my database. The process has been working well until I pushed this row into my SQL Server Database and it tells me it is invalid geography. The SRID of the geography is 7844 (GDA 2020)

QUADDED_ID  QUAD_ID PFI wkt
23218   7023    30007035    MULTIPOLYGON (((144.56174022200003 -36.72412881799994, 144.56174022200003 -36.724121159999982, 144.51328705100002 -36.724121159999982, 144.51313968200009 -36.725504011999931, 144.50147243700007 -36.724121159999982, 144.50083983200011 -36.724121159999982, 144.50083983200011 -36.730858658999978, 144.50087837100011 -36.730936683999971, 144.50112464300003 -36.731100616999981, 144.50227852500007 -36.731738958999983, 144.50486807100003 -36.734654775999957, 144.50508163100005 -36.734664717999976, 144.505419167 -36.73462993499993, 144.50572273100011 -36.734631203999982, 144.505936291 -36.734641144999955, 144.50624993200006 -36.73482344599995, 144.50641840900005 -36.73485130499995, 144.50662201000011 -36.734662104999984, 144.50678180700004 -36.734291739999946, 144.50693174800006 -36.733704129999978, 144.50709020300008 -36.733541895999963, 144.5121397050001 -36.733345636999957, 144.51199355300002 -36.735109730999966, 144.5127053440001 -36.737877590999972, 144.51309263500002 -36.737859952999941, 144.51890573300011 -36.738540372999978, 144.51922570800002 -36.738297044999968, 144.51944567300006 -36.738041087999932, 144.52027184200006 -36.737544268999955, 144.52111774200011 -36.736574380999969, 144.52122055700011 -36.736250351999956, 144.52121145900003 -36.735020156999951, 144.52081514600002 -36.733937083999933, 144.52068924800005 -36.73258474499994, 144.52044409900009 -36.731502291999959, 144.51989732900006 -36.730310462999967, 144.51945193500012 -36.729024418999984, 144.51931859700005 -36.728861658999961, 144.51836438500004 -36.728371104999951, 144.51797981600009 -36.728099177999979, 144.51789695000002 -36.727923102999966, 144.51791639200007 -36.727504112999952, 144.5183066620001 -36.726870340999938, 144.51835882800003 -36.726586673999975, 144.51829386200006 -36.726234934999979, 144.51772661400003 -36.725637817999939, 144.52393433500004 -36.725621727999965, 144.52949935400011 -36.725616276999972, 144.53554647400006 -36.725612458999933, 144.54186909000009 -36.725609406999979, 144.54565718400011 -36.725601840999957, 144.55195207300005 -36.725625857999944, 144.56046184000002 -36.725614432999976, 144.55976377700006 -36.724121485999945, 144.56174022200003 -36.72412881799994)))

When I use the IsValidDetailed() function it returns "Not valid because some portion of polygon ring (1) lies in the interior of a polygon." I've plotted all the points and I can't see why it would be invalid. The only thing I can see is that there is a sliver that is only 4cm across, but I would have thought 4cm isn't small enough to cause an issue.

If I put it into a geometry it is valid, but I need the final result as a geography type.

Does anyone have any ideas of what the issue may be?

added code

DECLARE @g geography;  
SET @g = geography::STGeomFromText('MULTIPOLYGON (((144.56174022200003 -36.72412881799994, 144.56174022200003 -36.724121159999982, 144.51328705100002 -36.724121159999982, 144.51313968200009 -36.725504011999931, 144.50147243700007 -36.724121159999982, 144.50083983200011 -36.724121159999982, 144.50083983200011 -36.730858658999978, 144.50087837100011 -36.730936683999971, 144.50112464300003 -36.731100616999981, 144.50227852500007 -36.731738958999983, 144.50486807100003 -36.734654775999957, 144.50508163100005 -36.734664717999976, 144.505419167 -36.73462993499993, 144.50572273100011 -36.734631203999982, 144.505936291 -36.734641144999955, 144.50624993200006 -36.73482344599995, 144.50641840900005 -36.73485130499995, 144.50662201000011 -36.734662104999984, 144.50678180700004 -36.734291739999946, 144.50693174800006 -36.733704129999978, 144.50709020300008 -36.733541895999963, 144.5121397050001 -36.733345636999957, 144.51199355300002 -36.735109730999966, 144.5127053440001 -36.737877590999972, 144.51309263500002 -36.737859952999941, 144.51890573300011 -36.738540372999978, 144.51922570800002 -36.738297044999968, 144.51944567300006 -36.738041087999932, 144.52027184200006 -36.737544268999955, 144.52111774200011 -36.736574380999969, 144.52122055700011 -36.736250351999956, 144.52121145900003 -36.735020156999951, 144.52081514600002 -36.733937083999933, 144.52068924800005 -36.73258474499994, 144.52044409900009 -36.731502291999959, 144.51989732900006 -36.730310462999967, 144.51945193500012 -36.729024418999984, 144.51931859700005 -36.728861658999961, 144.51836438500004 -36.728371104999951, 144.51797981600009 -36.728099177999979, 144.51789695000002 -36.727923102999966, 144.51791639200007 -36.727504112999952, 144.5183066620001 -36.726870340999938, 144.51835882800003 -36.726586673999975, 144.51829386200006 -36.726234934999979, 144.51772661400003 -36.725637817999939, 144.52393433500004 -36.725621727999965, 144.52949935400011 -36.725616276999972, 144.53554647400006 -36.725612458999933, 144.54186909000009 -36.725609406999979, 144.54565718400011 -36.725601840999957, 144.55195207300005 -36.725625857999944, 144.56046184000002 -36.725614432999976, 144.55976377700006 -36.724121485999945, 144.56174022200003 -36.72412881799994)))', 7844);  
SELECT @g.IsValidDetailed();

returns 24409: Not valid because some portion of polygon ring (1) lies in the interior of a polygon. The problem occurs in entry (2) in a geometry collection.

Thanks

Harry Smiles
  • 23
  • 4
  • I get different results. If I do `declare @g geography = '«your multipolygon»'; select @g.IsValidDetailed();`, it comes back as valid. What does your call to `IsValidDetailed()` look like? – Ben Thul May 10 '22 at 13:12
  • Similarly, if I do `DECLARE @g GEOGRAPHY = geography::STMPolyFromText('«your multipolygon»', 7844); SELECT @g;` I get no errors. – Ben Thul May 10 '22 at 13:16
  • Sorry I pasted the poly that I had made valid (I can't actually use that as it makes too many changes). I have updated the question. – Harry Smiles May 10 '22 at 23:25
  • Ah... that makes more sense. Well, the issue is easy to describe but I don't know of an easy way to fix. The path crosses over itself. Fortunately for us, the [example](https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/isvaliddetailed-geography-data-type?view=sql-server-ver15#examples) in the docs for `IsValidDetailed()` has the same pathology that your polygon does but is a lot more simple. I'd invite you to draw out the polygon in that example with pen and paper and I think you'll see what's going on. – Ben Thul May 11 '22 at 02:21
  • The way to fix this is using the parse command as per my original answer, yes I used geometry instead of geography but both have a parse function, I've updated my answer to geography – Ockert May 11 '22 at 07:19

2 Answers2

0

Maybe try using parse? I'm unable to get the same error as it just loads on my side, tried on SQL Server 2019

edit ok parse didn't work, try the MakeValid() to gently nudge the poly to fit

DECLARE @g geography;  
SET @g = geography::STGeomFromText('MULTIPOLYGON (((144.56174022200003 -36.72412881799994, 144.56174022200003 -36.724121159999982, 144.51328705100002 -36.724121159999982, 144.51313968200009 -36.725504011999931, 144.50147243700007 -36.724121159999982, 144.50083983200011 -36.724121159999982, 144.50083983200011 -36.730858658999978, 144.50087837100011 -36.730936683999971, 144.50112464300003 -36.731100616999981, 144.50227852500007 -36.731738958999983, 144.50486807100003 -36.734654775999957, 144.50508163100005 -36.734664717999976, 144.505419167 -36.73462993499993, 144.50572273100011 -36.734631203999982, 144.505936291 -36.734641144999955, 144.50624993200006 -36.73482344599995, 144.50641840900005 -36.73485130499995, 144.50662201000011 -36.734662104999984, 144.50678180700004 -36.734291739999946, 144.50693174800006 -36.733704129999978, 144.50709020300008 -36.733541895999963, 144.5121397050001 -36.733345636999957, 144.51199355300002 -36.735109730999966, 144.5127053440001 -36.737877590999972, 144.51309263500002 -36.737859952999941, 144.51890573300011 -36.738540372999978, 144.51922570800002 -36.738297044999968, 144.51944567300006 -36.738041087999932, 144.52027184200006 -36.737544268999955, 144.52111774200011 -36.736574380999969, 144.52122055700011 -36.736250351999956, 144.52121145900003 -36.735020156999951, 144.52081514600002 -36.733937083999933, 144.52068924800005 -36.73258474499994, 144.52044409900009 -36.731502291999959, 144.51989732900006 -36.730310462999967, 144.51945193500012 -36.729024418999984, 144.51931859700005 -36.728861658999961, 144.51836438500004 -36.728371104999951, 144.51797981600009 -36.728099177999979, 144.51789695000002 -36.727923102999966, 144.51791639200007 -36.727504112999952, 144.5183066620001 -36.726870340999938, 144.51835882800003 -36.726586673999975, 144.51829386200006 -36.726234934999979, 144.51772661400003 -36.725637817999939, 144.52393433500004 -36.725621727999965, 144.52949935400011 -36.725616276999972, 144.53554647400006 -36.725612458999933, 144.54186909000009 -36.725609406999979, 144.54565718400011 -36.725601840999957, 144.55195207300005 -36.725625857999944, 144.56046184000002 -36.725614432999976, 144.55976377700006 -36.724121485999945, 144.56174022200003 -36.72412881799994)))', 7844);  
SELECT @g.MakeValid().IsValidDetailed();  
SELECT @g
Ockert
  • 425
  • 4
  • 6
  • 1
    How does this address the question? They say "If I put it into a geometry it is valid, but I need the final result as a geography type." - so how does supplying code showing no errors in `geometry` help? – Martin Smith May 10 '22 at 08:22
  • @MartinSmith, ok changed to geography, it still works, my answer was around using the parse command which would fix the error you are getting – Ockert May 11 '22 at 07:15
  • @Ockert the WKT you are using is the incorrect one I provided first sorry. If I use geography::Parse with the updated wtk it is invalid. I've ended up converting the whole thing into Geometry to deal with the issue. – Harry Smiles May 13 '22 at 02:13
  • @HarrySmiles thanks for clearing that up, in this case you can try use the [MakeValid](https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/makevalid-geometry-data-type?view=sql-server-ver15) function, I'm not 100% sure how MakeValid works its magic but test and see if it works for you. Doing an eye check and the polygons look correct. Have updated my answer – Ockert May 13 '22 at 07:19
0

It appears that there isn't an answer to this using the GDA2020 SRID. If I change it to VicGrid2020 it will work, but that can't be used for the work I'm doing. As I sort of understand it, it is an issue with greater circles. It will work with a geometry as they use planar space rather than geodesic.

I have been able to convince the downstream team to convert there code into Geometry which doesn't seem to have caused them any issues.

Harry Smiles
  • 23
  • 4