I have three different sources in my PostGIS database that I would like to combine in a materialized view to make a single one. I want to merge all the adjacent and/or overlapping polygons to make a single geometry for each "cluster". I could compare the expected results as what the ArcMap 'Dissolve' tool would generate.
My problem is that although the result is a valid layer for QGIS, ST_Union() outputs nothing different from the input.
The script is supposed to:
- Fetch the sources in a WITH statement
- UNION the three sources to make a single table
- ST_Union() the geometries to merge them and use string_agg() to keep records from merged geometries
The SQL script looks like this:
drop materialized view mh.mh_multi_source; CREATE MATERIALIZED VIEW mh.mh_multi_source AS ( WITH mh_cic_es AS ( SELECT id, geom, CAST(type_mh AS VARCHAR(50)), source, CAST('Canards illimités Canada, 2003' AS VARCHAR(75)) AS "source_layer" FROM mh.mh_cic_estrie ), --SELECT * FROM mh_cic_es mh_geomont AS ( SELECT id, geom, CAST(classe AS VARCHAR(50)) AS "type_mh", source_1 || '/' || source_2 as "source", CAST('Géomont, 2008' AS VARCHAR(75)) AS "source_layer" FROM mh.mh_geomont ), --SELECT * FROM mh_geomont mh_sief AS ( SELECT id, geom, CAST('Inconnu' AS VARCHAR(50)) AS "type_mh", met_at_str AS "source", CAST('SIEF, 4eme inventaire' AS VARCHAR(75)) AS "source_layer" FROM mh.mh_sief_view ), mh_union AS ( SELECT * FROM mh_sief UNION SELECT * FROM mh_cic_es UNION SELECT * FROM mh_geomont ) SELECT CAST(ROW_NUMBER() OVER (ORDER BY id ASC) AS int) AS id, ST_Union(geom) as geom, string_agg(source_layer, '/') AS "source_layer" FROM mh_union GROUP BY id )