How to flatten spatially equal polygons in PostGIS?

Question

I have the following dataset of polygons. All the pieces are broken based on their borders. I added some transparency to better understand what's going on. The pieces that have stronger colors, means that there're more "spatially equal" polygons in that area (Note by spatially equal I mean ST_Within(A,B) == true and ST_Within(B,A) == true). Stronger the color is, the greater the number of polygons in this area.

(Here is my sample data)

I'm using the following code to flatten all the polygons (in other words, I want them all in the same transparency). Although, it works for some of the polygons, it does not flat all my polygons. Am I doing something wrong here?

SELECT
    ST_Union(geom)  geom,
    sum(val)        ct_values
FROM tbl_test
GROUP BY ST_Centroid(geom)

The expected output is to have something like the following image. There's no polygon over another.They just touch each other (the reason why there're still differences in the border line color).

Answer 1

In theory GROUP BY ST_Centroid(geom) should work. But the number of decimal places for the centroid you're grouping by is not quite the same. You can see it by using ST_AsText(geom). I tried using ST_SnapToGrid(geom, 0.0001) for your sample data, but it also doesn't get all the POINT's in the same precision (to be honest, for me, it seems like a bug, because in theory it should work, as you can see in the documentation).

Well, I tried to overcome this by using GROUP BY ST_Area(geom). Although the areas are slightly different as well, what I ended up doing was:

SELECT
    ST_Union(geom)  geom,
    sum(val)        ct_values
FROM tbl_test
GROUP BY (ST_Area(ST_SnapToGrid(geom, 0.00001))*10000)::int

/*It results in 20 polygons (without overlaps) out of 40 (from the sample dataset you posted)*/

---

Explanation:

Consider the two following "spatially equal" polygons :

polygon_a(area) = 0.00235434 
polygon_b(area) = 0.00235431 --area different from polygon_a

applying this:

(ST_Area(ST_SnapToGrid(geom, 0.00001))*10000)::int

you get:

polygon_a(area) = 23  
polygon_b(area) = 23 --area equals to polygon_a