I have a points layer with a column that separates the points into different types (eg 'high', 'medium', 'low'). I need to count the number of points within polygons, for each of the different types. At the moment, I'm doing it by creating a separate points layer for each of the types and then running the points within polygon tool for each of them. I was just wondering if there's a tool that lets you run points within polygon but with conditions, so that I could run it for those points where the type is 'high' etc?


SQL is the answer !

You can achieve this using the DB Manager of QGIS.

You choose Database / Database Manager / Database Manager then Virtual Layers / Qgis Layers you can then try queries like the following :

SELECT count(*), pt_type from pt_layer, poly_layer
where st_intersects(pt.geometry, poly.geometry)
group by pt_type 

pt_layer being your point layer and poly_layer being your polygon layer...

You can then load the result in the layer manager.

  • Thanks for the suggestion... Your instructions were great, but I'm getting an error message, and I'm not that familiar wtih SQL so can't figure it out... I've replaced pt_type, pt_layer and poly_layer with the names from my layers but it's telling me that 'pt.obj' isn't a layer. Do I also need to replace that with something? Or define what that is? I've tried replacing 'pt' with the name of my point layer but that didn't work. Thanks – NS31 May 31 at 10:23
  • sorry ... my mistake ... it was "geometry" instead of "obj" ... – snaileater May 31 at 11:37
  • That moves it past the first error, but I get a different error that I don't understand - pasted it at the end. "CombinedData" is a csv file I've uploaded to use as a layer. I've tried saving it as a shape file but that doesn't seem to make a difference. Does it need to be a layer in the main database for me to run the query or can it be held locally? Thanks. Error message is: Query execution error on DROP TABLE IF EXISTS "CombinedData"; CREATE VIRTUAL TABLE "CombinedData" USING QgsVLayer(CombinedData20190531140455318);: 1 - near "Order": syntax error – NS31 May 31 at 13:09
  • What role play the CombinedData table ? How is it connected to your first question ? – snaileater May 31 at 13:35
  • Sorry, didn't explain that properly. The CombinedData table is my points... I used your code and amended as follows: code SELECT count(*), Type from CombinedData, MSOAs where st_intersects(CombinedData.geometry, MSOAs.geometry) group by Type code – NS31 May 31 at 14:14

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.