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?

  • 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 6 '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 6 '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 6 '19 at 16:56
  • 1
    Sorry @Cyril didn't see the comments. – HeikkiVesanto Nov 6 '19 at 17:02
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;

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.