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)