[postgis-users] Recursive intersect

[Leslie Viljoen]

Hi everyone!

I am having a hard time finding polygons intersecting other polygons
recursively. My "projects" relation has an id and a geom. I'd like to start
with a project, select all the conflicting (intersecting) projects, then
select the projects that conflict with that, etc.
As near as I can figure, I need a recursive query, so after many attempts I
have this:

WITH RECURSIVE tp(project_id, conflict_id, conflict_geom) AS
(
 SELECT projects.id, conflicts.id, conflicts.the_geom
 FROM projects, projects as conflicts
 WHERE projects.id = 1740 and (ST_Intersects(projects.the_geom,
conflicts.the_geom))
 UNION
 SELECT conflicts.id, tp.conflict_id, tp.conflict_geom
 FROM tp, projects as conflicts where (ST_Intersects(tp.conflict_geom,
conflicts.the_geom))
)
SELECT * from tp;


This gives me:
ERROR:  could not implement recursive UNION
DETAIL:  All column datatypes must be hashable.


I assume that's because of the geometry - can UNION not work with a geom?


My ultimate goal is for my program to get a list of all the conflicts and
conflicts of conflicts (and conflicts of conflicts of conflicts...) for
every project, like so:

id   conflicts
1    7, 6, 3, 4
3    1, 19, 32


I already do this programmatically , but its too slow.


I'd appreciate any tips!

Leslie
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120125/418db004/attachment.html>