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 bWHERE 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 aGROUP 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:
Is attached - these are the ones which return GeometryCollection after ST_Centroid. I had to remove the attributes because of privacy reasons.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 perimeterFROM (SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom As newgeom, a.gid, a.objart_07, a.cat_07, a.the_geomas a_the_geom, b.the_geom as b_the_geomFROM 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 abdumpGROUP 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 butnot 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 GeometryCollectionsinstead 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 bWHERE 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
test_mv_dlm07_dump.dmp
Description: Binary data
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
