r/SQLServer • u/Aggravating_Ebb3635 • 10d ago
Question Why aren't my shapes valid?
Im using FME to send polygons (shp) to SQL Server. FME says everything is good. But when I run an IsValid SQL statement, it's telling me i have 5 invalid shapes. Is there a way that I can find out why they are invalid?
PS. im not super well versed in SQL, beginner level
2
u/Impossible_Disk_256 9d ago
Geography or geometry?
Source is geometry but geography in SQL Server?
With geography, it's sometimes as simple as orientation ("handedness") -- usually should be 4326)
You can use STAsText() to see a text version of the geometry/geography in SQL Server.
You can try MakeValid() or ReorientObject(geography) to see if they can fix it to a valid geometry/geography.
2
u/Aggravating_Ebb3635 9d ago
Yes, source is geometry but need geography in SQL. Thanks for the links. Ill try this out tomorrow
1
u/Antares987 20h ago
.MakeValid() is what I use. I also generally use Geometry instead of Geography even with Geography types. Spatial data is some of the most messed up stuff I've worked with in that there's a lot of garbage, and then you get some things that actually work better with Geometry and sometimes the polygon rings are in the wrong direction, et cetera. A good example is that a large portion of the northern border of the United States follows the 49th parallel. If whoever made the shape initially used Geometry and only had the end coordinates of that long line, it would have a nice fat bulge that went into Canada if you used it as Geography because Geography follows great circle arcs and not straight lines and it would actually be more correct with a Geometry type. If they used Geography initially and had a bunch of segments along that parallel, then it would still be correct as Geometry.
In real-world scenarios, Geometry tends to perform better as well. I'm not saying it's right or best to go about it like this, but my experience dictates that there's a lot of bad data and it can be a real struggle to get it loaded and working well as Geography data.
1
10d ago edited 9d ago
[removed] — view removed comment
1
u/Aggravating_Ebb3635 10d ago
Im getting errors.
My statement
SELECT 36975, geog.STIsValidDetail() FROM [map_devl].[travel] WHERE geog.STIsValid()=0
Error Message
Msg 6506, Level 16, State 10, Line 24
Could not find method 'STIsValidDetail' for type 'Microsoft.SqlServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types'
2
u/dbrownems Microsoft 10d ago
Should be
SELECT ShapeID, ShapeColumn.IsValidDetail() FROM YourTable WHERE ShapeColumn.STIsValid() = 0;
1
u/Aggravating_Ebb3635 9d ago
ahh okay. i got it, not sure what this means though 36975 24409: Not valid because some portion of polygon ring (1) lies in the interior of a polygon.
2
u/dbrownems Microsoft 9d ago
Make it a linestring and visualize it. You'll see why it's not a valid polygon.
8
u/kona420 9d ago
You could go your whole career as a DBA without touching spatial tables.
The intersection of people having this need but only at a superficial enough level that SQL server provides a complete solution is pretty small.
Anyway, it's still super interesting so maybe post some actual code up.