geometry STArea: Measure the Polygon Interior

geometry STArea measures the polygon interior rather than the length of its outline. I retain both measurements when a result needs to distinguish area, perimeter and an interior hole.

An outline and its interior have different units

STArea returns a float in square coordinate units. STLength on a polygon returns its perimeter. These measurements describe different properties. A longer outline doesn’t automatically establish a larger interior area.

The example uses local planar geometry with spatial reference identifier zero. It deliberately avoids geographic coordinates and Earth-surface units. An application must define what one coordinate unit means. Area then follows that unit squared, rather than an assumed square metre.

I’d label a reported quantity as area or perimeter explicitly. A generic Size column can hide the distinction. The input shape and its units belong beside that label. Correct arithmetic over the wrong unit can still produce an unusable report.

Compare simple shapes and a hole

A three-by-two rectangle and a right triangle with legs four and three both have area six. Their perimeters differ. The rectangle’s perimeter is ten, while the triangle’s is twelve. The query keeps both quantities beside the case label.

Another polygon has a four-by-four outer ring and a two-by-two interior hole. Its area is twelve after subtracting the hole. The perimeter includes both rings and is twenty-four. The original literal lists the outer and inner ring separately.

A line and an empty polygon complete the five cases. Both have zero area. The line still has a length of three. Those results separate a zero-area shape from an absent SQL value. No NULL replacement is used.

WITH Inputs AS
(
    SELECT Id, CAST(CaseLabel AS varchar(20)) AS CaseLabel,
           geometry::STGeomFromText(ShapeText, 0) AS Shape
    FROM (VALUES
        (1, 'Rectangle', N'POLYGON((0 0, 3 0, 3 2, 0 2, 0 0))'),
        (2, 'Triangle', N'POLYGON((0 0, 4 0, 0 3, 0 0))'),
        (3, 'With hole', N'POLYGON((0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 1 3, 3 3, 3 1, 1 1))'),
        (4, 'Line', N'LINESTRING(0 0, 3 0)'),
        (5, 'Empty polygon', N'POLYGON EMPTY')
    ) AS v(Id, CaseLabel, ShapeText)
)
SELECT Id, CaseLabel, CAST(Shape.STArea() AS decimal(12,3)) AS Area,
       CAST(Shape.STLength() AS decimal(12,3)) AS BoundaryLength
FROM Inputs
ORDER BY Id;

Keep ring structure intentional

An interior hole removes covered area while introducing another boundary. That is why area and perimeter move differently. The hole belongs inside the outer ring in this valid literal. The article doesn’t demonstrate repairing invalid or intersecting rings.

I can justify showing an outer bounding rectangle during an investigation. Its area is another property rather than the polygon’s occupied area. A hole or irregular boundary can make those values differ. Don’t silently substitute a bounding area for the actual method result.

Empty instances and zero- or one-dimensional figures have zero area. That outcome isn’t necessarily a failed call. It follows the shape’s dimension. A consumer needing only polygons should establish that input contract separately.

Compare each shape with both measurements

The native methods return float, and the script casts both displayed measurements to decimal(12,3). That width is sufficient for these integer-coordinate examples. It isn’t an unrestricted precision or range claim. A different coordinate domain needs its own review.

The complete CTE reads five literal shapes and the SELECT creates no objects or settings. ORDER BY fixes their case sequence. The copied polygon text retains the hole coordinates. No visual diagram is needed to reconstruct the input.

Compare all five labels, areas, perimeters and SQL types. Keep both equal-area shapes and the hole case. A matching total area would miss a wrong ring or perimeter interpretation. Retain complete tuples beside the literal shape descriptions.

Label every measurement, and area and perimeter stop trading places.

A polygon outline is not its occupied area, it is a boundary around the interior and its holes.

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