I've got a whole load of WKT data in a SQL Server table, of which I'm trying to detect the validity.
I'm currently doing a query like this:
UPDATE f SET f."CurrentGeomValid" = geometry::STGeomFromText(f."GEOM_CURRENT",29900).STIsValid() FROM "Feature" f WHERE f."CurrentGeomValid" IS NULL;
(Basically updating a column with the geometry validity). I'm hitting an issue when the WKT is completely invalid, for example:
POLYGON ((0 0, 10 10, 0 0) (100 100, 200 100, 200 200, 100 200, 100 100))
The initial loop of this polygon doesn't have enough points and gets me a message like so:
Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24305: The Polygon input is not valid because the ring does not have enough distinct points. Each ring of a polygon must contain at least three distinct points. System.FormatException: at Microsoft.SqlServer.Types.Validator.Execute(Transition transition) at Microsoft.SqlServer.Types.ForwardingGeoDataSink.EndFigure() at Microsoft.SqlServer.Types.OpenGisWktReader.ParseLineStringText() at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePolygonText() at Microsoft.SqlServer.Types.OpenGisWktReader.ParseMultiPolygonText() at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type) at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid) at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
The issue seems to be that the STGeomFromText function is failing to read the invalid geometry so that the STIsValid function can tell me that it's invalid!
I can certainly write some .NET code with an exception handler and process my dataset row by row.
Is there a way of doing this in a single query?