[postgis-users] Recursive intersect

[Nicolas Ribot]

>>
>> From: Nicolas Ribot <nicolas.ribot at gmail.com>
>>
>> Hi
>>
>> WITH RECURSIVE needs an UNION ALL to link the "non recursive" term
>> with the "recursive" one.
>>
>
> Wow, that at least runs... though I think the recursion might be infinite.
> Thanks for the tip Nicolas.
>

Hi,

Concerning the infinite iteration, one trick could be to use a boolean
value to test if some condition is reached (for instance, no more
polygon is found). Using an array to accumulate some values and test
the current value against the array may be very efficient to control
the iteration. Here is an extract of code that uses array to store
already treated values and compare this list with the current id: (the
purpose of this query was to find buildings by proximity search from a
given building)

with recursive mon_select as (
       select -1 as id, ref, array[-1] as ids, 1 as depth, geometry from
table_ori where ref = 1

       UNION ALL

       select distinct on(s.gid) s.gid as sel_gid, m.ref, m.ids ||
s.gid, m.depth+1, s.geometry
       from table_selection s, mon_select m
       where st_touches(m.geometry, s.geometry)
       and not (s.gid = any(ids))
) select distinct on (id) id, ref, ids, depth, geometry from mon_select;

The key parts are:
    • The array[-1] in the non-recursive term, to initiate the array
    • the "m.ids || s.gid" array concatenation in the select, to fill
up the array of ids
    • the "not (s.gid = any(ids))" in the where clause, returning true
if any value in the arrays (ids) meets the "s.gid = ..." condition.

HTH.

Nicolas