Joining points using PostGIS

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?

• ...that set does not have their ids in order of connectivity? I'm pretty sure that you will run into mismatches with a plain NN search eventually, even if you use a recursive query. Apr 30, 2019 at 18:48
• thanks for the reply. The poles might be sequential but it's safer to assume that they aren't, since some poles connect to more than one other and the other poles will be connected later in the search. May 1, 2019 at 6:38
• One way of doing this would be with a RECURSIVE CTE, where you keep track of the ids already included, as @ThingumaBob has suggested. You could also calculate the angle between the current segment and the previous and ensure that this is greater than 0. You could possibly use some other heuristic if you know the poles are running in more or less a straight line. However, it is possible you will run into problems if this condition does not hold. May 1, 2019 at 16:21

I think the root cause is that the query selects a single line for each pole id. Note that the query does not even select the nearest neigbor, but an arbitrary one - since it only has `ORDER BY p1.id`. You will need to add something like `ORDER BY p1.id, ST_Length(snapped_point)` to get nearest one.

What I would try is select all short-enough pairs, start with something like

``````SELECT
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
``````

And then select two of them using window functions, something like

``````SELECT ...
p2.id,
ROW_NUMBER() OVER (PARTITON BY p1.id ORDER BY ST_Length(snapped_point))
WHERE row_number < 3
``````