0

I'm working on a project of water supply which includes a layer representing the pipes. the operation that I want to do is generating a point layer where a pipe touches an other and then split it at this location. I found out that st_touches in PostGIS does it but I'm wondering if I can do it on the same layer because in the doc they were talking about two different layers or geomtries. How can I achieve this?

1
  • I answer your question but always is a good idea post the create table sentences and the queries that you are using now. Sep 27, 2017 at 8:32

1 Answer 1

1

Yes, you always can join a layer with itself. You only need to have a bit of care to not duplicate the results.

Assuming that id is the primary key of the table.

SELECT a.id FROM my_table a, my_table b
WHERE a.id < b.id AND ST_Touches(a.geom, b.geom);
5
  • here is what I did : SELECT pipe.id FROM pipe b, pipe d WHERE b.id < d.id and ST_Touches(b.geom, d.geom); and I got this error : invalid reference for column id. Sep 27, 2017 at 9:13
  • thanks it works.I had to specify it like this SELECT b.id, d.id FROM pipe b, pipe d WHERE b.id < d.id and ST_Touches(b.geom, d.geom); Sep 27, 2017 at 9:29
  • I update my answer Sep 27, 2017 at 10:01
  • Best practice is to use the JOIN clause.
    – Vince
    Sep 27, 2017 at 10:02
  • Actually, for this kind self-joins i usually use the cartesian product and not a join. Can you point to a link suggesting why is a good practice? I will update my answer according to it. Thanks. Sep 28, 2017 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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