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.

1 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);
  1. 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;

enter image description here

  • 1
    Why create spatialite when you can run the sql as virtual layers Jun 7, 2020 at 21:57
  • 1
    This is a matter of taste. Virtual layers will work, but I guess you will have to go via a layer (Load as new layer) between step 1 and 2. Combining the steps into one query and using Virtual layers would be elegant, but I think it would hide what is going on. Jun 7, 2020 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.