Search code examples
sqlsql-serverfloating-pointgeometrygeography

STIntersection result is STIntersects = 0

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)
Solution

  • 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.