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)
Best Answer
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