geometry STGeometryType: Type Names Say More Than Dimension

geometry STGeometryType identifies the represented shape type, which carries more information than its dimension. I compare single and multiple shapes in one small query. Equal dimensions do not mean that their structures are interchangeable.

A Point and a MultiPoint both have dimension zero. A LineString and a MultiLineString both have dimension one. Their type names still describe different forms that a consumer may need to handle.

Compare four fixed spatial values

The first input is one point at coordinates two and three. The second contains that point and another point. The next inputs are one horizontal line and two separate horizontal lines.

All values are constructed from fixed spatial text with reference identifier zero. The query returns only a case identifier, type name and dimension. No spatial rendering or persistent objects are required.

The final ordering follows the supplied case identifiers. That keeps the point pair and line pair together. The output can be checked without relying on arbitrary collection serialization order.

WITH Inputs AS
(
    SELECT CaseId, Wkt
    FROM (VALUES
        (1, 'POINT(2 3)'),
        (2, 'MULTIPOINT((2 3), (4 5))'),
        (3, 'LINESTRING(0 0, 4 0)'),
        (4, 'MULTILINESTRING((0 0, 4 0), (0 2, 4 2))')
    ) AS v(CaseId, Wkt)
), Shapes AS
(
    SELECT CaseId, geometry::STGeomFromText(Wkt, 0) AS Shape
    FROM Inputs
)
SELECT CaseId, Shape.STGeometryType() AS ShapeType,
       Shape.STDimension() AS Dimension
FROM Shapes
ORDER BY CaseId;

Read type and dimension together

The first two rows should return Point and MultiPoint, both with dimension zero. The last two should return LineString and MultiLineString, both with dimension one. The repeated dimension values are intentional.

A dimension of zero cannot tell a caller whether one point or several points were supplied. A dimension of one cannot distinguish one line from several line members. The type name supplies that missing distinction.

STGeometryType returns the OGC type name as text. That text is a diagnostic about the geometry value. It does not convert the value into an ordinary character representation of its coordinates.

The four-row output includes both type and dimension. Checking only the dimensions would miss the entire purpose of the comparison. Keep the type column when adapting this inspection to an import or interface.

Match the consumer contract

A drawing component might accept a line but require explicit handling for multiple lines. A calculation might expect one point rather than a set of points. Those requirements should be checked against the actual value.

I don’t silently select the first member to make a multiple shape look like a single shape. That would discard part of the input. Any reduction needs its own written selection rule.

Likewise, expanding a single shape into a collection changes its representation without necessarily changing its dimension. The distinction remains relevant for serialization and consumers. Dimension alone cannot reveal that representation choice.

Type inspection is also different from a validity check. This example uses valid instances. Check validity before inspecting types on imported shapes. Validate each property that the consuming operation actually requires.

Keep the scope precise

This example uses four valid nonempty instances. It does not enumerate every geometry type, curved shape or nested collection. Add those cases when an application accepts them.

A NULL geometry is a missing input and needs a separate policy. An empty geometry also has its own type and emptiness properties. Neither should be guessed from a single dimension value.

The query makes no performance or index claim. It simply exposes a representation detail that can be hidden by broad dimensional categories. That small check can prevent an unsupported shape from reaching a consumer.

Read the type name along with the dimension, and the shape tells you more.

Dimension is not shape, it is only the first clue to the type you hold.

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