geometry SRID Mismatch: NULL Is Not a Zero Distance

geometry SRID mismatch produces NULL from STDistance rather than a zero separation. I keep both spatial reference identifiers visible before interpreting the result.

A missing distance is a different result

Two points at the same coordinate can have a measured distance of zero. A mismatched spatial reference identifier produces another condition. STDistance returns NULL when the identifiers differ. Replacing that NULL with zero would hide the distinction.

STDistance returns the shortest distance between points in two geometry instances. Its native SQL type is float. This example uses single points to keep that relationship narrow. The displayed output is cast to decimal(12,3).

I’d retain the two identifiers alongside the measurement during diagnosis. A lone NULL doesn’t explain its cause. Empty or absent input can also need separate handling. The selected rows isolate identifier mismatch without an empty instance.

Compare matching and mismatched points

The invoking point is zero-zero with identifier zero in every row. A second point at three-four with the same identifier gives distance five. The matching coincident point at zero-zero gives zero. Those results answer ordinary planar separation questions.

The two mismatch rows use identifier one for the second point. One remains at three-four and the other coincides numerically at zero-zero. Both distances are expected to be NULL. Coordinate equality doesn’t remove the identifier requirement.

All instances are constructed from literal numeric inputs. A CTE retains their labels and a second constructs the point values. One SELECT returns both identifiers and distance. No stored object, connection option or spatial property is modified.

WITH Inputs AS
(
    SELECT Id,CAST(CaseLabel AS varchar(20)) AS CaseLabel,X,Y,OtherSrid
    FROM (VALUES
        (1,'MatchingSeparated',3,4,0),
        (2,'MismatchedSeparated',3,4,1),
        (3,'MatchingCoincident',0,0,0),
        (4,'MismatchedCoincident',0,0,1)
    ) AS v(Id,CaseLabel,X,Y,OtherSrid)
),
Points AS
(
    SELECT Id,CaseLabel,geometry::Point(0,0,0) AS FirstPoint,
           geometry::Point(X,Y,OtherSrid) AS OtherPoint
    FROM Inputs
)
SELECT Id,CaseLabel,FirstPoint.STSrid AS FirstSrid,
       OtherPoint.STSrid AS OtherSrid,
       CAST(FirstPoint.STDistance(OtherPoint) AS decimal(12,3)) AS Distance
FROM Points
ORDER BY Id;

Do not conceal a mismatch with a default

A convenience expression that substitutes zero for NULL would make the mismatched coincident row look like an ordinary successful measurement. It could also turn the mismatched separated row into an apparent coincidence. That is a different meaning from the returned value.

An application can choose a separate status for an unavailable measurement. It should retain the actual distance and explain that status. Don’t infer the intended default from a numeric display requirement. The example deliberately preserves NULL without substitution.

Keep both mismatch rows alongside the matching rows in the complete result. Compare every identifier and the decimal distance display. Checking only non-NULL distances would omit the central contract. A result count wouldn’t detect inappropriate zero substitution.

Matching identifiers do not establish every unit assumption

The matching rows use local planar coordinates. Their distances are expressed in those local units. The number zero as an identifier doesn’t promise metres. The application still needs a consistent coordinate system and documented unit meaning.

STSrid is an integer property you can assign. This example does not change it to force a distance. Assigning a common identifier isn’t evidence that incompatible source coordinates were transformed. Establish the coordinate relationship rather than treating a label change as that work.

Likewise, matching identifiers are only the requirement tested here. They don’t certify the data’s real-world accuracy or suitability. The small made-up points avoid those additional claims. A production spatial import needs its own provenance and coordinate contract.

Keep zero and NULL separately reviewable

The matching coincident row is a deliberate zero witness. The mismatched coincident row is its NULL counterpart. Their identical numbers make the identifier condition visible. Preserve that pair when extending the query.

Compare all four ordered rows and their SQL types. Keep the coincident zero result beside its mismatched NULL counterpart. A distance can be missing for more than one reason in a broader query. Retain source context before deciding how the consumer should respond.

Zero and NULL tell different stories, so keep them apart.

A missing distance is not zero separation, it is a result that needs its input context preserved.

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