[postgis-users] GeometryCollection after ST_Centroid(ST_Dump)
Birgit Laggner
birgit.laggner at vti.bund.de
Wed Oct 15 06:55:30 PDT 2008
Hi Regina,
this is the result, I get from the query you sent me:
gid geometrytype_centroid astxtrep
integer text text
-----------------------------------------------------------------------------------------------------
119810 "GEOMETRYCOLLECTION" "POLYGON((3713601.737
5966193.371,3713601.737 5966193.371,3713601.737
5966193.371,3713601.737 5966193.371))"
Thanks and regards,
Birgit.
Obe, Regina schrieb:
> Birgit,
> Ah that is weird - I've never seen it return a geometry collection
> before. So what is the ST_AsText/ST_AsBinary of the first.
>
> e.g.
> SELECT
> abdump.gid,
> geometrytype(ST_Centroid(abdump.newgeom)) As geometrytype_centroid,
> ST_AsText(abdump.newgeom) as astxtrep
> FROM
> (SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom As
> newgeom, a.gid, a.the_geom as a_the_geom, b.the_geom as b_the_geom
> FROM bfn.test_mv_dlm07_dump a, bfn.test_mv_dlm07_dump b
> WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND
> Intersects(a.the_geom,b.the_geom)=true
> ) As abdump
> WHERE abdump.gid = 119810
> GROUP BY (ST_Centroid(abdump.newgeom)),
> ST_Area(ST_Centroid(abdump.newgeom)),
> ST_Perimeter(ST_Centroid(abdump.newgeom)), abdump.gid, abdump.newgeom;
>
>
> By the way the result you sent doesn't match the query - its missing a
> field and the labels don't match the query.
>
Yes, that's true - I first made the query with the geom
(abdump.newgeom), but then deleted this from the query because the geom
would have needed such a wide column in the result table. And I forgot
to update this in my e-mail...
>
> 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: Wednesday, October 15, 2008 8:45 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] GeometryCollection after
> ST_Centroid(ST_Dump)
>
> Hi Regina,
>
> yes, that's true. The queries you took for testing work for me, too.
> But if I try to run:
>
> SELECT
> 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
> FROM bfn.test_mv_dlm07_dump a, bfn.test_mv_dlm07_dump 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.the_geom, b.the_geom
> ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc;
>
> I still get: error: Argument to X() must be a point.
>
> The same happens when I formulate the query like you suggested in the
> beginning:
>
> SELECT
> abdump.gid,
> 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,
> abdump.newgeom
> FROM
> (SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom As
> newgeom, a.gid, 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.newgeom
> --a_the_geom, b_the_geom
> ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc,
> abdump.gid asc;
>
> Meanwhile, I am a bit confused, because I see no logic in what
> happens...
>
> Just to explain what I thougt, the problem might be:
>
> When I run the following query:
> SELECT
> abdump.gid,
> geometrytype(ST_Centroid(abdump.newgeom)) As geometrytype_centroid,
> abdump.newgeom
> FROM
> (SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom As
> newgeom, a.gid, a.the_geom as a_the_geom, b.the_geom as b_the_geom
> FROM bfn.test_mv_dlm07_dump a, bfn.test_mv_dlm07_dump 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_Centroid(abdump.newgeom)),
> ST_Area(ST_Centroid(abdump.newgeom)),
> ST_Perimeter(ST_Centroid(abdump.newgeom)), abdump.gid, abdump.newgeom;
>
> I get this in return:
>
> gid geometrytype
> integer text
> ----------------------------------------
> 119810 "GEOMETRYCOLLECTION"
> 120217 "GEOMETRYCOLLECTION"
> 135837 "GEOMETRYCOLLECTION"
> 135837 "GEOMETRYCOLLECTION"
> 101408 "GEOMETRYCOLLECTION"
> 254497 "GEOMETRYCOLLECTION"
> 125720 "GEOMETRYCOLLECTION"
> 125731 "GEOMETRYCOLLECTION"
> 405587 "POINT"
> 412179 "POINT"
> 412179 "POINT"
> 405587 "POINT"
> 125720 "GEOMETRYCOLLECTION"
> 254497 "POINT"
> 242621 "POINT"
> 242621 "POINT"
> 254497 "POINT"
> 242621 "POINT"
> 254497 "POINT"
> 242621 "POINT"
> 254497 "POINT"
> 242621 "POINT"
> 254497 "POINT"
> 135850 "GEOMETRYCOLLECTION"
> 244155 "POINT"
> 254671 "POINT"
> 244155 "POINT"
> 254671 "POINT"
> 244155 "POINT"
> 254671 "POINT"
> 254671 "POINT"
> 244155 "POINT"
> 125742 "POINT"
> 135829 "POINT"
> 125742 "POINT"
> 135850 "POINT"
> 125731 "POINT"
> 135829 "POINT"
> 125742 "POINT"
> 125742 "GEOMETRYCOLLECTION"
> 135850 "POINT"
> 125742 "POINT"
> 134472 "GEOMETRYCOLLECTION"
> 133678 "GEOMETRYCOLLECTION"
> 126954 "GEOMETRYCOLLECTION"
> 135850 "POINT"
> 125731 "POINT"
> 135829 "POINT"
> 125742 "POINT"
> 135850 "POINT"
> 125731 "POINT"
> 135829 "POINT"
> 125742 "POINT"
> 135850 "POINT"
> 135829 "GEOMETRYCOLLECTION"
> 125731 "POINT"
> 135829 "POINT"
> 125742 "POINT"
> 135850 "POINT"
> 412179 "GEOMETRYCOLLECTION"
> 125731 "POINT"
> 135829 "POINT"
> 135850 "POINT"
> 125742 "POINT"
> 125731 "POINT"
> 135829 "POINT"
> 125742 "POINT"
> 135850 "POINT"
> 125731 "POINT"
> 135829 "POINT"
> 135829 "POINT"
> 125731 "POINT"
> 125742 "POINT"
> 135850 "POINT"
> 125720 "POINT"
> 135850 "POINT"
> 125731 "POINT"
> 135829 "POINT"
> 135850 "POINT"
> 125742 "POINT"
> 125742 "POINT"
> 135850 "POINT"
> 125731 "POINT"
> 135829 "POINT"
> 125731 "POINT"
> 135850 "POINT"
> 244155 "GEOMETRYCOLLECTION"
> 242621 "GEOMETRYCOLLECTION"
> 125731 "POINT"
> 135829 "POINT"
> 125742 "POINT"
> 135850 "POINT"
> 135837 "POINT"
> 125720 "POINT"
> 125720 "POINT"
> 135837 "POINT"
> 245042 "GEOMETRYCOLLECTION"
> 253750 "GEOMETRYCOLLECTION"
> 125720 "POINT"
> 135837 "POINT"
> 125720 "POINT"
> 135850 "POINT"
> 375311 "GEOMETRYCOLLECTION"
> 405587 "GEOMETRYCOLLECTION"
> 376289 "GEOMETRYCOLLECTION"
> 254671 "GEOMETRYCOLLECTION"
> 135837 "POINT"
> 125720 "POINT"
> 125720 "POINT"
> 404117 "GEOMETRYCOLLECTION"
> 135837 "POINT"
> 386583 "GEOMETRYCOLLECTION"
> 386466 "GEOMETRYCOLLECTION"
> 125720 "POINT"
> 135837 "POINT"
> 125720 "POINT"
> 135837 "POINT"
> 125720 "POINT"
> 135837 "POINT"
> 125720 "POINT"
> 135837 "POINT"
> 426566 "GEOMETRYCOLLECTION"
>
> This is why I think, the ST_Centroid produces a geometrycollection.
>
> Thanks again!
>
> Regards,
>
> Birgit.
>
> Obe, Regina schrieb:
>
>> 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?
>>
>>
> "POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007"
> USE_STATS"
>
>> 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
>>>
>>>
>>>
>>>
>>
>>
> _______________________________________________
> 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
>
>
More information about the postgis-users
mailing list