Ah I see the problem now.
SELECT ST_AsText(ST_Centroid(ST_GeomFromTExt('POLYGON((3713601.737
5966193.371,3713601.737 5966193.371,3713601.737
5966193.371,3713601.737
5966193.371))')))
Returns - "GEOMETRYCOLLECTION EMPTY"
The problem is that polygon is invalid.
If I do this
SELECT ST_IsValid(ST_GeomFromTExt('POLYGON((3713601.737
5966193.371,3713601.737 5966193.371,3713601.737
5966193.371,3713601.737 5966193.371))'))
I get
f and a notice that says
NOTICE: Too few points in geometry component at or near point
3.7136e+06 5.96619e+06
So I guess the moral of the story - change your query to include a
WHERE ST_IsValid(abdump.newgeom)
Hope that helps,
Regina
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Birgit Laggner
Sent: Wednesday, October 15, 2008 9:56 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GeometryCollection after
ST_Centroid(ST_Dump)
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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: [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
_______________________________________________
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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
