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
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users