# Dissolve overlapping polygons according to percent overlap using PostGIS?

I have a shapefile with convex hull polygons, all in the same layer/shapefile. Some of the polygons overlap (not duplicate polygons). I would like to dissolve/union the polygons which overlap with more than a specified percentage (65%).

Any idea how to go about solving the issue using PostGIS?

## 1 Answer

This can be easily accomplished using PostGIS. Preview the results using a modified version of the query below:

``````SELECT a.id, b.id, ST_Area(a.shape), ST_Area(b.shape)
, 100*(ST_Area(ST_Intersection(a.shape, b.shape))/LEAST(ST_Area(a.shape), ST_Area(b.shape)))::numeric(5,2)
as pct_overlap
, ST_Union(a.shape, b.shape) as new_shape
FROM public.convex_hull a
JOIN public.convex_hull b ON a.shape && b.shape AND ST_Intersects(a.shape, b.shape)
WHERE a.id <> b.id AND
65 <= 100*(ST_Area(ST_Intersection(a.shape, b.shape))
/LEAST(ST_Area(a.shape), ST_Area(b.shape)))::numeric(5,2)
;
``````

Swap out `public.convex_hull` with your polygon table. Using a self-join, each polygon is compared with every other polygon that intersects, except for itself - hence the `a.id <> b.id` in the where clause. The percentage of overlap is shown as the `pct_overlap` column and is also used in the where clause to select just those polygons that have at least 65% overlap.

I've defined "65% overlap" as being true if the overlapping area is 65% or greater than the area of the smaller of the two polygons. If your definition is different, please let me know.

It's also advised that your geometry column is using an equal-area projection - not 4326.

If the results of this query look acceptable, you can wrap it in the following to make a new table:

``````CREATE TABLE overlapping_polys AS
SELECT DISTINCT least(q.a_id, q.b_id) as oid, q.new_shape as shape
FROM (
SELECT a.id as a_id, b.id as b_id, ST_Area(a.shape), ST_Area(b.shape)
, 100*(ST_Area(ST_Intersection(a.shape, b.shape))/LEAST(ST_Area(a.shape), ST_Area(b.shape)))::numeric(5,2)
as pct_overlap
, ST_Union(a.shape, b.shape) as new_shape
FROM public.convex_hull a
JOIN public.convex_hull b ON a.shape && b.shape AND ST_Intersects(a.shape, b.shape)
WHERE a.id <> b.id AND
65 <= 100*(ST_Area(ST_Intersection(a.shape, b.shape))
/LEAST(ST_Area(a.shape), ST_Area(b.shape)))::numeric(5,2)
) q;
``````

I tested this using PostGIS and QGIS. The picture below shows 5 polygons, 4 of which overlap. The left pair overlap approximately 35% and the right pair overlap approximately 75%. The red outline is the result of the CREATE TABLE function above. • John, your solution works great. I am impressed by the efficiency of the query. One thing though, would it be possible to also return the undissolved polygons together with the dissolved polygons in the new table? – PyMapr Feb 18 '16 at 8:04
• The most efficient way I can think of off the top of my head would be to create the table using the SQL I posted, then insert the features from the input (convex_hull in my example) into the table where they do not intersect a feature that already exists in the output. – John Reiser Feb 18 '16 at 14:20
• What happen if you have a third polygon overlapping? I'm pretty sure this kind of solution works fine when there are never more than two polygons overlapping but otherwise it will produce new overlaps as polygons are always only compared in pairs. – Pierre Racine Feb 18 '16 at 14:24
• @Pierre - A recursive CTE could be employed, looping over all intersecting pairs until none remain. I have an example of using Recursive CTEs with PostGIS here: gis.stackexchange.com/questions/176517/… – John Reiser Feb 18 '16 at 14:38
• @Pierre, if there is a polygon within a polygon within a polygon, this method only dissolve the two inner most polygons, leaving a polygon within a polygon. But running the query again resolves the issue. I use the query in combination with psycopg2 (python) so it is easy to run the whole process multiple times without to much of a time cost. – PyMapr Feb 19 '16 at 10:37