Closest Point on a Line in SQL Server Spatial 2008 R2

Question

In an ideal world I would be running on SQL Server 2012 and be able to use the .ShortestLineTo() function to find the closest point on a line, to that of another point. I currently am able to find the closest line to my point - but now I need to find the coordinates of the point closest to my point in question.

Unfortunately I am stuck on SQL Server 2008 R2, so don't have the option to use .ShortestLineTo().

How do others achieve this in SQL Server Spatial Geometry types?

Cheers,

Matt

Answer 1

Late answer, but if it still helps (or for anyone else) you should be able to do the following with SQL 2008.

DECLARE @point GEOMETRY = GEOMETRY::STPointFromText('POINT(0 0)', 0);
DECLARE @line GEOMETRY = GEOMETRY::STLineFromText('POINT(10 10, 20 20)', 0);

SELECT STIntersection(@point.STBuffer(@point.STDistance(@line)));

Essentially, you calculate the distance between the two geometries,use that as a buffer on the point which should result in the geometries touching, and take the intersection (point) of that.