[postgis-users] Chained Intersections?

[Curtis W. Ruck]

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...

It appears the arrays don't have any information in them when i go to return the information...

Argh... I wish i had some SQL fu ...

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?

CREATE OR REPLACE FUNCTION intersection_all(in_polys geometry, OUT id int, OUT intersection_count int, OUT the_geom geometry)
  RETURNS SETOF record AS
$BODY$
DECLARE
    total_count int := 0;
    current_level_count int := 0;
    first_id_of_previous_level int := 0;
    last_id_of_previous_level int:= 0;
    num_intersections int ARRAY[0];
    geometries geometry ARRAY[0];
BEGIN
    -- handle level one by adding all input polygons
    FOR i IN 1..NumGeometries(in_polys) LOOP
        the_geom := GeometryN(in_polys,i);
        intersection_count := 1;
        geometries := array_append(geometries, the_geom);
        num_intersections := array_append(num_intersections, intersection_count);
        last_id_of_previous_level := last_id_of_previous_level+1;
        total_count := total_count +1;
        id := total_count;
        RAISE NOTICE 'Inserting polygon number: % at level: %',total_count,1;
        RETURN NEXT;
    END LOOP;
 
    -- handle all higher levels
    FOR level IN 2..100 LOOP
        RAISE NOTICE 'level: %',level;
        FOR i IN first_id_of_previous_level..last_id_of_previous_level LOOP
            RAISE NOTICE 'i: %',i;
            FOR j IN (i+1) .. last_id_of_previous_level LOOP
                RAISE NOTICE 'j: %   last_id_of_previous_level: %',j,last_id_of_previous_level;
                IF Intersects(geometries[i],geometries[j]) THEN
                    the_geom := Intersection(geometries[i],geometries[j]);
                    intersection_count := level;
                    current_level_count := current_level_count+1;
                    geometries := array_append(geometries, temp_geom);
                    num_intersections := array_append(num_intersections, level);
                    total_count := total_count +1;
                    id := total_count;
                    RAISE NOTICE 'Inserting polygon number %',total_count;
                    RETURN NEXT;
                END IF;
            END LOOP;
        END LOOP;
        IF current_level_count > 0 THEN
            first_id_of_previous_level := last_id_of_previous_level+1;
            last_id_of_previous_level := first_id_of_previous_level+current_level_count;
            current_level_count := 0;
        ELSE 
            EXIT;
        END IF;
    END LOOP;
 
    RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;

Input Polygons (srid 4326):
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))
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))
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))


----- Original Message ----
From: Mark Cave-Ayland <mark.cave-ayland at ilande.co.uk>
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Sent: Tuesday, October 10, 2006 6:01:18 AM
Subject: Re: [postgis-users] Chained Intersections?

On Mon, 2006-10-09 at 22:23 -0700, Curtis W. Ruck wrote:

> 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).
> 
> Does anyone have any suggestions on how to warp Postgres/PostGIS into
> doing this?
> 
> Curtis W. Ruck
> <unnamed large entity>


Hi Curtis,

Can you provide examples of the queries you have tried (along with any
error messages that you get). Subqueries and aggregates in a FROM clause
work fine, for example:

    select * from (select count(*) FROM pg_class) AS foo;


Kind regards,

Mark.


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061010/40a928e7/attachment.html>