Select points based on their date & their location (they fall within a radius of another point)

Question

I am studying lions, and their kill sites.

I currently have around 900 historic kill site locations with the record date (with some attribute info). The data is old and I am looking to confirm the reliability of it (looking for additional proof) by looking at the collar (movement) data (multiple GPS locations) of the lions over the time around when the kills were recorded.

So, within a 300m radius of each Kill location I am looking to see what movement happened within the 300m area 3 months before and 2 weeks after the kill (date) was recorded.

I have tried all I know and can - buffer around each kill, have clipped the points that fall within the 300m area easily but cannot separate and sort them to confirm if there was a kill or not.

Answer 1

You can use DB Manager - Virtual Layer and SQL with the query:

select  row_number() over() as id,
        kill.killdate, 
        track.tracktime, 
        case 
            when track.geometry is null then 'No track within 300 m'
            when julianday(killdate)-julianday(tracktime) < -14 then '2 weeks after'
            when julianday(killdate)-julianday(tracktime) > 90 then '3 months before'
            else 'Unreliable kill'
        end validation,
        kill.geometry
from lionkillsite as kill
left join liongpstrack as track
on st_distance(kill.geometry, track.geometry) < 300

My kill table/layer is named lionkillsite, gps track table liongpstrack, my date field in kill table killdate, track date column tracktime. You need to modify these in the query to match your data.

One point/row will be created for each combination of kill-track that are within 300 m. So you will get multiple overlapping kill points at some locations. It is possible to modify the query to only select one unique kill point, if you can decide how it should be selected (like pick most recent / closest track etc.).