Calculating number of points within polygon which are filtered according to a given rule

Question

Description of my problem (somewhat simplified)

I have two datasets:

• One polygon layer "villages" which represents the boundary of villages

• One point layer "buildings" which represent buildings. This layer has the attributes "year of construction" and "type".

I would like to count the number of buildings (points) inside each village (polygone) which are of a given type and age, namely:

• Number of individual houses with year of construction < 2000

• Number of individual houses with year of construction > 2000

• Number of appartment buildings with year of construction < 2000

• Number of appartment buildings with year of construction > 2000

The following figure shows an example, with the blue polygon representing the village and each point a building, with styling applied to distinguish the 4 categories of buildings mentionned above (purple: buildings <2000; green: buildings >2000; triangles: individual houses; squares: appartment buildings):

I know how to count points within a polygon using "Analysis Tools" in the "Processing framework" plugin, but I do not how to count only the points which respect a given rule.

Any suggestions?

Answer 1

Use "Select by Location" under Vector > Research Tools. Use that to select all of the points which are in the polygon.

Then open the attribute table of the points, click "Select features using an expression", use something like the following:

"type" = 'individual' AND "year of construction" < 2000

Then click the small drop down arrow next to Select Features and click "Filter Current Selection", this will use that expression to only select from what you have already selected using the polygon.

Repeat this for each combination of variables.

Answer 2

Use a virtual layer:

Layer -> Create Layer -> New Virtual Layer

and enter a query like this:

SELECT v.VillageName,
v.geometry,
SUM(CASE WHEN h.Hyear>2000 THEN 1 ELSE 0 END) "H_New",
SUM(CASE WHEN h.Hyear<=2000 THEN 1 ELSE 0 END) "H_Old",
SUM(CASE WHEN h.Fyear>2000 THEN 1 ELSE 0 END) "F_New",
SUM(CASE WHEN h.Fyear<=2000 THEN 1 ELSE 0 END) "F_Old"
FROM "Random points" h ,"Village"  v
WHERE ST_Contains(v.geometry, h.geometry)
GROUP by v.VillageName

Output

where labels were formatted as follows:

concat( 
"VillageName" , 
'\n  * H_New: ',"H_New" ,
'\n  * H_Old: ', "H_Old" ,
'\n  * F_New: ', "F_New" ,
'\n  * F_Old: ', "F_Old" 
)

Table: