10

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:

enter image description here

  • 2
    If any of the answers fills your need, please mark it as "accepted": it will help other users. – RafDouglas Nov 25 at 21:20
15

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

enter image description here

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)

enter image description here

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

9

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.

  • Just changed to using HAVING; wasn't sure SQLite supports it, but it does. – ThingumaBob Nov 22 at 12:29
  • If you go the virtual layer way (SpatialLite), you can keep any field from the polygon layer and simply do group by a.id, as it is the PK and the "1st" (and only!) matching row will be returned. – JGH Nov 22 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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