[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Recursive intersect
From: Leslie Viljoen <leslieviljoen () gmail ! com>
Date: 2012-01-30 21:40:17
Message-ID: CAGFQMzR4EAGw8sZB+6NSUsHeX_xujhRF-+tXr_zW4DXgTXkHSQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
On Thu, Jan 26, 2012 at 9:33 PM, Nicolas Ribot <nicolas.ribot@gmail.com>wrote:
> >>
> >> From: Nicolas Ribot <nicolas.ribot@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
[Attachment #5 (text/html)]
<br><br><div class="gmail_quote">On Thu, Jan 26, 2012 at 9:33 PM, Nicolas Ribot <span \
dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>></span> \
wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex">
<div class="HOEnZb"><div class="h5">>><br>
>> From: Nicolas Ribot <<a \
href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>><br> \
>><br> >> Hi<br>
>><br>
>> WITH RECURSIVE needs an UNION ALL to link the "non recursive" \
term<br> >> with the "recursive" one.<br>
>><br>
><br>
> Wow, that at least runs... though I think the recursion might be infinite.<br>
> Thanks for the tip Nicolas.<br>
><br>
<br>
</div></div>Hi,<br>
<br>
Concerning the infinite iteration, one trick could be to use a boolean<br>
value to test if some condition is reached (for instance, no more<br>
polygon is found). Using an array to accumulate some values and test<br>
the current value against the array may be very efficient to control<br>
the iteration. Here is an extract of code that uses array to store<br>
already treated values and compare this list with the current id: (the<br>
purpose of this query was to find buildings by proximity search from a<br>
given building)<br>
<br>
with recursive mon_select as (<br>
select -1 as id, ref, array[-1] as ids, 1 as depth, geometry from<br>
table_ori where ref = 1<br>
<br>
UNION ALL<br>
<br>
select distinct on(s.gid) s.gid as sel_gid, m.ref, m.ids ||<br>
s.gid, m.depth+1, s.geometry<br>
from table_selection s, mon_select m<br>
where st_touches(m.geometry, s.geometry)<br>
and not (s.gid = any(ids))<br>
) select distinct on (id) id, ref, ids, depth, geometry from mon_select;<br>
<br>
The key parts are:<br>
• The array[-1] in the non-recursive term, to initiate the array<br>
• the "m.ids || s.gid" array concatenation in the select, to fill<br>
up the array of ids<br>
• the "not (s.gid = any(ids))" in the where clause, returning true<br>
if any value in the arrays (ids) meets the "s.gid = ..." condition.<br>
<br></blockquote><div><br></div><div>Thank you very much for your help with this, \
I'll give it a try.</div><div><br></div><div>Leslie</div><div><br></div></div>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic