[Qgis-user] Spatial and Temporal query?

[Robert Le Brun]

Hi, 

I am trying to execute the following:

I am studying lions, and their kill sites.

I currently have around 890 historic kill site locations with the record date on a CSV file (with some other 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, a different CSV file with many (67000) locations and their dates, 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 made a buffer around each kill, have clipped the points that fall within the 300m area but cannot separate and sort them efficiently. 

There are 890 Kill sites and 67000 movement points over a 12 year period that I need to analyse, so doing the entire process manually isn’t feasible.

I have tried the DB manager using the following SQL Query but this doesn’t seem to extract exactly what’s needed:

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. 

Thanks.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20230807/32cb12f6/attachment.htm>