I have two distinct tables, one called rpg(id, code_culture, geom --polygons) and the other called ia(id, classe, geom --also polygons) Like this :
How should I proceed to get a single table with a single geometry while retaining the two attributes and ids like result(id_rp, id_ia, classe, code_culture, geom)?
Edit:
So far, I managed to do this :
With that query, adapted from some material here on stackex but it missed all the geom from ia that's not intersected by rpg but when they do, it's all good with both geom creating new polygons when intersected :
WITH cte1 AS(
SELECT
ia.id AS ia_id, rpg.id AS rpg_id
, ST_Multi(ST_Intersection(ia.geom, rpg.geom)) AS geom
FROM ia, rpg
WHERE ST_Intersects(ia.geom, rpg.geom) AND NOT ST_Within(ia.geom, rpg.geom)
UNION ALL
SELECT
ia.id AS ia_id, rpg.id AS rpg_id
, ia.geom AS geom
FROM ia, rpg
WHERE ST_Within(ia.geom, rpg.geom)
)
SELECT ia_id, rpg_id, (ST_Dump(geom)).geom::geometry(Polygon, 2154) AS geom
FROM cte1;
Edit 2 with solution :
WITH poly_a AS (
SELECT id, NULL AS code_cultu, classe, geom FROM ref_autres.cosia_small_singlepoly
)
,poly_b AS (
SELECT id, code_cultu, NULL AS classe, geom FROM ref_autres.rpg_small_singlepoly
)
,lines AS (
SELECT ST_Boundary(geom) AS geom FROM poly_a
UNION ALL
SELECT ST_Boundary(geom) AS geom FROM poly_b
)
,noded_lines AS ( SELECT ST_Union(geom) AS geom FROM lines )
,resultants AS (
SELECT geom, ST_PointOnSurface(geom) AS pip
FROM St_Dump(
(SELECT ST_Polygonize(geom) AS geom FROM noded_lines ))
)
SELECT a.id AS ida, b.id AS idb, a.classe, b.code_cultu, r.geom
FROM resultants r
LEFT JOIN poly_a a ON ST_Contains(a.geom, r.pip)
LEFT JOIN poly_b b ON ST_Contains(b.geom, r.pip)
WHERE a.id IS NOT NULL OR b.id IS NOT NULL;
