1

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?

example

4

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? – Filipe Dias Jul 17 at 10:44
  • Is riverscale a DB name or a schema name? If the former, you have your answer. – John Powell Jul 17 at 10:56
  • It's a schema name. – Filipe Dias Jul 17 at 10:57
  • oh, my bad, there is a period not a comma after b.id. Fixed. – John Powell Jul 17 at 10:58
  • 1
    You are welcome, please accept the answer if it works for you. – John Powell Jul 17 at 11:45
4

Tested on QGIS 2.18 and QGIS 3.4

I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

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

input

With the following Queries, 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 Virtual Layer will look like

output

  • 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. – John Powell Jul 17 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. – Taras Jul 17 at 10:27
  • 1
    Thanks for the complement. Your answer is perfectly good and answers the question as first stated. – John Powell Jul 17 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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