[postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

[Birgit Laggner]

Hi Chris,

thanks for your response. Normally, I shouldn't have multiple geometries 
because of the ST_Dump and I don't have any when I ask for 
Geometrytype() of the ST_Dump result. But still, splitting the GROUP BY 
from the INTERSECTION was seemingly a good idea (see my e-mail to Regina).

Regards,

Birgit.

Chris Hermansen schrieb:
> Is it possible the GROUP BY produces geometry collections when there are
> multiple geometries returned?  That seems at least plausible to me.
>
> Maybe if you try to look at what's returned with and without the GROUP
> BY in the cases where multiple geometries are returned (HAVING would
> help there).
>
> Obe, Regina wrote:
>   
>> Birgit,
>>
>> So you are saying ST_Centroid sometimes returns geometry collections.
>> Hmm I don't see how that is possible unless there is bug somewhere.
>>
>> Can you provide an example of that.  Regarding the below query - it
>> would be a bit easier to follow if you did a subselect first.
>>
>> Something like
>>
>>  SELECT
>>   abdump.gid,
>>   abdump.objart_07,
>>  abdump.cat_07,
>> ST_X(ST_Centroid(abdump.newgeom)) As x_centroid,
>> ST_Y(ST_Centroid(abdump.newgeom)) As y_centroid
>> ST_Area(ST_Centroid(abdump.newgeom)) As area,
>> ST_Perimeter(ST_Centroid(abdump.newgeom)) as perimeter
>> FROM (SELECT (ST_Dump(ST_Intersection(a.the_geom, 
>> b.the_geom))).geom As newgeom, a.gid, a.objart_07, a.cat_07, a.the_geom
>> as a_the_geom, b.the_geom as b_the_geom
>> FROM 
>>  bfn.test_mv_dlm07 a, bfn.test_mv_dlm07 b
>> WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
>> Intersects(a.the_geom,b.the_geom)=true
>> ) As abdump
>> GROUP BY ST_X(ST_Centroid(abdump.newgeom)), 
>> ST_Y(ST_Centroid(abdump.newgeom)), ST_Area(ST_Centroid(abdump.newgeom)),
>>
>> ST_Perimeter(ST_Centroid(abdump.newgeom)), abdump.gid, abdump.objart_07,
>> abdump.cat_07, a_the_geom, b_the_geom
>> ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc, a.gid
>> asc;
>>
>> Hope that helps,
>> Regina
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>> Birgit Laggner
>> Sent: Tuesday, October 14, 2008 6:56 AM
>> To: PostGIS Users Discussion
>> Subject: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)
>>
>> Dear list,
>>
>> I have a SELECT statement in which I try to group by the polygons 
>> resulting from a self intersection. After I realized that I can't group 
>> by the_geom because this would group polygons which are only similar but
>>
>> not equal, I decided to group by the area, the perimeter and the x- and 
>> y-coordinates of the centroid of every polygon. The SELECT statement I 
>> wrote, worked so far for some test datasets and showed seemingly 
>> reasonable results. But for at least one of my datasets, the statement 
>> does not work, because the ST_Centroid produces some GeometryCollections
>>
>> instead of POINTS. What could cause that problem and how can I solve
>> it???
>>
>> This is my statement:
>>
>> SELECT
>>   a.gid,
>>   a.objart_07,
>>   a.cat_07,
>>   ST_X(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
>> b.the_geom))).geom)) as x_centroid,
>>   ST_Y(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
>> b.the_geom))).geom)) as y_centroid,
>>   ST_Area((ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom) as
>> area,
>>   ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom) 
>> as perimeter,
>>   (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom as the_geom
>> FROM bfn.test_mv_dlm07 a, bfn.test_mv_dlm07 b
>> WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
>> Intersects(a.the_geom,b.the_geom)=true
>> GROUP BY ST_X(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
>> b.the_geom))).geom)), 
>> ST_Y(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
>> b.the_geom))).geom)), ST_Area((ST_Dump(ST_Intersection(a.the_geom, 
>> b.the_geom))).geom), ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom, 
>> b.the_geom))).geom), a.gid, a.objart_07, a.cat_07, a.the_geom,
>> b.the_geom
>> ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc, a.gid
>> asc;
>>
>> The ST_Dump results in POLYGONs, LINESTRINGs and POINTs.
>>
>> Thank you for any help!
>>
>> Regards,
>>
>> Birgit.
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> -----------------------------------------
>> The substance of this message, including any attachments, may be
>> confidential, legally privileged and/or exempt from disclosure
>> pursuant to Massachusetts law. It is intended
>> solely for the addressee. If you received this in error, please
>> contact the sender and delete the material from any computer.
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>   
>>     
>
>
>   

-- 
Dipl.-Geoökol. Birgit Laggner

Johann Heinrich von Thünen-Institut,
Bundesinstitut für Ländliche Räume, Wald und Fischerei
Institut für Ländliche Räume
Bundesallee 50
38116 Braunschweig

Johann Heinrich von Thünen-Institute
Federal Research Institute for Rural Areas, Forestry and Fisheries
Institute of Rural Areas
Bundesallee 50
D-38116 Braunschweig
Germany

Tel.: (0531) 596 - 5240
Fax: (0531) 596 - 5599
E-Mail: birgit.laggner at vti.bund.de
Internet: www.vti.bund.de