Why doesn't STDifference between a LINESTRING and a POINT on the line string yield a MULTILINESTRING of the two pieces?

Question

DECLARE @Route geography
SET @Route = geography::STLineFromText('LINESTRING(-0.175 51.0, -0.175 59.0)',4326)

DECLARE @Route2 geography
SET @Route2 = @Route.STDifference(geography::STPointFromText('POINT(-0.175 52)', 4326))

SELECT @Route2

Why does @Route2 evaluate to LINESTRING (-0.175 59, -0.175 51) instead of a MULTILINESTRING consisting of the line segments on either side of the point?

The only way I have been able to do this is by creating a second LINESTRING with very small length (0.0001) and use this to subtract. It works, but it isn't very elegant.

Answer 1

It appears the issue arises from the fact that the engine doesn't have a way of representing the following: (I'm using 1D syntax to make it simpler to understand.)

[1, 3] - 2 = [1, 2) U (2, 3]

Instead, it does [1, 3] - 2 = [1, 2] U [2, 3] and then simplifies this back to [1, 3].

The solution in this case is to either do the calculation manually, or, if your application will allow for it, add a very small buffer around the subtracted point to make it occupy space. In the example from the question, I do:

DECLARE @Route geography

SET @Route = geography::STLineFromText('LINESTRING(-0.175 51.0, -0.175 59.0)',4326)

DECLARE @Route2 geography
SET @Route2 = @Route.STDifference(geography::STPointFromText('POINT(-0.175 52)', 4326).STBuffer(0.01))

SELECT @Route2

This 0.01 meter buffer is irrelevant in my application but it solves the problem -- I now get a MULTILINESTRING with two pieces. The only gotcha is that they don't intersect, but I suppose this is to be expected.