geometry Collections: Read Members With STGeometryN

geometry Collections hold member shapes that STGeometryN can return individually. I extract a point and a line from one fixed collection. A third request deliberately exceeds its member count.

Member extraction is different from choosing a vertex inside a line. The returned value is itself a geometry instance. Its type and dimension help identify what the collection contains.

Request three member positions

The input collection contains two members in its supplied order. Its first member is a point, and its second is a horizontal line. The third requested position has no member.

A small VALUES constructor supplies indexes one, two and three. CROSS JOIN applies those requests to the one collection. The query requires no numbers table or persistent objects.

The output includes the total member count on every row. MissingMember records whether extraction returned NULL. Type and dimension are only inspected for returned members.

WITH Input AS
(
    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(2 3), LINESTRING(0 0, 4 0))', 0) AS Collection
), Requested AS
(
    SELECT i.Collection, v.MemberNumber,
           i.Collection.STGeometryN(v.MemberNumber) AS Member
    FROM Input AS i
    CROSS JOIN (VALUES (1), (2), (3)) AS v(MemberNumber)
)
SELECT MemberNumber, Collection.STNumGeometries() AS MemberCount,
       CASE WHEN Member IS NULL THEN 1 ELSE 0 END AS MissingMember,
       CASE WHEN Member IS NULL THEN NULL ELSE Member.STGeometryType() END AS MemberType,
       CASE WHEN Member IS NULL THEN NULL ELSE Member.STDimension() END AS MemberDimension
FROM Requested
ORDER BY MemberNumber;

Read the returned geometry types

The first request should return a Point with dimension zero. The second should return a LineString with dimension one. Both rows should report a total member count of two.

The third request should return MissingMember one with NULL type and dimension. STGeometryN returns NULL when the requested index exceeds the member count. That outcome does not create an empty replacement member.

The query deliberately never asks for member zero or a negative index. Those values are outside the valid range and can raise an exception. A caller should validate requested indexes before using them.

The expected output preserves all three requests, including the absent result. Removing the third row would hide the boundary behavior. It is useful when adapting a member loop or validating incoming requests.

Keep members separate from vertices

A LineString member can contain several coordinate points. STGeometryN retrieves that whole member rather than one of its vertices. Counting collection members therefore answers a different question from counting coordinates.

A member can itself be another collection. Such nesting requires a deliberate traversal rule if every leaf shape must be visited. This example only extracts the top-level members of one simple collection.

I don’t assume that every member has the same type. The point and line are intentionally different. A consumer that accepts only lines should inspect each returned member before applying its line-specific logic.

The supplied order identifies the members in this particular input. It should not become a business identifier for unrelated imported collections. Keep real identifiers separately if an application needs stable naming.

Make the boundary behavior visible

The total count is a useful first limit for member requests. It does not prove that each member satisfies the application’s requirements. Type, validity and business meaning remain separate checks.

The example performs no spatial comparison or repair. It only inspects the structure of a fixed collection. No rendering, indexing or performance behavior is claimed.

When adapting the query, keep a populated point, a populated line and a beyond-count request in the tests. They exercise the main return categories. A single successful extraction does not cover the index boundary.

Count the members, use one-based indexes, and treat an absent member as absent.

A collection member is not a vertex, it is a whole shape of its own.

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