geometry STLength: A Path Is Longer Than Its Chord

geometry STLength measures a path, while the distance between its endpoints can be shorter. I keep those measurements separate before using either one as a reported distance.

Choose the distance being requested

STLength returns the total length of a geometry’s elements. STDistance returns the shortest distance between two instances. Calling STDistance on a line’s endpoints therefore asks another question. It doesn’t sum the line’s intervening segments.

The distinction appears even in a tiny planar example. A path can turn before reaching its endpoint. Its straight endpoint separation can be shorter than the traveled path. A report named Distance needs to state which interpretation it supplies.

I’d choose the method from the actual measurement contract. A route description might need segment length. A proximity check might need shortest separation. Neither operation automatically establishes a real travel route or travel time.

Compare three controlled lines

The first literal line moves three units horizontally and four units vertically. Its total segment length is seven. Its endpoint separation is five by the right-triangle relationship. The query displays both values beside the case label.

The second line connects those same endpoints directly. Its path length and endpoint separation are both five. The third line returns to its starting point after those two segments. Its total length is twelve, while endpoint separation is zero.

Every line uses spatial reference identifier zero. The displayed measurements are explicitly decimal(12,3), converted from native float results. Their units follow the local coordinate contract. This script doesn’t label them as meters or kilometers.

WITH Inputs AS
(
    SELECT Id, CAST(CaseLabel AS varchar(20)) AS CaseLabel,
           geometry::STGeomFromText(LineText, 0) AS LineShape
    FROM (VALUES (1, 'Bent', N'LINESTRING(0 0, 3 0, 3 4)'),
                 (2, 'Straight', N'LINESTRING(0 0, 3 4)'),
                 (3, 'Closed', N'LINESTRING(0 0, 3 0, 3 4, 0 0)'))
        AS v(Id, CaseLabel, LineText)
)
SELECT Id, CaseLabel, CAST(LineShape.STLength() AS decimal(12,3)) AS PathLength,
       CAST(LineShape.STStartPoint().STDistance(LineShape.STEndPoint())
           AS decimal(12,3)) AS EndpointDistance
FROM Inputs
ORDER BY Id;

Closed does not mean length zero

A closed line has coincident start and end points. That explains the zero endpoint separation. Its segments still contribute path length. Treating the endpoint distance as the length would discard the entire closed traversal.

Overlapping segments can also contribute to STLength. The method doesn’t remove them as a cleanup policy. This example uses simple selected lines without overlapping segments. It doesn’t claim arbitrary GPS traces represent unique travel distance.

I can justify counting repeated traversal when the input intentionally records it. Another application may need geometric coverage instead. Those requirements should be explicit before repairing or deduplicating a shape. A convenient length method doesn’t decide the intended data meaning.

Keep planar and geographic measurements separate

The demonstration uses geometry rather than geography. Its coordinates are a small local plane. Assigning a familiar spatial reference number wouldn’t automatically turn the same geometry expression into an Earth-surface measurement. Choose the spatial model and units deliberately.

All three shapes come from literal text inside a CTE. The SELECT creates no tables or session changes. ORDER BY fixes the case sequence. Case label, path length and endpoint separation remain visible together.

Compare every displayed value and SQL type across the bent, straight and closed cases. Keep path length beside endpoint separation. Matching only the straight-line case wouldn’t establish the distinction. Retain the full original line text beside the calculation.

Say which distance you mean, and the report stays honest.

Endpoint separation is not path length, it is a different measurement over the same selected line.

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