<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><div style="font-family: arial,helvetica,sans-serif; font-size: 10pt;">Hmm, i just tried to recreate the error message i got and using this sql `select * from intersection_all( (select collect(the_geom) from polygons) );` It returns 3 blank records... That means that the function is working correctly to an extent, and i just need to have it actually return information...<br><br>It appears the arrays don't have any information in them when i go to return the information...<br><br>Argh... I wish i had some SQL fu ...<br><br>Alright after some toying around i was able to make it return some rows... Below is the new function. Now it only returns the 3 input rows (following the function). I reworked the function to return the rows as it creates them which leads me to believe my SQL is broken in
assembling the storage arrays num_intersections & geometries; From what i can tell it doesn't store anything to the arrays. Anyone have any ideas?<br><br>CREATE OR REPLACE FUNCTION intersection_all(in_polys geometry, OUT id int, OUT intersection_count int, OUT the_geom geometry)<br> RETURNS SETOF record AS<br>$BODY$<br>DECLARE<br> total_count int := 0;<br> current_level_count int := 0;<br> first_id_of_previous_level int := 0;<br> last_id_of_previous_level int:= 0;<br> num_intersections int ARRAY[0];<br> geometries geometry ARRAY[0];<br>BEGIN<br> -- handle level one by adding all input polygons<br> FOR i IN 1..NumGeometries(in_polys) LOOP<br> the_geom := GeometryN(in_polys,i);<br> intersection_count := 1;<br>
geometries := array_append(geometries, the_geom);<br> num_intersections := array_append(num_intersections, intersection_count);<br> last_id_of_previous_level := last_id_of_previous_level+1;<br> total_count := total_count +1;<br> id := total_count;<br> RAISE NOTICE 'Inserting polygon number: % at level: %',total_count,1;<br> RETURN NEXT;<br> END LOOP;<br> <br> -- handle all higher levels<br> FOR level IN 2..100 LOOP<br> RAISE NOTICE 'level: %',level;<br> FOR i IN first_id_of_previous_level..last_id_of_previous_level LOOP<br> RAISE NOTICE 'i:
%',i;<br> FOR j IN (i+1) .. last_id_of_previous_level LOOP<br> RAISE NOTICE 'j: % last_id_of_previous_level: %',j,last_id_of_previous_level;<br> IF Intersects(geometries[i],geometries[j]) THEN<br> the_geom := Intersection(geometries[i],geometries[j]);<br> intersection_count := level;<br> current_level_count := current_level_count+1;<br> geometries := array_append(geometries,
temp_geom);<br> num_intersections := array_append(num_intersections, level);<br> total_count := total_count +1;<br> id := total_count;<br> RAISE NOTICE 'Inserting polygon number %',total_count;<br> RETURN NEXT;<br> END IF;<br> END LOOP;<br> END LOOP;<br> IF current_level_count > 0 THEN<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> ELSE <br> EXIT;<br> END IF;<br> END LOOP;<br> <br> RETURN;<br>END;<br>$BODY$<br> LANGUAGE 'plpgsql' IMMUTABLE;<br><br>Input Polygons (srid 4326):<br>POLYGON((-0.998092458778228 1.17422642209203,-1.14314395797783 0.490412211579614,-0.821958495464423 -0.383350390741811,0.255566927161207 -0.138144284952004,0.33845349813241 0.766700781483622,-0.35917514087521 1.26402020731084,-0.35917514087521 1.26402020731084,-0.587113211046016 1.26056660018704,-0.587113211046016
1.26056660018704,-0.998092458778228 1.17422642209203))<br>POLYGON((0.290102998399208 1.20876249333003,-0.00690721424760012 0.849587352454824,-0.172680356190005 0.407525640608412,0.386803997865611 5.89805981832114e-17,1.15695838647303 0.34881431950381,1.34690677828204 1.02572131576863,0.946288351921227 1.33999956403444,0.528401889941415 1.42633974212944,0.324639069637209 1.34690677828204,0.290102998399208 1.20876249333003))<br>POLYGON((-0.34536071238001 -0.72871110312182,-0.483504997332014 -0.179587570437605,-0.203762820304206 0.0794329638474023,0.321185462513409 0.252113320037407,0.877216209445225 0.0863401780950025,1.04989656563523 -0.35572153375141,0.745979138740821 -0.967009994664027,-0.120876249333003 -0.877216209445225,-0.34536071238001 -0.72871110312182))<br><br><br><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;">----- Original Message ----<br>From: Mark Cave-Ayland <mark.cave-ayland@ilande.co.uk><br>To: PostGIS Users Discussion
<postgis-users@postgis.refractions.net><br>Sent: Tuesday, October 10, 2006 6:01:18 AM<br>Subject: Re: [postgis-users] Chained Intersections?<br><br><div>On Mon, 2006-10-09 at 22:23 -0700, Curtis W. Ruck wrote:<br><br>> I wrote a pl/pgsql function that i thought would do this, far below<br>> labeled Intersection_All. The problem with this is that it needs to<br>> be called from a FROM clause because it returns a SETOF records, but<br>> within a FROM clause i can't do an aggregate method collect(the_geom).<br>> (I also tried a subquery in the FROM clause).<br>> <br>> Does anyone have any suggestions on how to warp Postgres/PostGIS into<br>> doing this?<br>> <br>> Curtis W. Ruck<br>> <unnamed large entity><br><br><br>Hi Curtis,<br><br>Can you provide examples of the queries you have tried (along with any<br>error messages that you get). Subqueries and aggregates in a FROM clause<br>work fine, for
example:<br><br> select * from (select count(*) FROM pg_class) AS foo;<br><br><br>Kind regards,<br><br>Mark.<br><br><br>_______________________________________________<br>postgis-users mailing list<br>postgis-users@postgis.refractions.net<br><a target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></div><br></div></div></body></html>