geometry Line Endpoints: Preserve the Described Direction

geometry line endpoints identify the first and last described points. I keep that ordering explicit before using an endpoint as the start of a route.

A line description carries an order

A path can cover the same places in either direction. Its first described point still changes when the coordinate sequence is reversed. A spatial endpoint is therefore tied to that description. It doesn’t independently establish the application’s preferred travel direction.

STStartPoint returns the first point. STEndPoint returns the last point. Both methods return geometry values. For a nonempty LineString, those returned values are points whose coordinate properties can be inspected.

I’d name the application’s direction contract separately from geometric coverage. An imported line might have its coordinates reversed. The start method follows those coordinates rather than repairing them. Keeping the source description prevents an endpoint label from hiding that choice.

Compare an open path and its reversal

The first line runs from zero-zero through three-zero to three-four. Its start is zero-zero and its end is three-four. The second line describes those same coordinates in reverse order. Its start and end consequently exchange roles.

A third line returns from three-four to zero-zero. Its first and last described points are the same. The additional return segment makes this a closed line. The query includes STIsClosed beside the four endpoint coordinate outputs.

All three shapes use spatial reference identifier zero and literal WKT. The SQL contains only a CTE and SELECT. It changes no objects or session options. ORDER BY retains the intended open, reversed and closed comparison.

WITH Lines AS
(
    SELECT Id,CAST(CaseLabel AS varchar(20)) AS CaseLabel,
           geometry::STGeomFromText(LineText,0) AS Shape
    FROM (VALUES
        (1,'OpenForward','LINESTRING(0 0,3 0,3 4)'),
        (2,'OpenReversed','LINESTRING(3 4,3 0,0 0)'),
        (3,'Closed','LINESTRING(0 0,3 0,3 4,0 0)')
    ) AS v(Id,CaseLabel,LineText)
)
SELECT Id,CaseLabel,
       CAST(Shape.STStartPoint().STX AS decimal(12,3)) AS StartX,
       CAST(Shape.STStartPoint().STY AS decimal(12,3)) AS StartY,
       CAST(Shape.STEndPoint().STX AS decimal(12,3)) AS EndX,
       CAST(Shape.STEndPoint().STY AS decimal(12,3)) AS EndY,
       Shape.STIsClosed() AS IsClosed
FROM Lines
ORDER BY Id;

Read coordinate pairs rather than a friendly label

The output displays each endpoint’s X and Y as decimal(12,3). That cast gives the example a stable numeric format. STX and STY themselves are float properties. It doesn’t change the stored geometry or impose decimal coordinates on every spatial input.

These values are local planar coordinates, not latitude and longitude. The query doesn’t assign a physical distance unit. It checks the description’s endpoint orientation only. A geographic route would require a suitable model and a separately established coordinate contract.

The case label explains why each line was selected. It doesn’t substitute for checking both coordinate pairs. A result that returns only a start label could hide a reversal. The complete four-coordinate tuple makes that difference visible.

Keep closure separate from the wider route contract

STIsClosed checks whether the start and end points coincide. The closed row demonstrates that narrow relationship. It doesn’t prove that a route is appropriate, navigable or free from other issues. Those are additional questions.

The open and reversed rows both remain open. Reversing their description doesn’t add the missing return segment. The closed row contains that segment explicitly. Point order and closure therefore answer different parts of the example.

This script deliberately uses nonempty simple lines. For an empty geometry instance, the endpoint methods return NULL. A production contract should decide how that absence is handled. The current comparison doesn’t replace it with a made-up coordinate.

Retain both directions in an endpoint test

One open line alone could make an assumed direction seem correct. Its reversed counterpart exposes the dependence on description order. The closed case adds a different endpoint relationship. All three full rows belong in the expected result contract.

Compare every endpoint coordinate and closure bit across all three rows. Keep the forward and reversed coordinate pairs beside their descriptions. Retain the ordered WKT when reviewing orientation. A visually familiar path doesn’t determine which endpoint is first.

Keep the stored order in view, and the direction question answers itself.

A line endpoint is not a travel direction, it is a point picked by the stored order.

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