geometry STDimension: Count Dimensions, Not Points

geometry STDimension reports the maximum dimension of an instance. I keep that value separate from coordinate counts, area measurements and the shape name.

Identify the property before counting

A point, a line and a polygon describe different dimensional relationships. Counting their coordinate entries answers another question. A polygon’s repeated closing coordinate doesn’t add a spatial dimension. The method name should be read with that distinction in mind.

STDimension returns the maximum dimension of a geometry instance. Its native SQL result is int. It gives zero for a point, one for a line and two for a polygon. Empty instances return minus one.

I’d avoid turning the result into a coordinate-count label. The number two for a polygon doesn’t mean it contains two vertices. It describes the spatial dimension used by this method. A separate point-count method answers the description-size question.

Keep simple types and a mixed collection

The first three literal instances are a point, a three-coordinate line and a triangular polygon. They are expected to return dimensions zero, one and two. Their different coordinate counts don’t change those values. Each label remains beside the result.

The mixed collection contains a point and a line. Its maximum dimension is one. Combining two elements doesn’t add their dimensions into a total. The collection retains the higher-dimensional element’s dimension for this question.

The fifth instance is an empty geometry collection. Its dimension is expected to be minus one rather than zero. The query includes STIsEmpty for every instance. That flag distinguishes the empty row from the ordinary point row.

WITH Shapes AS
(
    SELECT Id,CAST(CaseLabel AS varchar(20)) AS CaseLabel,
           geometry::STGeomFromText(ShapeText,0) AS Shape
    FROM (VALUES
        (1,'Point','POINT(1 1)'),
        (2,'Line','LINESTRING(0 0,3 0,3 4)'),
        (3,'Polygon','POLYGON((0 0,4 0,0 3,0 0))'),
        (4,'PointAndLine','GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,3 0))'),
        (5,'EmptyCollection','GEOMETRYCOLLECTION EMPTY')
    ) AS v(Id,CaseLabel,ShapeText)
)
SELECT Id,CaseLabel,Shape.STDimension() AS MaximumDimension,
       Shape.STIsEmpty() AS IsEmpty
FROM Shapes
ORDER BY Id;

Read empty and zero as different values

The single point has dimension zero and is not empty. The empty collection has dimension minus one and is empty. A zero result therefore doesn’t mean that no geometry exists. Replacing the empty result with zero would conceal this difference.

The empty value is initialized from literal WKT. It isn’t a missing SQL value in the current inputs. Those meanings need separate handling in a broader query. The demonstration keeps the empty instance visible without inserting a display placeholder.

The empty row returns minus one, a defined value of its own. The other selected shapes provide ordinary witnesses beside it. Keep all five rows when checking a consumer’s classification. A query filtered to positive dimensions would remove both the point and empty cases.

Do not infer measurements from dimension alone

Two polygons can share dimension two while covering very different areas. Two lines can share dimension one while having different lengths. This method doesn’t calculate those quantities. A dimension label should not stand in for a requested measurement.

Likewise, the mixed collection doesn’t become a line simply because its maximum dimension is one. It still contains the point alongside the line. The result describes one property of the collection. Retain the instance’s actual type when the consumer needs it.

The coordinates use a local geometry plane with spatial reference identifier zero. The script makes no geographic unit, indexing or application-validity claim. It consists of a CTE and an ordered SELECT. No objects or session options are changed.

Keep a complete typed result

Compare each complete label, integer dimension and bit empty flag across all five rows. Keep the mixed and empty cases in that comparison. Read dimension zero separately from the empty minus-one result. These outputs expose mistakes that simple polygons alone would miss.

I can justify a dimension column in a diagnostic result when its meaning is explicit. It provides a compact classification property. Keep other questions in their own columns and methods. A small numeric value still needs the correct label and input context.

Read the number as a property, never as a count.

Dimension is not a coordinate count, it is a spatial property that preserves an explicit empty-instance result.

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