<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><div>This is a requirement given to me. (I had no say in it, but it would make my application ultra cool)<br><br>I need to have the ability to output from the database all possible intersections over a set of polygons. I wrote a pl/pgsql function Intersection(GeometryCollection) that iterates over the geometry collection and returns the intersection of all given polygons. The problem is that if all of the polygons don't share an area then it returns nothing. I need to write a pl/pgsql function that takes a GeometryCollection and outputs a set of polygons and integers. Each polygon would have an integer showing how many polygons intersected to create that polygon.<br><br><span>For example, if you look at <a target="_blank"
href="http://orbyn.mine.nu/intersections/4polys.png">http://orbyn.mine.nu/intersections/4polys.png</a> (to save the mailing list the attachment) the input polygons (red, green, blue) should return (yellow, cyan, purple, white) from the function where yellow, cyan, purple have a value of 2 and white has a value of 3.</span><br><br>I wrote a pl/pgsql function that i thought would do this, far below labeled Intersection_All. The problem with this is that it needs to be called from a FROM clause because it returns a SETOF records, but within a FROM clause i can't do an aggregate method collect(the_geom). (I also tried a subquery in the FROM clause).<br><br>Does anyone have any suggestions on how to warp Postgres/PostGIS into doing this?<br><br>Curtis W. Ruck<br><unnamed large entity><br><br><br><pre class="code"><span class="comment"></span><span class="comment"></span><br><span class="keyword">CREATE</span> <span class="keyword">OR</span> <span
class="keyword">REPLACE</span> <span class="keyword">FUNCTION</span> intersection_all(in_polys geometry, <span class="keyword">OUT</span> intersection_count <span class="keyword">int</span>, <span class="keyword">OUT</span> the_geom geometry)<br> <span class="keyword">RETURNS</span> <span class="keyword">SETOF</span> record <span class="keyword">AS</span><br>$BODY$<br><span class="keyword">DECLARE</span><br> total_count <span class="keyword">int</span> := 0;<br> current_level_count <span class="keyword">int</span> := 0;<br> first_id_of_previous_level <span class="keyword">int</span> := 0;<br> last_id_of_previous_level <span class="keyword">int</span>:= 0;<br> num_intersections <span class="keyword">int</span> <span class="keyword">ARRAY</span>[0];<br> geometries geometry <span class="keyword">ARRAY</span>[0];<br> temp_geom geometry;<br><span class="keyword">BEGIN</span><br> <span class="keyword">FOR</span> i <span class="keyword">IN</span> 1..NumGeometries(in_polys)
LOOP<br> <span class="keyword">FOR</span> j <span class="keyword">IN</span> i+1..NumGeometries(in_polys) LOOP<br> IF Intersects(GeometryN(in_polys,i),GeometryN(in_polys,j)) <span class="keyword">THEN</span><br> temp_geom := Intersection(GeometryN(in_polys,i),GeometryN(in_polys,j));<br> geometries := geometries || temp_geom;<br> num_intersections := num_intersections || 1;<br> last_id_of_previous_level := last_id_of_previous_level+1;<br> total_count := total_count +1;<br> <span class="keyword">END</span> IF;<br> <span class="keyword">END</span> LOOP;<br> <span class="keyword">END</span> LOOP;<br> <br> <span class="keyword">FOR</span> <span class="keyword">level</span> <span class="keyword">IN</span> 2..100 LOOP<br> <span class="keyword">FOR</span> i <span class="keyword">IN</span> first_id_of_previous_level..last_id_of_previous_level LOOP<br> <span class="keyword">FOR</span> j <span class="keyword">IN</span> i+1..last_id_of_previous_level
LOOP<br> IF Intersects(geometries[i],geometries[j]) <span class="keyword">THEN</span><br> temp_geom := Intersection(geometries[i],geometries[j]);<br> current_level_count := current_level_count+1;<br> geometries := geometries || temp_geom;<br> num_intersections := num_intersections || <span class="keyword">level</span>;<br> total_count := total_count +1;<br> <span class="keyword">END</span> IF;<br> <span class="keyword">END</span> LOOP;<br> <span class="keyword">END</span> LOOP;<br> IF current_level_count > 0 <span class="keyword">THEN</span><br> first_id_of_previous_level := last_id_of_previous_level+1;<br> last_id_of_previous_level := first_id_of_previous_level+current_level_count;<br> current_level_count := 0;<br> <span class="keyword">ELSE</span> <br> EXIT;<br> <span class="keyword">END</span> IF;<br> <span class="keyword">END</span> LOOP;<br> <br> <span class="keyword">FOR</span> i <span class="keyword">IN</span>
1..total_count LOOP<br> the_geom = geometries[i];<br> intersection_count = num_intersections[i];<br> <span class="keyword">RETURN</span> <span class="keyword">NEXT</span>;<br> <span class="keyword">END</span> LOOP;<br> <span class="keyword">RETURN</span>;<br><span class="keyword">END</span>;<br>$BODY$<br> <span class="keyword">LANGUAGE</span> <span class="literal">'plpgsql'</span> <span class="keyword">IMMUTABLE</span>;<br><span class="keyword"></span><br></pre><br></div></div></body></html>