PostGIS Union of two polygons layers

Question

With PostGIS, I need to perform the union of two polygon layers that have intersection areas and distinct areas while preserving all limits. I need to extract separated polygons for each intersected and distinct areas from both layers.

Using QGIS, the tool is called "Vector > Geoprocessing > Union"

here is a visual of the problem:

Two Input polygon layers (green vs red dashes) =

Output polygon layer (one distinct color per polygon)=

I need to do it with PostGIS. st_union seems to do a different job. Any idea?

Answer 1

You can try something like this.

select distinct a.geom1 from
(select distinct(st_dump(st_collect(t1.geom,t2.geom))).geom as geom1
 from t1 inner join t2 on not st_intersects(t1.geom,t2.geom)) a inner join 
(select distinct (st_dump(st_collect(st_symdifference(t1.geom,t2.geom),st_intersection(t1.geom,t2.geom)))).geom
from t1 ,t2 where st_intersects(t1.geom,t2.geom)) b
on not st_intersects(a.geom1,b.geom)
union
select (st_dump(st_collect(st_symdifference(t1.geom,t2.geom),st_intersection(t1.geom,t2.geom)))).geom
from t1 inner join t2 on st_intersects(t1.geom,t2.geom)

Note: t1 and t2 are names of tables containing polygon geometry and name of geometry column in my case is geom in both tables, it may be different in your case.