geometry STIntersection: Return the Shared Shape

geometry STIntersection returns the shared shape rather than only an intersection flag. I inspect its type and emptiness alongside its area. A shared edge has zero area but still contains points.

Two regions can share an area, meet at a boundary or remain separated. Those cases produce different intersection shapes. Treating every zero-area result as no intersection would hide boundary contact.

Use three relationships to one rectangle

The base rectangle extends from zero to four horizontally and zero to two vertically. The first comparison rectangle overlaps its right half. The second begins exactly at the base rectangle’s right edge.

The final rectangle begins one coordinate unit beyond that edge. Every instance uses reference identifier zero and valid polygon text. The query computes a new SharedShape for each pair.

SharedKind displays the nonempty geometry type or the label Empty. The separate IsEmpty flag makes that label’s meaning explicit. Area is returned with three fractional digits for a compact comparison.

WITH Inputs AS
(
    SELECT CaseId, CaseName, OtherWkt
    FROM (VALUES
        (1, CAST('Area overlap' AS varchar(20)), CAST('POLYGON((2 0, 6 0, 6 2, 2 2, 2 0))' AS varchar(200))),
        (2, 'Shared edge', 'POLYGON((4 0, 6 0, 6 2, 4 2, 4 0))'),
        (3, 'Separated', 'POLYGON((5 0, 7 0, 7 2, 5 2, 5 0))')
    ) AS v(CaseId, CaseName, OtherWkt)
), Shapes AS
(
    SELECT CaseId, CaseName,
           geometry::STGeomFromText('POLYGON((0 0, 4 0, 4 2, 0 2, 0 0))', 0) AS BaseShape,
           geometry::STGeomFromText(OtherWkt, 0) AS OtherShape
    FROM Inputs
), Shared AS
(
    SELECT CaseId, CaseName, BaseShape.STIntersection(OtherShape) AS SharedShape
    FROM Shapes
)
SELECT CaseId, CaseName,
       CASE WHEN SharedShape.STIsEmpty() = 1 THEN 'Empty' ELSE SharedShape.STGeometryType() END AS SharedKind,
       SharedShape.STIsEmpty() AS IsEmpty,
       CAST(SharedShape.STArea() AS decimal(12,3)) AS SharedArea
FROM Shared
ORDER BY CaseId;

Read area overlap, contact and separation

The overlapping pair should return a Polygon with area four. Their shared rectangle extends from two to four horizontally and retains the full height of two. That is a two-dimensional common region.

The adjacent pair should return a LineString with area zero. The shared edge extends vertically at horizontal coordinate four. IsEmpty remains zero because that line contains points.

The separated pair should have IsEmpty one and area zero. SharedKind is the query’s Empty label for that outcome. It represents an existing empty result rather than a shared boundary.

The two zero-area rows therefore communicate different relationships. The geometry type and emptiness flag keep them distinguishable. Area alone is not enough to classify the result population.

The method constructs geometry

STIntersection returns a geometry instance representing the points common to both inputs. A caller can inspect or use that resulting instance further. Its type need not be the same as either input type.

For polygons, a shared area can remain polygonal while a shared boundary becomes a lower-dimensional shape. More complex inputs can also produce collections. Do not declare every intersection result to be a polygon without checking.

This example does not promise a particular vertex order or text spelling for the returned shape. Its witnesses are type, emptiness and area. Equivalent geometry can have more than one textual representation.

The two instances must have matching spatial reference identifiers. A mismatch returns NULL from this method. These fixed inputs avoid that incompatible-reference case rather than interpreting it as separation.

Use the result within a defined coordinate model

The coordinates are planar values with an agreed abstract unit. The numeric area therefore uses squared coordinate units. The query does not convert them into square meters or infer a geographic reference.

I don’t treat a geometric intersection as an automatic business decision. A service boundary may have additional rules about touching edges or tolerance. Define those rules before turning a geometry result into an acceptance flag.

All input geometries here are valid and small. The example is not a repair procedure for invalid spatial data. Inspect and validate real inputs separately before applying methods that depend on valid geometry.

The complete three-row result checks area overlap, boundary contact and separation. Keep those relationships in an adapted test set. A sample containing only overlapping interiors cannot establish what a zero-area result means.

Want to run it yourself? Download the complete SQL example.

Inspect the returned geometry and its dimension, because a zero area can describe contact rather than an empty intersection.

A zero area is not an empty intersection, it is what a shared edge looks like.

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