Counting points that meet condition within polygons in QGIS

Question

I want to create a dataset containing information about civilian deaths in areas near UNPKOs. Therefore, I use the Geo-PKO dataset, which geocodes UN peacekeeping missions, and the UCDP-ged dataset, which geocodes violent conflict. I created buffer zones for each mission and want to know how many civilians died within that buffer zone. The problem is that I need to remove all civilian deaths that happened before the installation of a mission.

Both datasets have a year variable. So I want to remove any observation from a buffer zone if the year variable of the buffer zone is bigger than the year variable of the violent incident.

Any suggestions?

Answer 1

You can achieve this spatial + temporal join using a virtual layer.

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query. Feel free to add any field of interest from the two layers.

In this example, a is the polygon layer (mission area), pt is the point layer (violent death). Both have a dte_year field containing the date. The query looks for spatial intersection, but you could also add a buffer here. The join condition also rely on the year equivalence between the two dates.

SELECT  a.id as areaID, pt.*
FROM a
  JOIN pt 
    ON ST_INTERSECTS(a.geometry, pt.geometry)  
     AND  a.dte_year = b.dte_year

If you want, you can at the same time aggregate the information:

SELECT  a.id as areaID, 
        count(*) as event_cnt, 
        sum(pt.death_count) as total_death
FROM a
  JOIN pt 
    ON ST_INTERSECTS(a.geometry, pt.geometry)  
     AND  a.dte_year = b.dte_year
GROUP BY a.id

Answer 2

You could also use a Virtual Field in the buffer layer.

Add a Virtual Field using the Field Calculator use one of the following expressions depending on what you need:

---

This counts the number of points intersected by the parent geometry (the buffer) whose date is also later than the mission:

aggregate(
    'point_layer',
    'count',
    '',
    intersects($geometry, geometry(@parent)) AND "date_field" > attribute(@parent,'mission_date_field')
    )

---

This sums the total number of deaths (in the field "num_deaths") for the points which intersect the parent geometry and whose date is also later than the mission:

aggregate(
    'point_layer',
    'sum',
    "num_deaths",
    intersects($geometry, geometry(@parent)) AND "date_field" > attribute(@parent,'mission_date_field')
    )

Answer 3

Disclaimer: I am the developer of this plugin.

You can use "ProcessX Plug-In".

It has an algorithm "Count Features in Features With Condition" or "Count Points in Polygons With Condition". Since version 1.1 of the Plug-In you can use two conditions, allowing to filter for events between two dates. You will find both of them in your processing toolbox in ProcessX -> Vector - Conditional -> Count Features in Features with Condition: