TSQL - get closest coordinate on linestring to a point

Question

Consider the overly simplistic example: POINT(0 0) and LINESTRING (1 -10, 1 10)

The closest point on the line to the POINT would be 1, 0.

How would one determine this in TSQL? My simple, not entirely accurate, approach was to make a linestring (POINT POINT) and extend out the X coord of one coords until the two linestrings intersected.

So:

1. linestring (0 0, 0.25 0) (no intersect)
2. linestring (0 0, 0.5 0) (no intersect)
3. linestring (0 0, 0.75 0) (no intersect)
4. linestring (0 0, 1 0) (intersection - so 1 0 is the point closest to POINT

This quasi worked, but doesn't seem to the most bestest/more performant way of accomplishing this.

For example, one inefficiency is that I move it one direction (positive increments), and if there was no match (after x attempts), then I would start over, but with negative increments.

To optimize, I tried moving in larger steps, then when intersected (probably went past the point), I backed off 1 increment and started from there with a smaller increment. I did this a couple of times - instead of going in tiny tiny increments so as not to overshoot by too much.

One acceptable assumption based on my processing that the POINT will be next to (left/right) of the LINESTRING.

Another acceptable assumption is that the LINESTRING will be fairly "perpendicular" to the POINT.

Answer 1

I think you can do this mathematically rather than with a brute-force iterative algorithm.

There is a post to get closest point to a line that describes the method.

I converted this method to SQL which returns the correct value (1,0). Your 'trivial' example is actually a bit of an edge case (vertical line with infinite slope) so it seems robust.

I also tested the source code with this example: https://www.desmos.com/calculator/iz07az84f5 and using the input for the line of (-1,2) (3,0) and a point at (2,2) got the correct answer (1.4, 0.8).

SQL code (also in SQL Fiddle at http://sqlfiddle.com/#!6/d87aa/15)

DECLARE @x int, @y int, @x1 int, @y1 int, @x2 int, @y2 int 
DECLARE @atb2 float, @atp_dot_atb float
DECLARE @t float

--SELECT @x=0, @y=0 
--SELECT @x1=1, @y1=10, @x2=1, @y2=-10  
SELECT @x=2, @y=2
SELECT @x1=-1, @y1=2, @x2=3, @y2=0

SELECT @atb2 = SQUARE(@x2-@x1)  + SQUARE(@y2-@y1)  -- Basically finding the squared magnitude   of a_to_b
SELECT  @atp_dot_atb = (@x-@x1)*(@x2-@x1) + (@y-@y1)*(@y2-@y1) -- The dot product of a_to_p and a_to_b
SELECT @t = @atp_dot_atb / @atb2              --  The normalized "distance" from a to  your closest point

SELECT @x1 + (@x2-@x1)*@t, @y1 + (@y2-@y1)*@t  --Add the distance to A, moving towards B