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:
In QGIS you can conveniently use the
Select Features by Expression command:
With an expression like this:
aggregate('Random points', 'count','', contains(geometry(@parent),$geometry))>=5
aggregateis the function used to calculate the number of points contained in each polygon
Random pointsis the name of the point layer
''is the (empty)
aggregatefilter 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.
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.