How to create non-duplicate lines between all selected points in Spatialite?

Question

There is a nice answer for the similar question for PostGIS.

I tried to adapt it for my case where I need to connect all points from a point layer, but unfortunately I get duplicate geometries: lines from point n to point m and from point m to point n. Here is the query I use. check_points is the point layer and cp_path is the layer where I want to store created lines.

WITH points1 AS (SELECT geometry FROM check_points),
 points2 AS (SELECT geometry FROM check_points)
INSERT INTO cp_path (geometry) 
SELECT * FROM (SELECT DISTINCT MakeLine(points1.geometry, points2.geometry)
 FROM points1, points2
 WHERE points1.geometry != points2.geometry)

How can I avoid duplicated geometries?

• user30184: I would check AND rowid.points1<rowid.points2. That would also make your geometry comparison unnecessary.
• SS_Rebelious: @user30184, it is points1.rowid I guess. Are you sure this should work? I tried your advise and I got an empty result.
• user30184: I did not test but yes, it works after selecting also the rowid columns with this unpolished query WITH points1 AS (SELECT rowid,geometry FROM points), points2 AS (SELECT rowid,geometry FROM points) INSERT INTO cp_path (geometry) SELECT * FROM (SELECT DISTINCT MakeLine(points1.geometry, points2.geometry) FROM points1, points2 WHERE points1.geometry != points2.geometry AND points1.rowid<points2.rowid)
• SS_Rebelious: @user30184, ok that one worked, thank you. Consider posting this as an answer with a bit more in detail explanation what happens when this condition is added to the query.

Answer 1

This reminds the old quiz: 5 person will shake hands with each other. How many handshakes will there be in total? Is it 20 because everybody must shake hands with 4 other person? No, because if Mike shakes hands with John, John does not need to shake hands with Mike for another time.

Let's assume that you have 5 points. Point number 1 does not shake hands with herself, but only with points 2, 3, 4, and 5. When point number 2 is in turn, she quits number 1 and herself and shakes hands only with 3, 4, and 5.

With your data the handshake query becomes as:

WITH points1 AS (SELECT rowid,geometry FROM check_points),
 points2 AS (SELECT rowid,geometry FROM check_points) 
INSERT INTO cp_path (geometry) 
SELECT * FROM (SELECT MakeLine(points1.geometry, points2.geometry)
FROM points1, points2 
WHERE points1.rowid<points2.rowid);