This query also works fine for me on this sample SELECT 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 test_mv_dlm07_dump 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
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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 > [email protected] > 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 > [email protected] > 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: [EMAIL PROTECTED] Internet: www.vti.bund.de _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
