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) = Two Input polygon layers

Output polygon layer (one distinct color per polygon)= Output polygon layer

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

  • 1
    You need to split or to join polygons (dissolve boundaries) with intersections? Seems to be splited: we can first to isolate parts with ST_Intersection() and ST_Difference(), them scan all with usual SQL UNION, them merge all in a layer by ST_Collect() (faster tham ST_Union). Nov 9 '18 at 22:30

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)
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.

  • YES it works. st_symdifference is however pretty slow. Thanks
    – Martin C
    Nov 10 '18 at 9:21
  • 1
    Then please accept this answer
    – Asad Abbas
    Nov 10 '18 at 9:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.