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:

  1. Fetch the sources in a WITH statement
  2. UNION the three sources to make a single table
  3. 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
SELECT * FROM mh_cic_es
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

Your Answer

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

Browse other questions tagged or ask your own question.