Subscribe to this thread
Home - General / All posts - SQL - Closest Point on line to another point.
atrushwo108 post(s)
#21-Feb-17 17:01

Hi,

I'm looking for some help here. I have a drawing of points and a drawing of lines. For each point, I need to find the point on the line to which it is closest. I can do the nearest neighbour method to find the distance, but I can't figure out how to get the point. Any help would be greatly appreciated.

code

select [2].[ID],

(

select top 1 Distance([1].[id],[2].[ID]) as [Distance] 

from [Line] as [1] 

order by distance ([1].[id],[2].[ID])

)

from [Point] as [2]

artlembo


3,400 post(s)
#21-Feb-17 17:23

If you have two drawings: Line and Point, this will do it:

SELECT first(p) AS pointgeom, pid

FROM 

(

  SELECT  distance(point.[Geom (I)],p) AS dist,p, point.id AS pid, lineid

  FROM point, (SELECT line.id AS  lineID, p

               FROM line

               SPLIT BY Coords([Geom (I)]AS p

               )

  ORDER BY pid, dist ASC

)

GROUP BY pid

tjhb
10,094 post(s)
#21-Feb-17 17:38

Art, that will find the closest coord on the closest line, but not the closest point.

@atrushwo, try a forum search for nearest point on nearest line. I'll come back with a link.

atrushwo108 post(s)
#21-Feb-17 17:51

Thanks tjhb,

I found what I needed. Really appreciated.

http://www.georeference.org/forum/t65447.18

tjhb
10,094 post(s)
#21-Feb-17 17:53

Try this recent revision [for the case of a single target line], older revision here [as in your url].

atrushwo108 post(s)
#21-Feb-17 17:41

Hey! Thanks for the quick reply. Really appreciated.

Its not quite doing what I need though. Your query only considers the start and end points of the line. I need a query which identifies the (potentially) intermediate point on the line where the distance is shortest to the point.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.