Re: [postgis-users] Recursive intersect
On Thu, Jan 26, 2012 at 9:33 PM, Nicolas Ribot nicolas.ri...@gmail.comwrote: 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
[postgis-users] Recursive intersect
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. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Recursive intersect
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 17, 6, 3, 4 31, 19, 32 I already do this programmatically , but its too slow. I'd appreciate any tips! Leslie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
