PostGIS query to split a polygon by n other polygons

Question

I'm trying to write a PostGIS query to do the following:

1. Get polygons from a table that overlap geometry A above (in this case, B and C).
2. Get the intersecting area.
3. Split the intersecting area by the overlapping polygons (in this case, returning D and E).

In this case there are 2 overlapping polygons, but I need to support an arbitrary number.

I'm stuck on how to get started with this. I know I can use ST_Intersects to get the intersecting polygons and ST_Intersection to get the overlapping area, but I'm not sure the splitting will work. It looks like ST_Split only accepts two geometries Can anyone think of how I could get this to work with n polygons?

Answer 1

If Table a holds your polygon "A" and Table b holds your Polygons "B" and "C", the following should return the two records with polygons "D" and "E":

SELECT ST_Intersection(a.geom,b.geom)
FROM a JOIN b
ON ST_Intersects(a.geom, b.geom);

---

If you want to receive only a single (multi-)polygon per a.fid, you can add st_union and group by a.fid:

SELECT ST_Union(ST_Intersection(a.geom,b.geom))
FROM a JOIN b
ON ST_Intersects(a.geom, b.geom)
GROUP BY a.fid;