Let's said that I have this line network:
The goal is to find the longest connected subnetwork. So I want to find all the line's ID that are connected to the line with the ID = 1 because I know that the ID=1 is part of the longest connected subnetwork.
I guess that have to use a recursive query, I have try to run the following query:
WITH RECURSIVE connected_line AS ( SELECT a.ID,a.geom FROM network a WHERE ID = 1 UNION ALL SELECT b.ID,b.geom FROM network b, connected_line c WHERE st_intersects(b.geom, c.geom) AND NOT b.ID = c.ID ) SELECT ID FROM connected_line;
The result should be:
ID 1 2 3 4
But the query is endless, how to create such a recursive query ?