SQL Select valid Geometries without using MakeValid

Question

I have a large table of data with SQLGeometry values in it. Many of the rows contain "not-well-formed" Geometries according to OGC (Geometry.STIsValid()). This causes many of my Geometries to throw an error when I check the Geometry.STGeometryType().

When I use this SQL Query I assumed incorrectly that it would leave out the invalid Geometries:

SELECT [Geometry] FROM Features 
  WHERE [Geometry] IS NOT NULL
    AND [Geometry].STIsValid() = 1
    AND [Geometry].STGeometryType() = 'Point'

The STGeometryType() function throws this error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly. System.ArgumentException: at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid() at Microsoft.SqlServer.Types.SqlGeometry.STGeometryType()

I get the same error using a sub-query:

SELECT G.* FROM (
  SELECT [Geometry] FROM Features 
    WHERE [Geometry] IS NOT NULL
     AND [Geometry].STIsValid() = 1
) AS G
   WHERE G.[Geometry].STGeometryType() = 'Point'

Using the Geometry.MakeValid() function is not a workable solution, I cannot have SQL Server arbitrarily changing my Geometries but I need to be able to tell what type they are for a View like this one:

CREATE VIEW vw_Points
AS
  SELECT [Geometry] FROM vwValidFeatures 
    WHERE [Geometry].STGeometryType() = 'Point'

Does anyone have a better solution or a workaround for this?

The best solution I can come up with is to select all the Geometries STAsText() and then parse the string because STAsText() does not throw any errors if it is invalid.

Answer 1

This is the best solution I could find by myself:

CREATE VIEW [vwValidGeometries]
AS
  SELECT A.* FROM (
    SELECT 
        Id,
        CASE [Geometry].STIsValid() 
           WHEN 1 THEN [Geometry] 
           ELSE NULL 
        END AS 'Geo'
    FROM Features
  )
    WHERE Geo IS NOT NULL

Then:

SELECT * FROM vwValidGeometries WHERE Geo.STGeometryType() = 'Point'

This isolates the STIsValid() function enough that it isn't touched by any functions that will throw an error if the geometry is invalid.

I found this answer on MSDN.