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?

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);