[postgis-users] Recursive intersect

[Nicolas Ribot]

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 <leslieviljoen at gmail.com> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>