4

I have a points layer with a column that separates the points into different types (e.g. 'high', 'medium', and '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 the 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?

2

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 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.

7
  • 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 '19 at 10:23
  • sorry ... my mistake ... it was "geometry" instead of "obj" ... – Snaileater May 31 '19 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 '19 at 13:09
  • What role play the CombinedData table ? How is it connected to your first question ? – Snaileater May 31 '19 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 '19 at 14:14
1

When I looked into this, I was surprised to see that there was no single tool that accomplished this task. However, using 3.18, this can be solved with two tools:

Assume that the point layer has a field "type", as described in the original post. Additionally, assume that the polygon layer has a field named "poly_code", where each polygon has a unique value (for example A, B, C...).

Use the Intersection tool, where the Input Layer = your point layer and the Overlay layer = your polygon layer.

The resulting output will be a new point layer containing the two input fields, "type" and "poly_code". In other words, each point now contains the attributes from the matching polygons they fall within.

The final task is to count the number of points, by "type", within each polygon. This is accomplished using the Processing Toolbox > Statistics by Categories tool, where the Input Vector Layer = the output point layer from the Intersection tool, and within the Field(s) with Categories option, both "type" and "poly_code" are checked on.

Voila! The output table now contains a count for each "type" by "poly_code" combination.

1

You can easily do this by using expressions - the function overlay_within is available since QGIS 3.16 (for older versions see the refFunctions plugin). You can achieve it in one step, however for clarity I explain it in two steps. If you want to do it in one step, simply replace "polygon_id" in the expression of step 2 with the whole expression from step 1.

Select the layer with your points and use the field calculator:

  1. Create a new field "polygon_id" that contains the id (or any other unique value) for the polygon the points lays within. Replace 'polygon' with the name of your polygon layer:

array_first (overlay_within ( 'polygon', $id ) )

  1. Now count the number of features, grouped by the combination of your types (here: fieldname is "value", change this accordingly) and the field "polygon_id" (the one from above):

count ( $id, group_by:= "value" || "polygon_id" )

You get the number of features for each type and each polygon separately.

Screenshot with random points, see example highlighted with red rectangle: polygon 4 (see column polygon_id) contains 6 features/points (column sum) with the type/value 2 (column value) enter image description here

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.