When trying to import a Shapefile (With a polygon completely inside another one) using Geography type, outter Polygon fails and is detected as MultiPolyline

[Danpamio]

Hey Fischer!

Found out that when I try to import the attached shapefile (made it just as an example for you) using Geography type, SRID 4326, on MS SQL Server 2016, Area 4 is perfectly imported as Geography type, but Area 5 don't, it is imported as a multi polyline and I really have no idea why.

I have already tested importing the same shapefile using Shape2SQL by Morten Nielsen (another great master!), and both areas were imported correctly as geography.

Please, feel free to contact me if you need further information.

Thank you for your great work.

xfisher_1.0.8_issue_example_ShapeFile.zip

[xfischer]

Hi @Danpamio ! Thanks for reporting this.
This project is in fact a direct inspiration of @dotMorten's Shape2SQLServer with his kind authorization. I just needed reprojection, indexes and faster imports.
I'll try to see what I can do. Thanks for providing the data.

[dotMorten]

Try my original tool. It should handle these cases
Oh wait... you already said you did :-)

[xfischer]

@Danpamio

• Can you check if your area5 is valid when you import it with @dotMorten's shape2sql, ( STIsValid() should return 1) ?
• If I declare it manually (see https://gist.github.com/xfischer/de8764b1e0d1efe20ec130caeef8ae10), then it shows up but is not valid. Reason given by SQL Server (with IsValidDetailed()) is :

24412: Not valid because the interior of a polygon with rings (2) and (1) is not connected.

Nevertheless, this is still a bug, because MakeValid() looses the shape type (Polygon) and, as you said, returns a LineString, which is not acceptable with a Polygon shape file as input.
Let me know!

[Danpamio]

Try my original tool. It should handle these cases
Oh wait... you already said you did :-)

:) Started my Georefencing work with your tool, but had a few issues when working with SQL Server 2016 and 2017 (I tried the SqlServerSpatial140.dll to SqlServerSpatial.dll renaming tip, but still..) So Im only using it with SQL Server 2008. Great work btw!

[Danpamio]

@Danpamio

• Can you check if your area5 is valid when you import it with @dotMorten's shape2sql, ( STIsValid() should return 1) ?
• If I declare it manually (see https://gist.github.com/xfischer/de8764b1e0d1efe20ec130caeef8ae10), then it shows up but is not valid. Reason given by SQL Server (with IsValidDetailed()) is :

24412: Not valid because the interior of a polygon with rings (2) and (1) is not connected.

Nevertheless, this is still a bug, because MakeValid() looses the shape type (Polygon) and, as you said, returns a LineString, which is not acceptable with a Polygon shape file as input.
Let me know!

Gotta say that I'm impressed how fast we are talking about it!

Yes, Area 5 returns valid with @dotMorten shape2sql hash and It even works using with STIntersects!
When using your tool STIntersects does not works on Area 5 coz it is just a LineString.

[xfischer]

Ok thanks for checking.
So I’ll try to see what I can do. The plan :

• understand what’s wrong with your polygon
• try to correct this very case
• if not successful, dive into NetTopology to see if the case is handled out of the box.

[Danpamio]

Awesome @xfischer!

Own you guys beers!

If you need anything from here that can help, just let me know.

[dotMorten]

It's been quite a few years since I touched Shape2Sql, but I do remember doing quite a few hoops to ensure geographies were valid before importing (and going through several hoops to auto-fix them). The problem is ShapeFiles are always flat-earth geometry, but once you pretend those coordinates are spherical, you quickly get into self-intersecting problems, or geometries spanning more than half a hemisphere etc.

[xfischer]

Thanks a lot for your input @dotMorten ! I knew there was no easy solution. The ugly trick I had was to do a tiny positive/negative buffer. But this was a last resort!
I'll let you know !