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:
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this communityI 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 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 polygonRandom points
is the name of the point layercount
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.
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.
HAVING
; wasn't sure SQLite supports it, but it does.
– geozelot
Nov 22 '19 at 12:29
group by a.id
, as it is the PK and the "1st" (and only!) matching row will be returned.
– JGH
Nov 22 '19 at 13:16