geometry STUnion: Merge the Shared Area Only Once

geometry STUnion combines covered points rather than adding the separate area measurements. I check overlapping and identical inputs before treating an area sum as total coverage.

Choose combined coverage rather than an arithmetic total

STUnion returns a geometry representing the union of two instances. That result is another spatial value. It isn’t simply the sum of their STArea outputs. A shared region is included once in combined coverage.

This distinction matters whenever shapes overlap. Adding each separate area counts the shared portion twice. Identical shapes make the problem more obvious. The arithmetic sum doubles one shape, while the union still covers the same region.

I’d decide whether a report asks for total recorded allocations or distinct covered area. An arithmetic sum can intentionally represent allocated amounts that overlap. A union answers the coverage question instead. Neither operation should silently stand in for the other.

Compare three rectangle relationships

The first rectangle is a two-by-two square with area four. Three second rectangles create overlapping, separate and identical cases. Each second rectangle also has area four. All instances use spatial reference identifier zero.

The overlapping pair shares a one-by-one square. Their separate area sum is eight, but their union area is seven. The separate pair has no shared area and a union area of eight. The identical pair has a union area of four.

The query retains both individual areas and their arithmetic sum beside the union area. Every measurement is displayed as decimal(12,3). Those chosen values avoid a complicated coordinate representation. The complete literal polygons remain in the copyable SQL.

WITH Inputs AS
(
    SELECT Id, CAST(CaseLabel AS varchar(20)) AS CaseLabel,
           geometry::STGeomFromText(
               N'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0) AS A,
           geometry::STGeomFromText(SecondText, 0) AS B
    FROM (VALUES
        (1, 'Overlap', N'POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))'),
        (2, 'Separate', N'POLYGON((3 0, 5 0, 5 2, 3 2, 3 0))'),
        (3, 'Identical', N'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))')
    ) AS v(Id, CaseLabel, SecondText)
)
SELECT Id, CaseLabel, CAST(A.STArea() AS decimal(12,3)) AS AreaA,
       CAST(B.STArea() AS decimal(12,3)) AS AreaB,
       CAST(A.STArea() + B.STArea() AS decimal(12,3)) AS SumAreas,
       CAST(A.STUnion(B).STArea() AS decimal(12,3)) AS UnionArea
FROM Inputs
ORDER BY Id;

Keep geometry identity and units explicit

STUnion returns NULL when the two spatial reference identifiers do not match. This demonstration keeps them equal rather than changing that property to bypass a mismatch. A real application must establish that both shapes use the same coordinate contract.

The coordinates represent a simple local plane. The article doesn’t infer metres from the identifier zero. Its areas use square local units. Geographic surface calculations require a suitable model and another unit contract.

I can justify inspecting the generated union shape during diagnosis. Its exact ring text isn’t the narrow claim tested here. The output checks covered area for these rectangles. Different valid spatial representations should not be mistaken for different coverage merely because their text differs.

Retain the cases that expose duplicate coverage

The identical-input case prevents a naive area sum from appearing sufficient after one successful disjoint test. The overlap case checks the intermediate amount. All three relationships belong in the result contract. A single returned polygon doesn’t establish the intended coverage.

The script is a CTE and SELECT over literal shapes. It creates no objects or session changes. ORDER BY fixes the three-case sequence. The original inputs remain available without requiring a diagram or a production spatial index.

Compare every complete label and numeric tuple with its SQL types. Keep all four area columns together. Matching only the sum would not check STUnion. Retain the individual shapes and relationship labels when checking overlapping and identical coverage.

Draw two overlapping squares once and the double count is easy to see.

An area sum is not distinct coverage, it is a total that can count overlap twice.

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