On Thu, Jan 26, 2012 at 9:33 PM, Nicolas Ribot <nicolas.ri...@gmail.com>wrote:
> >> > >> From: Nicolas Ribot <nicolas.ri...@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
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users