geometry STIsValid: Parsed Shapes Still Need a Validity Check

geometry STIsValid checks whether a spatial instance satisfies the validity rules for its geometry type. I use two parsed lines to separate those questions. Successful construction alone does not establish that a shape is valid.

A source string can have acceptable syntax while describing a problematic path. The second line below retraces one segment. Both rows are geometry values, but their validity flags should differ.

Keep the inspection query small

The first line travels from one upper point through a lower central point to another upper point. It never retraces a segment. The second line adds a downward excursion and returns along that same segment.

The coordinate strings are fixed inputs in a VALUES constructor. Each uses spatial reference identifier zero. The query performs no repair, updates or persistent storage changes.

InputWkt returns the supplied coordinate text rather than querying the type of an invalid instance. IsValid answers the separate validity question. Keeping both columns preserves the diagnostic input in the result.

WITH Inputs AS
(
    SELECT CaseId, CaseName, Wkt
    FROM (VALUES
        (1, 'Ordinary line', 'LINESTRING(0 4, 2 2, 4 4)'),
        (2, 'Retraced segment', 'LINESTRING(0 4, 2 2, 2 0, 2 2, 4 4)')
    ) AS v(CaseId, CaseName, Wkt)
), Shapes AS
(
    SELECT CaseId, CaseName, CAST(Wkt AS varchar(80)) AS InputWkt,
           geometry::STGeomFromText(Wkt, 0) AS Shape
    FROM Inputs
)
SELECT CaseId, CaseName, InputWkt,
       Shape.STIsValid() AS IsValid
FROM Shapes
ORDER BY CaseId;

Read the two flags

The ordinary line should return its supplied WKT and a validity flag of one. The retraced line should return its supplied WKT and zero. Acceptable coordinate syntax does not imply validity.

The central excursion creates the invalid example deliberately. Removing that excursion would change the input and eliminate the case being tested. Keep the two full source strings when reproducing this comparison.

I don’t call distance, intersection or other operations on the invalid row here. Some spatial methods require a valid instance and raise an error otherwise, even during inspection. An inspection query should avoid turning a diagnostic example into an unrelated failure.

The output includes the supplied text and both validity flags. No geometric output ordering or rewritten coordinate string is involved. The result is a small classification exercise rather than a rendering demonstration.

Treat validity as one input rule

The validity check is defined for the represented geometry type. It does not know whether a line represents a road, cable or boundary. A technically valid shape can still be wrong for that application.

For example, the first line might use the wrong coordinate system or connect the wrong locations. Its validity flag would not establish business correctness. Those requirements need additional source checks.

A NULL database value is another separate input condition. It does not represent the same problem as this populated invalid line. Keep missing inputs distinct from values that fail a shape rule.

Malformed coordinate text is also outside this demonstration. Parsing can reject text before a geometry instance exists. STIsValid is an inspection of an existing instance rather than a replacement for handling conversion failures.

Choose the next step deliberately

A production import can retain the source record and route invalid shapes for review. That preserves the information needed to explain the failure. Automatically discarding the row loses that context.

MakeValid is a different operation that returns a valid geometry. Its result can have a different type or slightly shifted points. Review the proposed result before treating it as a business correction.

The example does not establish a performance result or an indexing strategy. It only shows why construction and validity need separate checks. Keep those checks explicit when adapting the query to incoming data.

Decide how an invalid shape is handled before it reaches any spatial work.

A parsed shape is not a valid shape, it is only syntax that was accepted.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.