Tracing golden monkeys through time
source link: https://smathermather.com/2019/06/28/tracing-golden-monkeys-through-time/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Tracing golden monkeys through time
My collegue TUYISINGIZE Deogratias (“Deo) and others at Dian Fossey Gorilla Fund International have been studying golden monkeys (Cercopithecus kandti) in Rwanda. Golden monkeys are an endangered monkey along the Albertine Rift (including the Virungas, host to the endangered mountain gorilla). They are also cute as can be, but more on that another time.
Deo has been leading efforts to track the golden monkeys in several locations across their range, observing their habits. Among the data gathered is the location of the groups of the monkeys as they move through their range. One element we want to understand from these data are how much does each group move per day.
The raw data look something like this:
So we tweak things a bit to get ids in order of date and time, and also prep the data so that the date and time are proper types in PostgreSQL:
DROP TABLE IF EXISTS goldenmonkeys_sorted; CREATE TABLE goldenmonkeys_sorted AS ( WITH nodate AS ( SELECT gid, geom, id, lat, lon, alt, dater || ' ' || timer AS dater, month AS monther, season, groupid FROM hr_g_all ) , sorted AS ( SELECT gid, geom, id, lat, lon, alt, dater::TIMESTAMP WITH TIME ZONE AS datetimer, monther, season, groupid FROM nodate ORDER BY groupid, datetimer ) SELECT gid AS id, ROW_NUMBER() OVER( PARTITION BY gid) AS gid, datetimer, date(datetimer) AS dater, monther, season, groupid, geom FROM sorted ORDER BY gid );
Resulting in the following:
Golden Monkey Ranging in Gishwati National ParkOk. Now we want to turn this into traces of the movements of the monkeys everyday. Something like this:
But for every trace, for every day for each group.
We will create a function that leverages WITH RECURSIVE. We’ve seen this before. WITH RECURSIVE allows us to take each record in sequence and perform operations with the previous record, in this case calculating travel time, travel distance, and combining the individual points into a single line with ST_MakeLine.
CREATE OR REPLACE FUNCTION goldenmonkey_time (date, text)
RETURNS TABLE(dater date, monther text, traveltime interval, distance float, geom geometry) AS $$
WITH RECURSIVE gtime AS (
SELECT gid, dater, monther, datetimer, datetimer – (SELECT min(datetimer) FROM goldenmonkeys_sorted WHERE dater = $1 AND groupid = $2) AS timediff, 0::float AS distance, geom
FROM goldenmonkeys_sorted WHERE dater = $1 AND groupid = $2
UNION ALL
SELECT n.gid, w.dater, w.monther, w.datetimer, n.datetimer – w.datetimer AS timediff, ST_Distance(n.geom, w.geom) AS distance, n.geom
FROM goldenmonkeys_sorted n, gtime w
WHERE w.dater = $1 AND n.gid::integer = w.gid::integer + 1
)
SELECT max(dater) AS dater, max(monther) AS monther, max(timediff) AS traveltime, ST_Length(ST_MakeLine(geom)) AS length, ST_MakeLine(geom) FROM gtime;
$$ LANGUAGE SQL;
Now to use our function, we need a list of dates and groups so we can calculate this for each day:
WITH dategroup AS (
SELECT
DISTINCT dater, groupid
FROM
goldenmonkeys_sorted ORDER by groupid, dater
)
SELECT groupid, (goldenmonkey_time(dater, groupid)).dater,
(goldenmonkey_time(dater, groupid)).monther,
(goldenmonkey_time(dater, groupid)).traveltime,
(goldenmonkey_time(dater, groupid)).distance,
(goldenmonkey_time(dater, groupid)).geom FROM dategroup
ORDER BY groupid, (goldenmonkey_time(dater, groupid)).dater;
Now we have traces not just for one day and group, but all traces and groups:
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK