1

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?

  • I would check AND rowid.points1<rowid.points2. That would also make your geometry comparison unnecessary. – user30184 Oct 12 '14 at 13:50
  • @user30184, it is points1.rowid I guess. Are you sure this should work? I tried your advise and I got an empty result. – SS_Rebelious Oct 12 '14 at 15:47
  • 1
    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) – user30184 Oct 12 '14 at 16:00
  • @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. – SS_Rebelious Oct 12 '14 at 21:24
2

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

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.