I have lines overlapping in one layer as in image below. How do I delete one of these lines and leave the other one in the dataset?

enter image description here


you can use an SQL query to delete them.

Before doing so, be warned that it can lead to surprises should more than 2 lines intersects each others. It is also suspicious to delete a random line between the two intersecting ones. --> make a backup before deleting anything.

That being said, the query would refer twice to the same table: once for deleting the rows and once for identifying intersecting rows.

The line tmp.id < t1.id is used to delete the line having the smallest ID between the two intersecting ones. It also filter self deletion (a line intersects itself)

FROM t1 
  FROM t1 tmp 
  WHERE tmp.id < t1.id
   AND ST_INTERSECTS(tmp.geom, t1.geom));
  • Hi, this worked super. Just wondering how does it work. I am unsure what SELECT 1 FROM t1 tmp is doing? I dont understand what 1 or tmp means in this case. Thanks – user1655130 Oct 21 '18 at 17:48
  • 1
    @user1655130 See this post. Basically you can select whatever you want (1,*,null ... or event nothing at all), the exists keyword is the important one (get from t1 any row that match the content of the exists clause). As for tmp is it just a table alias. You can't use t1 directly as if it used in the delete from t1, so you assign a temporary name – JGH Oct 21 '18 at 18:07

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.