3

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.

2
  • 2
    Are you using PostGIS? If so, can you mention that in your question body, please?
    – PolyGeo
    Feb 11, 2020 at 22:15
  • I've added it into the text. I am using PGAdmin4 with the PostGIS extension.
    – theSchmett
    Feb 12, 2020 at 14:43

1 Answer 1

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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