1

Being an SQL-noob, I found a working solution to generate a virtual layer with counts of points in the polygons of another layer, inspired by this https://gis.stackexchange.com/a/54680

SELECT polygons.fid,polygons.geometry, count(points.geometry) AS counts
FROM polygons
LEFT JOIN points
ON st_contains(polygons.geometry,points.geometry )
GROUP BY polygons.fid;

This returns a new polygon layer with the fid and the count of points.

But the end result I would like to achieve is, to only count a subset of points, represented by 

points.controlled IS NULL

I can't find out where and how to inject this criteria into the query. All trial-and-error only led to the latter.

There is already a similar question, but the answer misses the subset part completely
Counting points in a polygon for a subset of my points layer?

Bernd V.
  • 3,179
  • 25
  • 49
  • nope, this gives just a count of 0. I would like to subset/filter/whatever the points layer to those points which are not NULL in the points.controlled column before being counted within the polygons. SQL-logic is so unlogical to me :( – Bernd V. Nov 06 '19 at 16:37
  • @Cyril oh sorry, YES, it worked. The problem here was, that I queried an already joined layer, and there was a problem with he field names. Please post your answer and I will acknowledge it. – Bernd V. Nov 06 '19 at 16:51
  • never mind, you answered correctly, Heikki was faster to post this as an answer. Thanx for your time and input! – Bernd V. Nov 06 '19 at 16:56
  • 1
    Sorry @Cyril didn't see the comments. – HeikkiVesanto Nov 06 '19 at 17:02

1 Answers1

1

You can add the filter in the join condition:

SELECT polygons.fid,polygons.geometry, count(points.geometry) AS counts
FROM polygons
LEFT JOIN points
ON st_contains(polygons.geometry,points.geometry) and points.controlled IS NULL
GROUP BY polygons.fid;
HeikkiVesanto
  • 16,433
  • 2
  • 46
  • 68