geometry STEquals: Equal Shapes Can Have Different Text

geometry STEquals compares the represented point set, which can stay the same when its text representation changes. I separate that question from byte-for-byte text identity. Reversing a line does not necessarily change the places it occupies.

A spatial value describes more than one possible textual spelling. Coordinate order and component structure can differ while representing the same set of points. A text comparison can therefore reject a valid shape match.

Compare three complete line pairs

The first pair describes the same horizontal line in opposite directions. The second compares that line with two component lines covering the same path. The final pair introduces a different endpoint and shape.

Every geometry uses spatial reference identifier zero. The query returns each input type, a complete text-byte comparison and STEquals. It avoids depending on one particular whitespace style in a serialized text value.

All inputs are fixed, valid planar geometries. The query constructs them in expressions rather than stored tables. It changes no data, coordinate system configuration or session setting.

WITH Inputs AS
(
    SELECT CaseId, CaseName, LeftWkt, RightWkt
    FROM (VALUES
        (1, CAST('Reversed' AS varchar(20)), CAST('LINESTRING(0 0, 2 0, 4 0)' AS varchar(200)), CAST('LINESTRING(4 0, 2 0, 0 0)' AS varchar(200))),
        (2, 'Subdivided', 'LINESTRING(0 0, 2 0, 4 0)', 'MULTILINESTRING((4 0, 2 0), (0 0, 2 0))'),
        (3, 'Different shape', 'LINESTRING(0 0, 2 0, 4 0)', 'LINESTRING(0 0, 4 1)')
    ) AS v(CaseId, CaseName, LeftWkt, RightWkt)
), Shapes AS
(
    SELECT CaseId, CaseName, geometry::STGeomFromText(LeftWkt, 0) AS LeftShape,
           geometry::STGeomFromText(RightWkt, 0) AS RightShape
    FROM Inputs
)
SELECT CaseId, CaseName, LeftShape.STGeometryType() AS LeftType,
       RightShape.STGeometryType() AS RightType,
       CASE WHEN DATALENGTH(LeftShape.STAsText()) = DATALENGTH(RightShape.STAsText())
             AND CONVERT(varbinary(max), LeftShape.STAsText()) = CONVERT(varbinary(max), RightShape.STAsText())
            THEN 1 ELSE 0 END AS TextBytesEqual,
       LeftShape.STEquals(RightShape) AS SamePointSet
FROM Shapes
ORDER BY CaseId;

Read shape equality separately from spelling

The reversed pair should have different text bytes and equal point sets. Both instances are LineString values. Their traversal order differs, but the occupied horizontal line remains the same.

The subdivided pair should also have equal point sets with different text. Its types are LineString and MultiLineString. The two components together cover the entire original line without introducing a new occupied point.

The final pair should return zero for point-set equality. Its tilted line occupies different points from the horizontal input. Different text alone could not explain whether it was this genuine difference or merely another representation.

The text diagnostic compares byte length and binary content together. It is an identity check for the exported text, not a spatial predicate. Keeping both columns makes their different contracts visible.

Define the equality your application needs

STEquals returns one when both geometry instances represent the same point set. It is not a promise that their component order, vertex lists or text exports match. Applications that care about those details need a different contract.

A route can carry direction or ordered stops beyond its occupied path. Reversing that route might change its business meaning despite geometric equality. Do not discard meaningful directional attributes merely because this predicate returns one.

The same caution applies to separately stored components. Two geometries can represent equal occupied points while their organization differs. Decide whether that organization is part of the record identity before deduplicating data.

The method returns NULL when the spatial reference identifiers differ. All pairs here deliberately have matching identifiers. A missing result from an incompatible reference is not the same outcome as a populated zero for different shapes.

Keep the bounded example within its limits

These coordinates describe a simple plane, not a geographic latitude-longitude calculation. The example has no distance-unit or measurement claim. It only compares the point sets represented by the constructed geometry values.

I don’t treat shape equality as evidence that every stored attribute is equivalent. Compare the business key and other required fields separately. A matching shape can belong to two different records legitimately.

The batch includes one equal reversed pair, one equal differently structured pair and one unequal pair. That combination checks more than identical literals would. It also shows why text inequality cannot substitute for shape inequality.

No execution plan or spatial-index benefit is claimed here. Larger matching tasks need suitable data and their own plan review. Establish the logical equality requirement first so any later optimization preserves the intended record population.

Choose point-set equality when shape equality is the requirement, and keep text identity as a separate comparison.

STEquals is not a text comparison, it is a check that two shapes cover the same points.

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