[postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

[Obe, Regina]

Birgit,

I just loaded up this data set you posted and tried doing

SELECT ST_X(ST_Centroid(the_geom))
FROM test_mv_dlm07_dump

Works perfectly fine for me.   Even if you pass a geometry collection to ST_Centroid, it would always return a point.

To demonstrate

SELECT ST_GeometryType(ST_Centroid(ST_Collect(the_geom))), ST_AsText(ST_Centroid(ST_Collect(the_geom))) as full_cent
FROM test_mv_dlm07_dump;

Gives me this:
"ST_Point";"POINT(3728516.33771743 5964986.31004069)"


Are you saying the sample you posted when you do the above doesn't work for you?  If so, then there appears to be something wrong with your install.

What does 

SELECT postgis_full_version();

return for you?

Thanks,
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: Wednesday, October 15, 2008 5:03 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

Hi Regina,

thanks for your response! I tried the query you suggested and still get 
the same result (error: Argument to X() must be a point), which is 
totally reasonable, regarding that the result of the ST_Centroid is 
still a GeometryCollection. My guess was, that this is caused by the 
fact that ST_Dump returns not only POLYGONs but also LINESTRINGs and 
POINTs. I thought, it could be that ST_Centroid has difficulties 
returning a centroid for points and linestrings. So, what I did so far, 
was to split the query into one part, with which I just find all 
overlapping geometries without grouping or anything else, and one part, 
which does the grouping including calculating of the centroid and 
everything else. And, between these two parts, I just delete all 
geometries that are LINESTRINGs or POINTs (I don't need them, I only 
need the polygons). At least, these queries run - I don't know yet if 
the result is correct...
But, as I saw now, when I selected the data example you asked for (see 
attachement), that the GeometrieCollections don't occur at the 
linestring or point geometries as I expected. There seems to be some 
other problem, I don't understand...

Here my new (working) query:

CREATE TABLE bfn.mv_dlm07_olvg
(
  gid serial NOT NULL,
  gid_mv_dlm07 integer
);
SELECT 
AddGeometryColumn('bfn','mv_dlm07_olvg','the_geom','31467','MULTIPOLYGON',2);

ALTER TABLE bfn.mv_dlm07_olvg DROP CONSTRAINT enforce_geotype_the_geom;

INSERT INTO bfn.mv_dlm07_olvg (
  gid_mv_dlm07,
  the_geom)
SELECT
  a.gid,
  (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom AS the_geom
FROM bfn.mv_dlm07_orig a, bfn.mv_dlm07_orig b
WHERE ST_OVERLAPS(a.the_geom,b.the_geom)=true AND 
INTERSECTS(a.the_geom,b.the_geom)=true;

DELETE FROM bfn.mv_dlm07_olvg
WHERE GeometryType(the_geom)='LINESTRING';
DELETE FROM bfn.mv_dlm07_olvg
WHERE GeometryType(the_geom)='POINT';

CREATE TABLE bfn.mv_dlm07_ol
(
  gid serial NOT NULL,
  gid_mv_dlm07 integer,
  x_centroid numeric,
  y_centroid numeric,
  area numeric,
  perimeter numeric
);
SELECT 
AddGeometryColumn('bfn','mv_dlm07_ol','the_geom','31467','MULTIPOLYGON',2);

ALTER TABLE bfn.mv_dlm07_ol DROP CONSTRAINT enforce_geotype_the_geom;

INSERT INTO bfn.mv_dlm07_ol (
  gid_mv_dlm07,
  x_centroid,
  y_centroid,
  area,
  perimeter,
  the_geom)
SELECT
  a.gid_mv_dlm07,
  st_x(st_centroid(a.the_geom)) as x_centroid,
  st_y(st_centroid(a.the_geom)) as y_centroid,
  st_area(a.the_geom) as area,
  st_perimeter(a.the_geom) as perimeter,
  a.the_geom as the_geom
FROM bfn.mv_dlm07_olvg a
GROUP BY st_x(st_centroid(a.the_geom)), st_y(st_centroid(a.the_geom)), 
st_area(a.the_geom), st_perimeter(a.the_geom), a.the_geom
ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc, 
a.gid_mv_dlm07 asc;

Regards,

Birgit.

 

Obe, Regina schrieb:
> 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.  
Is attached - these are the ones which return GeometryCollection after 
ST_Centroid. I had to remove the attributes because of privacy reasons.

> 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