Detecting invalid WKT in text column in SQL Server

Question

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?

Answer 1

Here's a stored procedure that will do it, no CLR needed, unfortunately it can't be a function because it uses TRY... CATCH...

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestWKT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TestWKT]

GO
------------------------------------------
CREATE PROCEDURE TestWKT(
    @wkt varchar(max),
    @srid int) 
AS
BEGIN

SET NOCOUNT ON;

DECLARE @valid bit;

BEGIN TRY
    SET @valid = geometry::STGeomFromText(@wkt, @srid).STIsValid();
END TRY
BEGIN CATCH
    SET @valid = 0
END CATCH

SELECT @valid;

END
------------------------------------------------
GO

exec TestWKT 'garbage', 4326

exec TestWKT 'POINT(-84 32)', 4326