# Creating lines connecting each possible pair of points in PostGIS

I am working with a group of points I would like to create lines connecting each possible pair of points.

Example: If I have points A, B and C I would like to have lines connecting A-B, A-C and B-C.

How can I do this in PostGIS?

• Jul 17, 2019 at 10:15
• Sorry to say ... but i feel that question has been asked a thousand times ... Jul 17, 2019 at 10:23
• @snaileater. It probably has, feel free to find one, and suggest closing as a duplicate of. Jul 17, 2019 at 10:26
• just wanted to promote "searching" a bit ... Jul 17, 2019 at 10:31
• @snaileater. Lol, promoting searching without searching, super ironic :-). Jul 17, 2019 at 10:59

In PostGIS, this can be done quite easily with a Cartesian product or CROSS JOIN in SQL, ie, joining a table on itself and using the 2nd form of ST_MakeLine. So, assuming a table called points with an id and a geometry called geom, this would look like:

SELECT
a.id,
b.id,
ST_MakeLine(a.geom, b.geom)
FROM points a, points b
WHERE a.id < b.id;

The comma between points a and points b is just syntactic sugar for CROSS JOIN. Setting a.id < b.id means that you will get a line between point 1 and point 2, but not between point 2 and point 1 and also you won't get a line between each point and itself (which will be nonsense, anyway). So, if you want the lines going in both directions, change the WHERE clause so you just exclude cases where the points are the same, so you have:

SELECT
a.id,
b.id,
ST_MakeLine(a.geom, b.geom)
FROM points a CROSS JOIN points b
WHERE a.id != b.id;

In terms of runtime, if you have a.id < b.id, then in big O notation, this is n^2/2, whereas a.id != b.id is n^2, i.e., twice as much work/time.

• Thanks, but Running: SELECT a.id, b.id. ST_MakeLine(a.geom, b.geom) FROM riverscale.points a, riverscale.points b WHERE a.id < b.id; Returns: cross-database references are not implemented: b.id.st_makeline LINE 1: SELECT a.id, b.id. ST_MakeLine(a.geom, b.geom... ^ . What am I doing wrong here? Jul 17, 2019 at 10:44
• Is riverscale a DB name or a schema name? If the former, you have your answer. Jul 17, 2019 at 10:56
• It's a schema name. Jul 17, 2019 at 10:57
• oh, my bad, there is a period not a comma after b.id. Fixed. Jul 17, 2019 at 10:58
• Thanks, it is working. One more thing: I tried to create a table with the result with "CREATE TABLE riverscale.lines AS SELECT a.id, b.id, ST_MakeLine(a.geom, b.geom) FROM riverscale.points a, riverscale.points b WHERE a.id < b.id;" but got an error saying "column "id" specified more than once" Jul 17, 2019 at 11:29

Let's assume we have 10 features in "points" layer respectively, see image below.

With the following query, it is possible to achieve the lines between all possible connection of points excluding themselves.

SELECT
p1.id  || '-' || p2.id AS Name,
MakeLine(p1.geometry, p2.geometry)
FROM
points AS p1,
points AS p2
WHERE
p1.id <> p2.id

The output will look like

• Fair enough, you posted while I was writing. I will leave mine anyway, as it is a pure Postgis solution and has further information about the WHERE clause. Jul 17, 2019 at 10:24
• By any means, thank you for posting! I will definitely check your answer because it includes explicit explanations from a PostGIS expert. Fair enough that our solutions partially overlap, however exact software was not determined bu the author in his question. Jul 17, 2019 at 10:27
• Thanks for the complement. Your answer is perfectly good and answers the question as first stated. Jul 17, 2019 at 14:11
• Greetings @Taras - I have a follow up question. Let's assume I am performing this operation above utilising polygon vertices and connecting them to each other. For concave polygons however I don't want to make connections "outside" of the polygon. I am using something like this SELECT ST_MakeLine(a.geom, b.geom) AS geom FROM points a CROSS JOIN points b JOIN poly ON ST_Contains(poly.geom_s, ST_MakeLine(a.geom, b.geom)) WHERE a.id < b.id; however it is utterly slow. Do you have any ideas how to speed this up? Aug 9, 2021 at 16:04
• Hello @TimothyDalton, i think it goes beyond the comment session. I suggest asking it either as a separate question or texting me in discourd. It will be kind if you can provide a piece of data for testing purposes. How big is your data? Are you working with PostGIS? Mind that a Virtual Layer is good but only to a certain extend. Aug 10, 2021 at 6:55