
--Import all shp in postgis db
-- union ALL des geom et attributs des 28 data sources dans une seule table
drop table if exists layer_union;
create table layer_union as
select inrae_2014.data_id, inrae_2014.geom from inrae_2014 UNION ALL
select aesn_2006.data_id, aesn_2006.geom from aesn_2006 UNION ALL
select aesn_2019.data_id, aesn_2019.geom from aesn_2019 UNION ALL
--(...)etc.

-- res table

drop table if exists res;
create table res as
with tmp as 
(select st_union(ST_Force2D(st_boundary(geom))) as geom from layer_union
)
select (st_dump(st_collectionextract(st_polygonize(geom), 3))).path[1] as id,
       (st_dump(st_collectionextract(st_polygonize(geom), 3))).geom::geometry(polygon, 2154) as geom
from tmp;

-- res table id unique
alter table res add column res_id int generated always as identity primary key;
-- res table index on pointOnSurfacee
create index on res using gist (st_pointOnSurface(geom));
analyze res;

-- res_assoc table
--JOIN simple for filter polygons without link with input polygons (for instance : holes for data input)
drop table if exists res_assoc;
create table res_assoc as
select res.res_id, array_agg(l.data_id) as data_id_ori, count(distinct l.data_id) as num_sources
from res join layer_union l on st_contains(l.geom, st_pointonsurface(res.geom))
group by res.res_id;

-- res_assoc table : index creation 
create index on res_assoc(res_id);
analyse res_assoc;

----cleaning: we remove from the res table the polygons that did not match in res_assoc:
-- these are polygons representing holes in the input layers
delete from res
where not exists (
select null
from res_assoc ra
where ra.res_id = res.res_id);


-- -- Final table with the new polygons and the source polygon information, as a join:
-- Much faster to create a table than to update the res table (after adding the desired columns).
drop table if exists res_final;
create table res_final as
select ra.res_id, data_id_ori, num_sources, geom::geometry(polygon, 2154) as geom
from res_assoc ra join res r on ra.res_id = r.res_id;



