2

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

enter image description here

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?

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)
;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.