[postgis-users] Select largest polygon of a multipolygon ?

[Arnaud Lesauvage]

Mark Cave-Ayland wrote:
>> > See dump() and the two previous posts.
>> > (yes, I understood you made it, but might be useful for others)
>> 
>> I tried with dump(), but I am not familiar with set returning
>> functions at all, and postgresql's documentation is not very
>> expansive on this matter.
>> 
>> For example :
>> SELECT gid, Area(geom(Dump(uu_geometry))) FROM uu
>> GROUP BY gid, Area(geom(Dump(uu_geometry)));
>> works fine, but
>> SELECT gid, Max(Area(geom(Dump(uu_geometry)))) FROM uu
>> GROUP BY gid;
>> does not work : "set-valued function called in context that cannot
>> accept a set"
>> 
>> Why is it so ?

> I've just tried the query in the previous email and noticed from the EXPLAIN
> ANALYZE that the query was not correct because the planner was collapsing
> the "gid = uu.gid" clause into simply "gid = gid". The following uses an
> alias so that the planner realizes it needs to set uu_inner.gid to the
> current value of gid from the outer part of the query, and not collapse the
> WHERE clause to read "gid = gid":
> 
> 
> SELECT uu.gid, (SELECT max(polygons.area) from (SELECT
> area(geom(dump(uu_geometry))) FROM uu AS uu_inner WHERE uu_inner.gid =
> uu.gid) AS polygons) AS maxarea FROM uu;


Hi Mark !
Thanks for your answers !

"SELECT uu.gid, (SELECT max(polygons.area) from (SELECT 
area(geom(dump(uu_geometry))) FROM uu AS uu_inner WHERE 
uu_inner.gid = uu.gid) AS polygons) AS maxarea FROM uu;"

Wow o_O ! I think I'd rather stay with my plpgsql loop... ;-)

Thanks a lot for pointing out the answer though !

--
Arnaud