Hi, I'm having difficulty understanding what your question is. Could you explain better what you expect to happen with your query and how your result differs from your expectation?
–
Jay GuarneriMar 27 '13 at 14:36

1.: I want to get all independent roadnets. 2: ST_Intersection is syntactically wrong in a GROUP statement.
–
youseeusMar 28 '13 at 10:28

2 Answers
2

Your example may not be the best since it only contains two groups so I added line(point(6 12) point(8 7)) which intersects with 5.
This should be possible with a recursive CTE. This works on the test data:

WITH RECURSIVE
inter_agg AS
(
SELECT r.id,array[r.id]||array_agg(r2.id) as arr FROM
roads AS r
JOIN roads AS r2
ON r.id<>r2.id
WHERE ST_Intersects(r.way,r2.way)
GROUP BY r.id
),
final AS
(
SELECT i.id,arr as inter, array[i.id] as ex FROM inter_agg AS i
UNION ALL
SELECT f.id,f.inter&i.arr,ex||array[i.id]
FROM final AS f
JOIN inter_agg AS i
ON f.id<i.id AND f.inter@>ARRAY[i.id] AND (f.ex@>ARRAY[i.id]) IS FALSE
)
SELECT inter FROM final WHERE inter=ex;

@youseeus I hope it does. I looked up graph theory and it turns out I did a query to find clique and you wanted unconnected trees. If you want to find that other thing after all I'll see what I can do.
–
Jakub KaniaMar 28 '13 at 16:08

INSERT INTO roads (id , way) VALUES (7,ST_GeomFromText ( 'LINESTRING (0 0, 2 0)', 900913 ) );
WITH RECURSIVE
inter_agg AS
(
SELECT r.id,array[r.id]||array_agg(r2.id) as arr FROM
roads AS r
JOIN roads AS r2
ON r.id<>r2.id
WHERE ST_Intersects(r.way,r2.way)
GROUP BY r.id
),
final AS
(
SELECT distinct i.id,arr as inter, array[i.id] as ex FROM inter_agg AS i
UNION ALL
SELECT f.id,uniq(sort(f.inter||i.arr)),uniq(sort(ex||array[i.id]))
FROM final AS f
JOIN inter_agg AS i
ON f.id<i.id AND f.inter@>ARRAY[i.id] AND (f.ex@>ARRAY[i.id]) IS FALSE
)
SELECT distinct inter FROM final WHERE inter=ex;

One should take into account, that this query tends to be extremly slow when input consists large trees.

My task was to find intersected polygons. I had about 60 polygons in experemental input
(up to 13 connected polygons in each tree, 8 trees overall). Execution time was about 500ms. Addition of the one big polygon, which united 21 polygons, led to 8s execution time.