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.

Note: at the time I posted this, I have still not isolated which rows are throwing the errors, when and if I do, I will post an example — Kyle B, Jun 29 '18 at 12:44
I posted an answer below, not sure if it is the best solution possible, if anyone else can think of a better one, I will accept it instead. — Kyle B, Jun 29 '18 at 13:21
Have you looked in the process that populates the `FEATURES` table to see what is causing some rows to have invalid geometries? — Mazhar, Jun 29 '18 at 15:28
@Mazhar, It's an external process that I can't change... and I would **not** really call them invalid, they are just invalid according to SQL Server's implementation. For example: `LINESTRING(12 12 0, 12 12 5)` is invalid, that's a vertical line but SQL server thinks it should be a `POINT(12 12 0)`. So there really isn't anything I can do here except workaround it. — Kyle B, Jun 29 '18 at 15:31

score 1 · Accepted Answer · answered Jun 29 '18 at 13:19

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.

SQL Select valid Geometries without using MakeValid

1 Answers1