Merging two multipolygon tables into one and afterwards dissolving boundaries using PostGIS

Question

I am trying to merge two multipolygon tables into one and at the same time - in the same query - dissolve the boundaries between the polygon districts using postgis.

The two tables are the red polygons and the beige polygons in the map below.

As mentioned, the first thing I want to do is to merge the two tables into one and second I want to dissolve the boundaries based on values in a column name. Name are e.g. 'JS22C' and 'JS22A' in the map. I cannot figure out if you do this by doing a join operation or by using a UNION/ST_UNION

I have been trying the following:

CREATE OR REPLACE VIEW schema.tablename AS
SELECT row_number() over() as id, st_union(a.the_geom, b.the_geom), name 
FROM schema.table1 a, schema.table2 b
GROUP BY name;

But it says that name is ambiguous which is quite obvious because the column name exists in both tables .

Can anybody help out?

Answer 1

No need for a JOIN; in fact, joining both tables just makes things more complicated.

Rather, collect all names & geoms within a sub-query, and ST_Union them based on name:

SELECT name, ST_Multi(ST_Union(geom)) AS geom
FROM   (
  SELECT name, geom
  FROM   schema.table1
  UNION ALL
  SELECT name, geom
  FROM   schema.table2
) q
GROUP BY
       name
;

This has the benefit of the result being agnostic to none-matches of a JOIN, and the aggregate ST_Union attempting to dissolve even invalid geometries (e.g. if your MultiPolygons have common vertices, they are invalid; a JOIN ... USING (name) and the two-parameter ST_Union signature would require an intermediate ST_UnaryUnion or ST_MakeValid).

---

For reference, this would be an alternative using a JOIN:

SELECT COALESCE(a.name, b.name),
       ST_Multi(
         COALESCE(
           ST_Union(ST_UnaryUnion(a.geom), ST_UnaryUnion(b.geom)),
           a.geom,
           b.geom
         )
       ) AS geom
FROM   schema.table1 AS a
FULL OUTER JOIN
       schema.table2 AS b
USING  (name)
;