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
SET @PointID = 1
DECLARE @SearchCount INT
SET @SearchCount = 10
DECLARE @SearchDistance INT
SET @SearchDistance = 15000

SELECT TOP(@SearchCount) 
    a.pointGeom.STDistance(b.lineGeom) as [DistanceFromTarget]
    LINES b,
    POINTS a
    a.PointID = @PointID
    AND (a.pointGeom.STDistance(b.lineGeom)) < @SearchDistance
ORDER BY a.pointGeom.STDistance(b.lineGeom)
  • Can you please share the code to get the nearest line from the point, i am stuck in this. will be very thankful to you – user57340 Aug 27 '15 at 20:01
  • Sadly what I ended up doing was just displaying the nearest line to the point. I can give you code for this, but you'll need to add some sort of filtering. If you are wanting the exact lat/long where they intersect then I can't help you I'm afraid. (Assuming you are also on SQL 2008) – Ravendarksky Aug 28 '15 at 8:33

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

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

WITH Candidate AS ( -- Original Query
    SELECT TOP(@SearchCount) 
        a.pointGeom.STDistance(b.lineGeom) as [DistanceFromTarget],
        LINES b,
        POINTS a
        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
    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;

ShortestLineTo (geography Data Type)


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

  • Hi Russell... I was wondering why I couldn't find this in the documentation but it is because it was added in Sql Server 2012. I'm using 2008 currently :-( – Ravendarksky Sep 3 '14 at 13:34
  • Sorry, missed that. If you can't upgrade your DB you'll likely have to implement the computation yourself. A planar approximation would be easy and I can give you pointers, but it will be imperfect. A true geodetic solution would not be trivial. – Russell at ISC Sep 3 '14 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.