[postgis-users] How to get n largest (multi-)polygons contained in parent (multi-)polygon?

[Scholle]

Hi, 

having an arbitrary (multi-)polygon A of area a (ST_Area), I want to get the
n largest (B0...Bn) (multi-)polygons which are contained (ST_Contains) in A,
with ST_Area(B0) >= ST_Area(B1) >= ST_Area(B2),...

What I have got so far: 

CREATE OR REPLACE FUNCTION getplaceswithinplace(place_id int4, x int)
RETURNS SETOF places AS $$
DECLARE
cnt INT;
rr RECORD;
area float8;
parent RECORD;
BEGIN
  SELECT * INTO parent FROM places WHERE id = place_id;
  area := ST_Area(parent.geom_multi_polygon_xy_srid_4326);
  LOOP
    SELECT COUNT(*) INTO cnt FROM places WHERE
ST_Area(geom_multi_polygon_xy_srid_4326) >= area &&
ST_Contains(parent.geom_multi_polygon_xy_srid_4326,
geom_multi_polygon_xy_srid_4326) LIMIT x;
    IF FOUND AND cnt >= x THEN
      FOR rr IN (SELECT * FROM places WHERE
ST_Area(geom_multi_polygon_xy_srid_4326) >= area &&
ST_Contains(parent.geom_multi_polygon_xy_srid_4326,
geom_multi_polygon_xy_srid_4326) LIMIT x) LOOP
        RETURN NEXT rr;
      END LOOP;
      RETURN;
    ELSE
      area := area * 0.99;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Just wondering how this piece of code might be improved...

Thanks for any feedback...


-- 
View this message in context: http://old.nabble.com/How-to-get-n-largest-%28multi-%29polygons-contained-in-parent-%28multi-%29polygon--tp32105569p32105569.html
Sent from the PostGIS - User mailing list archive at Nabble.com.