Hi WITH RECURSIVE needs an UNION ALL to link the "non recursive" term with the "recursive" one.
Nicolas On 25 January 2012 05:50, Leslie Viljoen <[email protected]> wrote: > 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
