[postgis-users] Recursive intersect

[Leslie Viljoen]

On Thu, Jan 26, 2012 at 9:33 PM, Nicolas Ribot <nicolas.ribot at gmail.com>wrote:

> >>
> >> 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.
>
>
Thank you very much for your help with this, I'll give it a try.

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