[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