I have a set of points which represents a series of telegraph poles. I want to link them to simulate the wire running along them. The code I've been using is shown below.
SELECT DISTINCT ON (p1.id)
p1.id AS p1id,
p2.id AS p2id,
ST_ShortestLine(p1.geom,p2.geom) As snapped_point
FROM public.poles AS p1
INNER JOIN public.polescopy AS p2 ON ST_DWithin(p1.geom,p2.geom,100.0)
WHERE p1.id <> p2.id
ORDER BY p1.id
A problem occurs when the next pole in line is further away than the previous pole. This causes the nearest neighbour search to double back to the previous pole and ends the line prematurely.
Is there a way of tagging the previously used pole so that the nearest neighbour search ignores it?