Why is this SQL Server geometry query returning no geometry?-CodePudding

I'm querying SQL Server 2017 for the point on a geometry line that is closest to a given point. The following works well for some data, not others. I can't understand the inconsistency.

declare @line geometry = 'LINESTRING(0 0, 10 10)' 
declare @p1 geometry = 'POINT(3 2)'
declare @p2 geometry = 'POINT(3 1)'

select @p1.STBuffer(@p1.STDistance(@line)).STIntersection(@line).ToString()
select @p2.STBuffer(@p2.STDistance(@line)).STIntersection(@line).ToString()

The result for p1 is POINT (2.500000 2.499999) as expected. The result for p2 is GEOMETRYCOLLECTION EMPTY and I wonder why. There's nothing really different about the points.

CodePudding user response：

Spatial type coordinates and the results of distance calculations are all floating point numbers, not exact real numbers. And so when comparing you have to test with some tolerance for rounding and truncation differences at the limits of floating point accuracy.

So if you buffer with a small tolerance this works fine:

declare @line geometry = 'LINESTRING(0 0, 10 10)' 
declare @p1 geometry = 'POINT(3 2)'
declare @p2 geometry = 'POINT(3 1)'

select @p1.STBuffer(1.0000000001*@p1.STDistance(@line)).STIntersection(@line).ToString()
select @p2.STBuffer(1.0000000001*@p2.STDistance(@line)).STIntersection(@line).ToString()

This is essentially the same as

select 'true' where 2 = power(sqrt(2),2)

quite literally as STDistance for geometry points is sqrt((x1-x2)^2 (y1-x2)^2)