geometry STSymDifference: Keep the Parts Outside the Overlap

geometry STSymDifference keeps points belonging to either input while excluding their shared portion. I use it when both sides’ exclusive regions matter. Directed subtraction would answer a different question about only one side.

Two overlapping regions can leave separate pieces after their common area is removed. The output can therefore have multiple components. The method returns a geometry, not merely a yes-or-no difference flag.

Compare three rectangle relationships

The left rectangle has width four and height two, so its area is eight. The first right rectangle has the same size and overlaps half of it. The second right input is identical to the left.

The final right rectangle is separate and has area four. Every input uses reference identifier zero. The query computes symmetric difference in both operand orders for a point-set comparison.

The result columns report type, emptiness, area, component count and swapped equality. They do not depend on a particular vertex order in a text export. This is a read-only query with fixed valid shapes.

WITH Inputs AS
(
    SELECT CaseId, CaseName, RightWkt
    FROM (VALUES
        (1, CAST('Partial overlap' AS varchar(20)), CAST('POLYGON((2 0, 6 0, 6 2, 2 2, 2 0))' AS varchar(200))),
        (2, 'Identical', 'POLYGON((0 0, 4 0, 4 2, 0 2, 0 0))'),
        (3, 'Separated', 'POLYGON((6 0, 8 0, 8 2, 6 2, 6 0))')
    ) AS v(CaseId, CaseName, RightWkt)
), Shapes AS
(
    SELECT CaseId, CaseName,
           geometry::STGeomFromText('POLYGON((0 0, 4 0, 4 2, 0 2, 0 0))', 0) AS LeftShape,
           geometry::STGeomFromText(RightWkt, 0) AS RightShape
    FROM Inputs
), Exclusive AS
(
    SELECT CaseId, CaseName, LeftShape.STSymDifference(RightShape) AS ResultShape,
           RightShape.STSymDifference(LeftShape) AS SwappedShape
    FROM Shapes
)
SELECT CaseId, CaseName,
       CASE WHEN ResultShape.STIsEmpty() = 1 THEN 'Empty' ELSE ResultShape.STGeometryType() END AS ResultKind,
       ResultShape.STIsEmpty() AS IsEmpty,
       CAST(ResultShape.STArea() AS decimal(12,3)) AS ExclusiveArea,
       ResultShape.STNumGeometries() AS ComponentCount,
       ResultShape.STEquals(SwappedShape) AS SwapHasSamePointSet
FROM Exclusive
ORDER BY CaseId;

Read the exclusive area

The partially overlapping inputs share an area of four. Removing that shared region from both sides leaves two pieces totaling eight. The expected result is a nonempty MultiPolygon with two components.

Identical inputs have no exclusive portion. Their expected result is empty with area zero and no components. The Empty display label describes that returned empty geometry rather than a missing SQL value.

The separated inputs have no shared points. Both regions remain in the result, giving area twelve across two components. This includes the left area eight and right area four.

Each swapped comparison should return one for equal point sets. Symmetric difference includes exclusive portions from both inputs, so exchanging the operands preserves that set. The diagnostic does not demand identical text serialization.

Choose the correct difference question

Symmetric difference is appropriate when both exclusive populations belong in the result. A left-only removal asks for a directed difference instead. Joining both complete shapes asks for a union, which includes their overlap.

The names can sound similar while describing different sets. Write the intended relationship before choosing a method. A result with the expected area alone may still belong to the wrong operation.

The returned type can vary with the inputs. These examples use separated rectangular pieces or an empty result. More complex source shapes can require a different collection of components.

I don’t treat a component count as a business count automatically. A single business region can have several disconnected geometric pieces. Keep record identity separate from the shape’s internal organization.

Interpret the witnesses within the input model

These planar coordinates use an abstract common unit. The area is in squared coordinate units, with a display cast to three fractional digits. No physical meter or geographic-area conversion is implied.

Matching reference identifiers are required for this operation. A mismatched pair can return NULL rather than an exclusive shape. The bounded inputs deliberately avoid that incompatible-reference outcome.

The source geometries are valid. This example does not repair invalid boundaries or define tolerance for nearly coincident edges. Those requirements need their own rules before geometric results become application decisions.

Keep partial overlap, identical input and separated input in an adapted test set. Each establishes a different part of the exclusive-set contract. Comparing the swapped point set also checks that a directed operation was not substituted accidentally.

Sketch two overlapping rectangles and shade what is left. That is the whole idea.

A symmetric difference is not a subtraction, it is the parts only one shape owns.

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