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?
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:
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:
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.
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.
p1.id || '-' || p2.id AS Name,
points AS p1,
points AS p2
p1.id <> p2.id
The output will look like