STIntersection result is STIntersects = 0

Question

I have line @a that intersects another line @b. When I take the intersection point and detect if/where it intersects @b, it returns false

declare @a GEOMETRY = Geometry::STGeomFromText('LINESTRING (-83 24, -80.4907132243685 24.788632986627039)', 4326)
declare @b GEOMETRY = Geometry::STGeomFromText('LINESTRING (-74.7 21.8, -75.7 22.1, -77.8 22.6, -79.4 23.3, -80.4 24.5, -81.5 28, -84 33, -87 36)', 4326)


DECLARE @intersectionPoint geometry = @a.STIntersection(@b) -- POINT (-80.49071322436852 24.788632986627078)

IF @intersectionPoint IS NULL
    THROW 50000, '@intersectionPoint not found', 1


-- Expect 1, Result 0
SELECT @b.STIntersects(@intersectionPoint)

Answer 1

It boils down to general approach that you should use when dealing with floating-point numbers in calculations. You should not use equality comparison with floating point numbers, like if a == b, but always compare them with some epsilon precision that makes sense in your application domain, like if abs(a-b) < 1e-8.

It is conceptually similar to performing some non-trivial calculations, for example:

double a = 2.0;
a = sqrt(a);
a = a*a;

and then expecting that if a == 2.0 would return true instead of writing if abs(a-2.0) < 1e-8.

---

Geometry point in SQL Server is represented as floating point numbers, which are not exact.

DECLARE @intersectionPoint geometry = @a.STIntersection(@b)

calculates intersection point to the best of its precision, but it will never be exact.

So, expression like @b.STIntersects(@intersectionPoint) is conceptually equivalent to equality comparison. It is equivalent to if @b.STDistance(@intersectionPoint) == 0, which will be true only in few special cases.

You should use something like @b.STDistance(@intersectionPoint) < 1e-8 instead.

Answer 2

This appears to be a rounding error. If I add the following to your code:

SELECT @b.STDistance(@intersectionPoint);

I get ≈ 3 femtometers. Which unless you're measuring something at the atomic scale is probably good enough to be considered as "on the line".

Out of curiosity, what problem are you actually trying to solve?