# How to recursively loop through parent polygons intersections to get smallest (child) polygons with no overlaps?

I'm struggling with a problem for a couple of days and realized many people also get stuck when the topic is intersections in PostGIS (v2.5). That's why I decided to ask for a more detailed and generic, common question.

I have the following table:

``````DROP TABLE IF EXISTS tbl_foo;
CREATE TABLE tbl_foo (
id bigint NOT NULL,
geom public.geometry(MultiPolygon, 4326),
att_category character varying(15),
att_value integer
);
INSERT INTO tbl_foo (id, geom, att_category, att_value) VALUES
(1, ST_SetSRID('MULTIPOLYGON (((0 6, 0 12, 8 9, 0 6)))'::geometry,4326) , 'cat1', 2 );
INSERT INTO tbl_foo (id, geom, att_category, att_value) VALUES
(2, ST_SetSRID('MULTIPOLYGON (((5 0, 5 12, 9 12, 9 0, 5 0)))'::geometry,4326), 'cat1', 1 );
INSERT INTO tbl_foo (id, geom, att_category, att_value) VALUES
(3, ST_SetSRID('MULTIPOLYGON (((4 4, 3 8, 4 12, 7 14,10 12, 11 8, 10 4, 4 4)))'::geometry,4326) , 'cat2', 5 );
``````

It looks like this: I want to get all the child polygons based on the intersection of the parent polygons. For the outcome, it'd be expected:

• The child polygons with no overlap between them.
• An column containing the sum of the value of their parent polygons,
• An column containing the count of parent polygons of one category
• An column containing the count of another category
• An column containing the category of the child polygon, based on the following rule: -If ALL the parent polygons are from one class, the child polygon also have this class. Else, the category of the child polygon is a third category.

So it'd looks like it: So, in the end, the output table generated (for this example) will have 7 rows (all the 7, non-overlapping, child polygons), containing columns of `category`, `sum_value`, `ct_overlap_cat1`, `ct_overlap_cat2`

The following code I started, gives me the individual intersections, comparing one parent with another.

``````SELECT
(ST_Dump(
ST_SymDifference(a.geom, b.geom)
)).geom
FROM tbl_foo a, tbl_foo b
WHERE a.ID < b.ID AND ST_INTERSECTS(a.geom, b.geom)
UNION ALL
SELECT
ST_Intersection(a.geom, b.geom) as geom
FROM tbl_foo a, tbl_foo b
WHERE a.ID < b.ID AND ST_INTERSECTS(a.geom, b.geom);
``````

How do I recursively loop through the result of this mentioned code, that, independent of the number of overlap polygons I always get its 'smallest' (child) polygons (Fig. 2)?

Try this:

Install by running the postgis_addons.sql file to get the ST_SplitAgg() function.

Test by running the postgis_addons_test.sql file.

``````WITH  result_table AS (
WITH  parts AS (
SELECT a.att_value val,
CASE WHEN a.att_category = 'cat1' THEN 1 ELSE 0 END cat1,
CASE WHEN a.att_category = 'cat2' THEN 1 ELSE 0 END cat2,
unnest(ST_SplitAgg(a.geom, b.geom, 0.00001)) geom
FROM tbl_foo a,
tbl_foo b
WHERE ST_Equals(a.geom, b.geom) OR
ST_Contains(a.geom, b.geom) OR
ST_Contains(b.geom, a.geom) OR
ST_Overlaps(a.geom, b.geom)
GROUP BY a.id, a.att_category , ST_AsEWKB(a.geom), val
)
SELECT CASE WHEN sum(cat2) = 0 THEN 'cat1'
WHEN sum(cat1) = 0 THEN 'cat2'
ELSE 'cat3'
END category,
sum(val*1.0) sum_value,
sum(cat1) ct_overlap_cat1,
sum(cat2) ct_overlap_cat2,
ST_Union(geom) geom
FROM parts
GROUP BY ST_Area(geom)
)
SELECT category, sum_value, ct_overlap_cat1, ct_overlap_cat2,
(ST_Dump(result_table.geom)).geom as geom
FROM result_table
``````
• I have looked at your addons git repo before. Inspirstionsl stuff. – John Powell Nov 6 '18 at 9:13
• Wow great solution. You did a pretty amazing job for creating this addons, as well. Before I click to award this answer, I just one to make sure about one thing that's bugging me. Running the code you provide, the 'polygon 5' (of the second figure of the question) doesn't seem to recognize the overlap with another polygon ('ct_overlap_cat2=1'; 'ct_overlap_cat2=0'). Therefore, this polygon ends up classified as 'cat1' and the 'sum=2', instead of 'cat3' and the 'sum=7'. I'm facing a bit of difficulty to debug this little issue. Could you help me? – Matt_Geo Nov 6 '18 at 15:40
• The only problem with this solution is that the case statements are hard coded. In principle, it should probably be able to handle an arbitrary number of categories. – John Powell Nov 6 '18 at 16:20
• @Matt_Geo I get 6 polygons in the resulting table. The triangle polygon get splitted in three. One with sum=2, one with sum=7 and one with sum=8 as in your wished figure. – Pierre Racine Nov 6 '18 at 21:14
• What if you replace ST_Centroid(geom) with ST_Area(geom)? – Pierre Racine Nov 7 '18 at 22:15

I suppose if you use polygon geometry type instead of MultiPolygon everything will fall into place:

``````DROP TABLE IF EXISTS tbl_foo;
CREATE TABLE tbl_foo (
id bigint NOT NULL,
geom public.geometry(Polygon, 4326),
att_category character varying(15),
att_value integer
);

INSERT INTO tbl_foo (id, geom, att_category, att_value) VALUES
(1, ST_SetSRID('POLYGON ((0 6, 0 12, 8 9, 0 6))'::geometry,4326) , 'cat1', 2 );
INSERT INTO tbl_foo (id, geom, att_category, att_value) VALUES
(2, ST_SetSRID('POLYGON ((5 0, 5 12, 9 12, 9 0, 5 0))'::geometry,4326), 'cat1', 1 );
INSERT INTO tbl_foo (id, geom, att_category, att_value) VALUES
(3, ST_SetSRID('POLYGON ((4 4, 3 8, 4 12, 7 14,10 12, 11 8, 10 4, 4 4))'::geometry,4326) , 'cat2', 5 );
``````

The result is 9 entries that correspond to the different intersection options in the example given by you.