[postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

Wed Oct 15 02:03:10 PDT 2008

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: test_mv_dlm07_dump.dmp
Type: application/octet-stream
Size: 17581 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081015/13efc406/attachment.obj>