Selecting polygons based on area of overlap in QGIS?

Question

I am working with point data in QGIS, where the points represent electricity pylons on a transmission grid.

I have created buffers with a 1 km radius around each of the points, and I would like to know if there is any way that I can extract buffers from this layer based on the area of overlap with other 1km buffers.

I only want the buffers which share less than 10% of their interior space with another 1 km buffer in the same layer.

Answer 1

Create a SpatiaLite database, import your point buffer dataset into it, rename it to pbuffer, start DB Manager, open the SpatiaLite DB, and run the following two queries (one at a time):

1. Query #1 creates a table with the overlapping areas of the point buffers.

   CREATE TABLE pbi AS SELECT pb1.id AS id1, pb2.id AS id2,
                              st_intersection(pb1.geom, pb2.geom)
   AS geom
   FROM pbuffer AS pb1, pbuffer AS pb2
   WHERE pb1.id <> pb2.id AND st_overlaps(pb1.geom, pb2.geom);

2. Query #2 will, for each point buffer, give you the area of overlap with other point buffers (area), and it also produces a "flag" (ok) that is True (1) if the area of overlap is less than 10% of their interior space (for point buffers with 1 km radius).

   SELECT pbi.id1, st_union(geom) AS geom,
          st_area(st_union(geom)) AS area,
          st_area(st_union(geom))/(pi()*1000000) < 0.1 as ok
   FROM pbi
   GROUP BY pbi.id1;