geometry STIsClosed and STIsSimple: Ask Both Questions

geometry STIsClosed checks whether a line returns to its starting point. I ask a separate simplicity question when intersections matter to the intended operation.

Separate two spatial properties

A line can return to its start and still cross itself elsewhere. Closing its endpoints doesn’t remove that crossing. The two properties need different expressions. One true bit shouldn’t stand in for the other question.

STIsClosed asks whether the start and end points coincide. STIsSimple follows the Open Geospatial Consortium simplicity rules. Both methods return bit values. Their similar names don’t make their meanings interchangeable.

The simplicity rules allow a figure to meet itself at its endpoints. An intersection elsewhere can prevent simplicity. I’d retain that qualification instead of claiming every meeting point is forbidden. A normal closed triangular line illustrates why endpoint contact matters.

Compare three complete line descriptions

The first line runs from zero-zero through two-zero to two-two. Its endpoints differ, and its segments do not cross. It is therefore expected to be open and simple. Both properties remain beside its case label.

The second line forms a triangular loop and returns to zero-zero. Its endpoint meeting closes the line without an additional crossing. The third traces a crossing loop before returning to zero-zero. That row separates closure from simplicity.

All instances are literal LineStrings with spatial reference identifier zero. The script uses a CTE and one SELECT. It creates no tables and changes no connection options. ORDER BY preserves the deliberate open, closed and crossing sequence.

WITH Lines AS
(
    SELECT Id,CAST(CaseLabel AS varchar(20)) AS CaseLabel,
           geometry::STGeomFromText(LineText,0) AS Shape
    FROM (VALUES
        (1,'OpenSimple','LINESTRING(0 0,2 0,2 2)'),
        (2,'ClosedSimple','LINESTRING(0 0,2 0,1 2,0 0)'),
        (3,'ClosedCrossing','LINESTRING(0 0,2 2,0 2,2 0,0 0)')
    ) AS v(Id,CaseLabel,LineText)
)
SELECT Id,CaseLabel,Shape.STIsClosed() AS IsClosed,
       Shape.STIsSimple() AS IsSimple
FROM Lines
ORDER BY Id;

Read both bits in the crossing row

The crossing loop is expected to return true for closure and false for simplicity. Its first and last coordinates still agree. Two internal segments meet at another location. The true closure result doesn’t override that intersection.

Likewise, the open line is expected to be simple despite its false closure result. A process that needs an open path could accept that property intentionally. A process requiring a closed boundary asks another question. The method alone cannot choose the business requirement.

The triangular line gives a true result for both properties. That ordinary success is useful beside the counterexample. Without the crossing row, the outputs might look interchangeable. Keep the differing row whenever this distinction is tested.

Do not turn this pair into a complete validity policy

The query doesn’t claim these two bits certify every required spatial rule. Simplicity and closure have defined meanings. Other validity or application requirements can still matter. A familiar-looking line isn’t proof that every relevant check has passed.

This example remains limited to LineStrings. Points, empty instances, polygons and collections have their own closure rules. A rule for one shape type shouldn’t be casually applied to every instance. Extend the test inputs before extending the claim.

The coordinates describe a local plane. They don’t encode a physical route or a geographic surface. The query makes no navigation or indexing claim. Its purpose is to keep two geometry properties separate in a small reproducible comparison.

Keep a complete result contract

Compare every complete label and both bits across all three ordered tuples. Keep the closed crossing line beside the closed simple line. A count of closed rows wouldn’t check the simplicity distinction. Preserve the internal crossing in the literal input.

I can justify exposing both flags in a diagnostic query. A single summary word would hide which condition failed. Keep the application’s acceptance rule explicit when combining them. The returned properties describe the input rather than rewriting it.

Ask both questions, and let each bit answer only its own.

Closure is not simplicity, it is an endpoint relationship that leaves other intersections to another check.

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