7

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?

5
  • 1
    So, in other words, you want to count points that meet a condition within a polygon?
    – Mayo
    Jun 16 at 14:46
  • 1
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Jun 16 at 14:55
  • @Mayo exactly, that is what I want to do! Jun 16 at 15:31
  • A screenshot would help
    – BERA
    Jun 19 at 7:46
  • Please, do not forget about "What should I do when someone answers my question?"
    – Taras
    Jun 20 at 10:02

3 Answers 3

8

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
7

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')
    )
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:

enter image description here

2
  • @MrXsquared...I really like this solution, but it is possible to set multiple conditions? Jun 18 at 13:02
  • 1
    @LucasLeonhard, yes, since v1.1 of the plugin you can use 2 conditions, so you can also filter for dates between two dates.
    – MrXsquared
    Jul 11 at 10:19

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.