6

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?

9

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.

1

| improve this answer | |
  • 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
  • 1
    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
  • 1
    @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

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.