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;