geometry STDistance: Measure the Closest Points of Two Shapes

geometry STDistance measures the shortest distance between points belonging to two represented shapes. I compare an interior gap with an endpoint gap. Intersecting shapes provide a useful zero-distance case.

The closest location on a line does not have to be one of its listed vertices. It can lie inside a segment. Comparing only endpoint distances can therefore answer a different question.

Use distances that can be checked directly

The first point is three units above the middle of a horizontal line. Its perpendicular projection lies inside that line. The shortest gap should be three units.

The second point lies beyond the line’s right endpoint. Its horizontal and vertical gaps from that endpoint are three and four. The shortest distance should therefore be five units.

The final pair contains overlapping rectangular regions. Their shared area means that some points belong to both shapes. The shortest separation should be zero rather than a distance between their centers.

WITH Inputs AS
(
    SELECT CaseId, CaseName, LeftWkt, RightWkt
    FROM (VALUES
        (1, 'Perpendicular interior', 'POINT(2 3)', 'LINESTRING(0 0, 4 0)'),
        (2, 'Closest endpoint', 'POINT(7 4)', 'LINESTRING(0 0, 4 0)'),
        (3, 'Intersecting regions', 'POLYGON((0 0, 4 0, 4 2, 0 2, 0 0))',
             'POLYGON((2 1, 6 1, 6 3, 2 3, 2 1))')
    ) AS v(CaseId, CaseName, LeftWkt, RightWkt)
), Shapes AS
(
    SELECT CaseId, CaseName, geometry::STGeomFromText(LeftWkt, 0) AS LeftShape,
           geometry::STGeomFromText(RightWkt, 0) AS RightShape
    FROM Inputs
)
SELECT CaseId, CaseName,
       CAST(LeftShape.STDistance(RightShape) AS decimal(10,3)) AS ClosestDistance
FROM Shapes
ORDER BY CaseId;

Read the complete result

The expected distances are three, five and zero in case order. The query casts the float result to decimal with three fractional digits for a compact output. That presentation does not change the method’s underlying return type.

The first result is not the distance to either endpoint. Those endpoints are farther from the point than the line’s interior. Keeping that case prevents an accidental endpoint-only interpretation.

The second result is an endpoint case because the perpendicular projection would lie beyond the finite segment. The method measures the represented line, not its unlimited extension. A different line extent would change the answer.

The overlapping regions are not required to have matching centers. Sharing any represented point is enough to make the minimum distance zero. This result is distinct from comparing centroids or measuring boundary-only gaps.

State the units and reference system

Every input here uses the same abstract plane and reference identifier zero. The numbers have the units assigned to those coordinates. The example does not identify those units as meters, kilometers or feet.

A geometry distance should not be treated automatically as a geodesic surface distance. Coordinates and the chosen representation determine the interpretation. A geographic application needs an appropriate model and reference system.

STDistance returns NULL when the reference identifiers do not match. An empty comparison geometry also produces NULL. Those outcomes should be handled separately from a definite measured gap of zero.

I don’t replace NULL with zero in a distance filter. That would make an unavailable comparison look like an intersection. Preserve the distinction until the application has an explicit missing-input policy.

Keep the measurement question focused

This example measures a minimum gap, not path length or travel distance. A road route can be much longer than the shortest geometric separation. A routing question needs connectivity and permitted movement rules.

The fixed inputs are valid and have exact small coordinates. No tolerance, approximate contact rule or nearest-neighbor ranking is included. Those additions require their own examples and acceptance rules.

The result does not establish an index choice or performance benefit. It explains the value returned for three bounded shape pairs. Measure production behavior separately after deciding which distance question the application needs answered.

State the coordinate units first, then trust the number.

A distance between shapes is not a route, it is the shortest gap between them.

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