[postgis-users] GeometryCollection after ST_Centroid(ST_Dump)
Obe, Regina
robe.dnd at cityofboston.gov
Wed Oct 15 05:04:10 PDT 2008
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: 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
More information about the postgis-users
mailing list