Merging Polygons using ST_Multi ST_Union group by column value

Question

I'm trying to merge polygons  together to form a larger 1 and I'm getting some odd results
In the  picture the red is giving the correct outline but there are 11 identical polygons I was expecting only 1 (this is related to the number of smaller polygons to be merged) eg the number of polygons with the same gd.gid_4 value
Can anyone say  what I'm doing wrong?  I'm running this query in PostgreSQL and viewing in qgis
Please note using "*" for clarity here
INSERT INTO new_table (*)
SELECT g.*,gd.geometry
FROM gadm36 g
JOIN(
    SELECT gd.gid_4 AS gid_4,
    ST_Multi(ST_Union(gd.geom) ) As geometry
    FROM gadm36 AS gd
WHERE  gd.gid_0 = 'FRA'
    GROUP BY gd.gid_4

) gd ON g.gid_4 = gd.gid_4;

FSimardGIS · Accepted Answer

Your subquery generates 1 multipolygon for each gid_4. However, joining on the condition g.gid_4 = gd.gid_4 multiplies your rows by the number of rows of each gid_4 value, 11 in this case, resulting in 11 identical rows with a multipolygon.
Since you only want 1 multipolygon per gid_4, I suggest using only the subquery:
SELECT gd.gid_4 AS gid_4,
    ST_Multi(ST_Union(gd.geom) ) As geometry
FROM gadm36 AS gd
WHERE  gd.gid_0 = 'FRA'
GROUP BY gd.gid_4

And add any additional fields that you need in both the SELECT and GROUP BY statements. Make sure the fields you add only contain one unique value per gid_4, otherwise you'll need to exclude those fields or use them in an aggregate function.

Merging Polygons using ST_Multi ST_Union group by column value

One Answer

Add your own answers!

Ask a Question