geometry STDifference: Keep the Subtraction Direction

geometry STDifference depends on which shape invokes the method. I compare retained points as well as area because equal remaining areas can describe different regions.

Name the shape that remains

STDifference returns the points of the invoking geometry that aren’t within the supplied geometry. A minus B therefore starts from A. Reversing the method call starts from B. The two results need not describe the same place.

The method returns another geometry value rather than an arithmetic number. STArea can measure that generated value afterward. An equal area doesn’t prove equal coverage. A comparison needs a property suited to the actual question.

I’d name a subtraction result after its direction. A label such as DifferenceArea alone can conceal which region was removed. The example calls its outputs AMinusBArea and BMinusAArea. Interior membership checks also identify which remaining region contains each test point.

Use overlapping squares with equal areas

A is a two-by-two square beginning at the origin. B is another two-by-two square shifted one unit right and up. They overlap by one square unit. Each subtraction is expected to retain three square units.

The query supplies four test points. One lies only in A, another only in B, and one lies inside their overlap. The fourth lies outside both. These points are deliberately away from boundaries so that this example stays focused on subtraction direction.

The retained areas repeat beside each point, while two bit outputs test membership in the respective difference shapes. The first point belongs only to A minus B. The second belongs only to B minus A. The overlap point belongs to neither remainder.

WITH Shapes AS
(
    SELECT geometry::STGeomFromText(
        N'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0) AS A,
        geometry::STGeomFromText(
        N'POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0) AS B
), Points AS
(
    SELECT Id, CAST(PointText AS nvarchar(30)) AS PointText
    FROM (VALUES (1, N'POINT(0.5 0.5)'), (2, N'POINT(2.5 2.5)'),
                 (3, N'POINT(1.5 1.5)'), (4, N'POINT(4 4)')) AS v(Id, PointText)
)
SELECT p.Id, p.PointText,
       CAST(d.AMinusB.STArea() AS decimal(12,3)) AS AMinusBArea,
       CAST(d.BMinusA.STArea() AS decimal(12,3)) AS BMinusAArea,
       d.AMinusB.STContains(n.TestPoint) AS InAOnly,
       d.BMinusA.STContains(n.TestPoint) AS InBOnly
FROM Shapes AS s
CROSS JOIN Points AS p
CROSS APPLY (VALUES (s.A.STDifference(s.B), s.B.STDifference(s.A)))
    AS d(AMinusB, BMinusA)
CROSS APPLY (VALUES (geometry::STGeomFromText(p.PointText, 0))) AS n(TestPoint)
ORDER BY p.Id;

Area alone can hide the directional result

Both remainder areas equal three in this carefully balanced example. A check comparing only those numbers would therefore miss a reversed call. The point tests expose the different coverage. Keep them beside the numeric measurement when reviewing the direction.

I can justify examining polygon text or a rendered shape while investigating a real input. Those representations need their own comparison rules. The narrow example checks selected interior points and area. It doesn’t claim they prove arbitrary spatial equivalence.

These methods return NULL when the spatial reference identifiers don’t match. Both literal polygons and all points use identifier zero here. Matching that identifier is necessary for these comparisons. The example doesn’t assign geographic units or transform coordinate systems.

Keep set subtraction separate from scalar subtraction

Subtracting B’s area from A’s area would produce zero because their individual areas match. That arithmetic isn’t the area of A minus B. Only the shared region is removed from A’s coverage. The geometry operation and numeric subtraction answer different questions.

The complete script reads literal polygons and points through CTEs. It creates no objects or session changes. ORDER BY fixes the four-point sequence. All generating coordinates remain copyable for reconstruction of the selected shapes.

Compare every full area and membership tuple with its SQL type. Keep both point-membership columns beside the area pair. Matching areas alone cannot establish that the invoking and supplied shapes were kept in the intended order. Retain the selected point coordinates during that check.

Name the direction, and the remainder keeps its meaning.

An equal area is not an equal region, it is a number that can hide a reversed subtraction.

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