Select polygons with 5 or more points

Question

I have two layers (polygon and point) in QGIS added from PostgreSQL database and want to select polygons from index grid with 5 or more points on them. Is this possible to do in QGIS or in PgAdmin?

here is an example photo:

Answer 1

In QGIS you can conveniently use the Edit -> Select -> Select Features by Expression command:

With an expression like this:

aggregate('Random points', 'count','', contains(geometry(@parent),$geometry))>=5

Where:

• aggregate is the function used to calculate the number of points contained in each polygon
• Random points is the name of the point layer
• count is the aggregation type
• '' is the (empty) aggregate "expression"
• contains(geometry(@parent),$geometry)is the aggregate filter specifying you want to count only points contained in each feature of the "parent" layer (in this case, the polygon layer)

Only polygons containing at least (>=) 5 points are selected.

Answer 2

The equivalent SQL (PostgreSQL/PostGIS & SpatiaLite [i.e. QGIS Virtual Layers]) would be:

SELECT  a.<id>
        a.<geom>
FROM    poly AS a
JOIN    pts AS b
  ON    ST_Intersects(a.<geom>, b.<geom>)
GROUP BY
        a.<id>, a.<geom>
HAVING  COUNT(b.<id>) >= 5
;

Make sure you have a spatial index in place. Note that any additional column from poly mentioned in the SELECT list (i.e. a.<column>) needs to appear in the GROUP BY, if there is no unique (primary) key present in the table to base the grouping on.

---

In case your grid is regular and its cells strictly square, you can use (PostgreSQL/PostGIS only):

SELECT  a.<id>
        a.<geom>
FROM    poly AS a
JOIN    pts AS b
  ON    a.<geom> && b.<geom>
GROUP BY
        a.<id>, a.<geom>
HAVING  COUNT(b.<id>) >= 5
;

to avoid the intersection test on the geometry components. This might be useful for very large tables.