Drawing line to closest point within single column

Question

I have sets of points that define a roadway segment, and can connect them with St_MakeLine, but I have no field with which to order them, so they zig-zag. Ordering by lat/long/x/y only partially helps, as they curve back on themselves.

I would like to connect each point to the next closest point with a new line, from within the same column.

ArcGIS Pro was my first try, but it is closing the segments at both ends (regardless of the settings I use). I am using PGAdmin4 with the PostGIS extension. Query speed is not an issue, I need it to run correctly once and then I will import the results into ArcGIS Pro.

CL. · Answer

The Introduction to PostGIS workshop does something like this in the Advanced Geometry Constructions part.

CREATE TABLE t (
  id int,
  p  geometry(point)
);
INSERT INTO t VALUES (1, ST_MakePoint(1, 1)),
                     (2, ST_MakePoint(2, 2)),
                     (3, ST_MakePoint(3, 1)),
                     (4, ST_MakePoint(1.5, 1)),
                     (5, ST_MakePoint(4, 2));

WITH RECURSIVE points(p, ids) AS (
  (SELECT p, array[id]
   FROM t
   WHERE id = 1)  -- start point
  UNION ALL
  (SELECT t.p, array_append(points.ids, t.id)
   FROM t
   JOIN points ON NOT points.ids @> array[t.id]
   ORDER BY t.p <-> points.p
   LIMIT 1)
)
SELECT ST_AsText(ST_MakeLine(p)) FROM points;

LINESTRING(1 1,1.5 1,2 2,3 1,4 2)

Drawing line to closest point within single column

One Answer

Add your own answers!

Ask a Question