This issue was reported by a colleague. We are not supposed to work with multipart geometries, as most of the time colleagues have issues with them, they take more time to process in my QGIS project due to numerous spatial queries, and now they are causing additional problems...
Rather than trying to make multipart geometries work, I added a BEFORE UPDATE trigger to ensure that only single-part polygons are created. Multipart ones are processed but not inserted.
Here is the trigger:
CREATE TRIGGER split_multipolygon BEFORE INSERT ON HABITATS_POLY FOR EACH ROW WHEN ST_NumGeometries(NEW.geom) > 1 BEGIN INSERT INTO log_triggers (trigger_name, action) VALUES ('split_multipolygon','INSERT'); UPDATE TABLE_VARIABLES SET split_multi = 1 WHERE id = 1; -- Insert every single parts of the multipolygon INSERT INTO HABITATS_POLY ( visibilite, lib_physio, eunis_1, eunis_2, eunis_3, eunis_4, code_eunis, enjeu_hab, etat_cons, typicite, comment, annee, surf_hab, num_etude, corine_1, corine_2, corine_3, corine_4, cde_corine, eunis22_1, eunis22_2, eunis22_3, eunis22_4, cde_eun22, eur28_1, eur28_2, eur28_3, eur28_4, cde_eur28, chn2000_1, chn2000_2, chn2000_3, chn2000_4, cde_chn2k, pvf2_1, pvf2_2, pvf2_3, pvf2_4, cde_pvf2, zh_1, zh_2, zh_3, zh_4, cde_zh, hab_prio, cat_hab, date_hab, couleur, rel_ehp, rel_zh, compart, user, data_prop, liaison_zh, date_updt, lab_eur28, context_zh, bal, geom ) WITH RECURSIVE split_parts(part_number, part_geom) AS ( SELECT 1, ST_Multi(ST_GeometryN(NEW.geom, 1)) AS part_geom UNION ALL SELECT part_number + 1, ST_Multi(ST_GeometryN(NEW.geom, part_number + 1)) FROM split_parts WHERE part_number + 1 <= ST_NumGeometries(NEW.geom) ) SELECT NEW.visibilite, NEW.lib_physio, NEW.eunis_1, NEW.eunis_2, NEW.eunis_3, NEW.eunis_4, NEW.code_eunis, NEW.enjeu_hab, NEW.etat_cons, NEW.typicite, NEW.comment, NEW.annee, NEW.surf_hab, NEW.num_etude, NEW.corine_1, NEW.corine_2, NEW.corine_3, NEW.corine_4, NEW.cde_corine, NEW.eunis22_1, NEW.eunis22_2, NEW.eunis22_3, NEW.eunis22_4, NEW.cde_eun22, NEW.eur28_1, NEW.eur28_2, NEW.eur28_3, NEW.eur28_4, NEW.cde_eur28, NEW.chn2000_1, NEW.chn2000_2, NEW.chn2000_3, NEW.chn2000_4, NEW.cde_chn2k, NEW.pvf2_1, NEW.pvf2_2, NEW.pvf2_3, NEW.pvf2_4, NEW.cde_pvf2, NEW.zh_1, NEW.zh_2, NEW.zh_3, NEW.zh_4, NEW.cde_zh, NEW.hab_prio, NEW.cat_hab, NEW.date_hab, NEW.couleur, NEW.rel_ehp, NEW.rel_zh, NEW.compart, NEW.user, NEW.data_prop, NEW.liaison_zh, NEW.date_updt, NEW.lab_eur28, NEW.context_zh, NEW.bal, part_geom AS geom FROM split_parts; UPDATE TABLE_VARIABLES SET split_multi = 0 WHERE id = 1; SELECT RAISE(IGNORE); --avoid insert into the table END;
It works fine now.
