Getting coordinates of shortest distance between point and line MS SQL 2008

Question

I have a table of point data (POINTS) in a SQL Server 2008 database table (No ShortestLineTo Function!). Each one has a SpatialPoint field of type geography (4326).

I have another table of line data (LINES). Each one has a set of points (geography, 4326 as well) and looks something like this: LINESTRING (-3.6323834096187415 56.610269344608838, -3.6317436657085147 56.610169913782741, -3.631120602112166 56.610079250198808, -3.6304975456164366 56.609988583421483, -3.6302179950805238 56.609929350497836, -3.6298861194017187 56.609789936217744, -3.6292192708989894 56.609439268468428, -3.6290017792165123 56.60930733362, -3.6287490209975957 56.609112972308289)

I know how to get which line is nearest a point and the distance of it... but I can't seem to get the actual coordinates of where the two intersect (If I draw a straight line from the point to the nearest part of the line I am wanting the lat/long of where that is!)

I'm hoping there is a fairly easy way of doing this in SQL. I've trawled through the documentation but can't see anything which achieves this. http://msdn.microsoft.com/en-us/library/bb933917.aspx . I must say that the documentation isn't great.

Here is my current query which just gives me the distance.

----For testing
DECLARE @PointID INT
SET @PointID = 1
DECLARE @SearchCount INT
SET @SearchCount = 10
DECLARE @SearchDistance INT
SET @SearchDistance = 15000

SELECT TOP(@SearchCount) 
    a.PointID,
    a.pointGeom.STDistance(b.lineGeom) as [DistanceFromTarget]
FROM 
    LINES b,
    POINTS a
WHERE 
    a.PointID = @PointID
    AND (a.pointGeom.STDistance(b.lineGeom)) < @SearchDistance
ORDER BY a.pointGeom.STDistance(b.lineGeom)

Answer 1

Here's something that should work with Geography datatypes. You may want to play around with the tolerances in the buffers to suit your data better. I've used CTEs to try and make the method clearer

DECLARE @PointID INT;
SET @PointID = 1;
DECLARE @SearchCount INT;
SET @SearchCount = 10;
DECLARE @SearchDistance INT;
SET @SearchDistance = 15000;

WITH Candidate AS ( -- Original Query
    SELECT TOP(@SearchCount) 
        a.PointID,
        a.pointGeom.STDistance(b.lineGeom) as [DistanceFromTarget],
        a.pointGeom,
        b.lineGeom,
        b.LineID
    FROM 
        LINES b,
        POINTS a
    WHERE 
        a.PointID = @PointID
        AND (a.pointGeom.STDistance(b.lineGeom)) < @SearchDistance
    ORDER BY a.pointGeom.STDistance(b.lineGeom)
    ),
    BufferIntersection AS ( -- Get first geography from the intersection of buffer
    -- May require a check to ensure a linestring
    SELECT PointID, pointGeom, LineID,
        pointGeom.BufferWithTolerance(pointGeom.STDistance(lineGeom) + 0.05, .01, 0).STIntersection(lineGeom).STGeometryN(1) AS Intersection
    FROM Candidate
    ),
    IntPoint AS ( -- Calculate the mid point for the intersection line.
    SELECT PointID, pointGeom, LineID,
        Intersection.STIntersection(Intersection.STPointN(1).BufferWithTolerance(Intersection.STLength() / 2.0, 0.001, 0)).STPointN(2) intPoint
    FROM BufferIntersection
    )
--put it together
SELECT PointID, LineID,
    Geography::STGeomFromText('LINESTRING(' + 
    CAST(CAST(pointGeom.Long AS NUMERIC(28,20)) AS VARCHAR(40)) + ' ' + CAST(CAST(pointGeom.Lat AS NUMERIC(28,20)) AS VARCHAR(40)) + ', ' + 
    CAST(CAST(intPoint.Long AS NUMERIC(28,20)) AS VARCHAR(40)) + ' ' + CAST(CAST(intPoint.Lat AS NUMERIC(28,20)) AS VARCHAR(40)) + ')',pointGeom.STSrid)
FROM IntPoint;

Answer 2

ShortestLineTo (geography Data Type)

http://msdn.microsoft.com/en-us/library/ff929252.aspx

(The point on the line would be the endpoint that isn't your point).