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

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Arnaud Lesauvage
> Sent: 12 July 2006 09:32
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Select largest polygon of a multipolygon ?
> 
> strk at refractions.net wrote:
> > On Tue, Jul 11, 2006 at 07:29:44PM +0200, Arnaud Lesauvage wrote:
> >> Nahum Castro a écrit :
> >> >select the area, order by area desc and limit to 1
> >>
> >> You did not understand the question nore did you read previous
> >> answers.
> >> "Is there a simple way to get the largest polygon of a
> >> multipolygon geometry ?"
> >> I can't find another way to explain that, but it seems that my
> >> question is not clear.
> >
> > 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 ?
> 
> --
> Arnaud


Hi Arnaud,

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;


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.