geometry STPointN: Read a One-Based Point Position

geometry STPointN selects a point using a position that starts at one. I retain that position beside its coordinates instead of assuming a familiar zero-based indexing rule.

Read positions within one geometry

The argument is an integer between one and the instance’s number of points. STPointN returns a geometry Point. The original instance can contain several coordinates. Selecting one point is different from selecting a row from a table.

For a user-created geometry, the ordering follows the original input points. A system-constructed instance has its own deterministic output ordering. This example deliberately supplies one literal LineString. It doesn’t assume every generated geometry preserves an arbitrary source-row sequence.

I’d keep the original coordinate order when investigating a path. Sorting points independently by their coordinates would change that path. A point position can carry structural meaning. It isn’t necessarily an identifier for a business record.

Read a short line at four positions

The line starts at origin, moves three units right, then four units upward. The index list contains one through four. Each request uses the same literal line with spatial reference identifier zero.

Positions one, two and three return their corresponding coordinates. Position four exceeds the available count and returns NULL. The query retains the original request even when no point is returned. It doesn’t filter away that outcome.

The selected point’s X and Y properties are cast to decimal(12,3) for display. Those explicit widths fit the small integer coordinates. They aren’t a universal spatial precision recommendation. The point remains a geometry value before those property conversions.

WITH Shape AS
(
    SELECT geometry::STGeomFromText(N'LINESTRING(0 0, 3 0, 3 4)', 0) AS LineShape
), Positions AS
(
    SELECT Id, PointPosition
    FROM (VALUES (1, 1), (2, 2), (3, 3), (4, 4)) AS v(Id, PointPosition)
)
SELECT p.Id, p.PointPosition,
       CAST(n.SelectedPoint.STX AS decimal(12,3)) AS X,
       CAST(n.SelectedPoint.STY AS decimal(12,3)) AS Y
FROM Shape AS s
CROSS JOIN Positions AS p
CROSS APPLY (VALUES (s.LineShape.STPointN(p.PointPosition))) AS n(SelectedPoint)
ORDER BY p.Id;

Separate missing point and invalid index

An argument less than one raises an exception. That differs from the NULL result for an index above the point count. The script doesn’t execute a deliberately invalid index. Its four positive requests expose the useful upper-bound behavior.

I can justify validating a caller’s requested position before applying the method. That prevents an invalid index from interrupting a larger query. The validation still needs the actual instance’s point count. A fixed maximum copied from this three-point line is insufficient.

A returned NULL point also differs from a real origin point. The origin has X and Y values of zero. The excessive request has missing coordinate properties. Keeping the request and both properties visible prevents those cases from being merged by display defaults.

Keep coordinate units and shape scope clear

These are planar geometry coordinates in a deliberately simple local example. The query makes no latitude, longitude or Earth-distance claim. Spatial reference identifier zero doesn’t establish meters by itself. The application must define the coordinate unit it uses.

The script reads one literal line and a literal index list. It creates no objects or connection settings. ORDER BY fixes the four-case sequence. Keep every requested position beside its displayed coordinates and declared types.

Compare all three available points and the excessive request. Preserve the missing point’s NULL coordinate outputs. A matching count doesn’t prove the coordinate order. The full copyable line text keeps the original input sequence available for comparison.

Count from one, and the points line up.

A point position is not a zero-based record key, it is an index within the geometry instance.

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