[postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

Birgit Laggner birgit.laggner at vti.bund.de
Tue Oct 14 03:56:25 PDT 2008


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.




More information about the postgis-users mailing list