I miss my mountain gorilla friends in Rwanda. Let’s write a little more code to support them. I’ll be visiting Karisoke again next week, so it seems timely to post a little more code (HT Jean Pierre Samedi Mucyo for working with me on this one).
The problem today is simple — given a time series of gorilla locations and dates, can we calculate rate of travel using PostgreSQL? Well, of course we can. We can do anything in Postgre.
We have two tricks here:
- The first is to order our data so we can just compare one row to the next.
- Once we do that, we need simply to use PostGIS to calculate distance, and ordinary time functions from Postgres to calculate time difference.
This is my first use of WITH RECURSIVE, and it’s probably unnecessary (could be replaced with windowing functions), but I was very proud to finally get over my fear of WITH RECURSIVE . (We actually use one windowing function in our prep of the data. But there we are… ).
-- Let's do some data prep.
DROP TABLE IF EXISTS allgorillagrp16;
CREATE TABLE allgorillagrp16
(
Y numeric NOT NULL,
X numeric NOT NULL,
dater text,
timer text,
location_other text,
labeler text,
id text
)
WITH (
OIDS=FALSE
);
-- Now that we've created the table, we'll import data into it.
COPY allgorillagrp16 FROM 'c:\\temp\\allgorillagrp16.csv' WITH DELIMITER AS ','
;
-- Add a primary key
ALTER TABLE allgorillagrp16 ADD COLUMN gid BIGSERIAL PRIMARY KEY;
-- Geometry!
SELECT AddGeometryColumn ('public', 'allgorillagrp16', 'geom', '32735', 'POINT', 2);
UPDATE allgorillagrp16 SET geom = ST_SetSRID(ST_MakePoint(x,y), 32735);
-- Add a proper date time field
ALTER TABLE allgorillagrp16 ADD COLUMN datetimer TIMESTAMP WITH TIME ZONE;
UPDATE allgorillagrp16 SET datetimer = to_timestamp(dater || ' ' || timer || ' ' || ' ', 'DD-Mon-YY HH24:MI ');
------------------------
-- We want to sort our data by group id and date/time
-- We use ROW_NUMBER to give unique and sortable ids
DROP TABLE IF EXISTS allgorillagrp16_sorted;
CREATE TABLE allgorillagrp16_sorted AS
(
WITH singlegroup AS (
SELECT * FROM allgorillagrp16
ORDER BY id, datetimer
)
SELECT id, ROW_NUMBER() OVER( PARTITION BY id) AS gid, datetimer, geom FROM singlegroup
ORDER BY gid
);
-- We need a function which will take our input gorilla dates, and return the distance, and time difference
-- of travel
DROP FUNCTION gorilla_time(bigint, text);
CREATE OR REPLACE FUNCTION gorilla_time (bigint, text)
RETURNS TABLE(id text, gid BIGINT, geom GEOMETRY, distance FLOAT, timediff INTERVAL, datetimer TIMESTAMP WITH TIME ZONE) AS $$
WITH RECURSIVE gorillamove(id, gid, geom, datetimer) AS (
SELECT id, gid, geom, datetimer, 0::float AS distance, '00:00'::interval AS timediff
FROM allgorillagrp16_sorted
WHERE gid = $1 AND id = $2
UNION ALL
SELECT n.id, n.gid, n.geom, n.datetimer, ST_Distance(n.geom, w.geom) AS distance, n.datetimer - w.datetimer AS timediff
FROM allgorillagrp16_sorted n, gorillamove w
WHERE n.gid::integer = w.gid::integer + 1 AND n.id = $2
)
SELECT id, gid, geom, distance, timediff, datetimer
FROM gorillamove ORDER BY gid;
$$ LANGUAGE SQL;
SELECT DISTINCT id FROM allgorillagrp16;
-- Now we can run it on all our groups. There's a better way to do this. Today
-- my pride is not so great that I will find it.
SELECT * FROM gorilla_time(1, 'Titus')
UNION ALL
SELECT * FROM gorilla_time(1, 'Isabukuru')
UNION ALL
SELECT * FROM gorilla_time(1, 'Himbara')
UNION ALL
SELECT * FROM gorilla_time(1, 'Giraneza')
UNION ALL
SELECT * FROM gorilla_time(1, 'Musilikale')
UNION ALL
SELECT * FROM gorilla_time(1, 'Mafunzo')
UNION ALL
SELECT * FROM gorilla_time(1, 'Ntambara')
UNION ALL
SELECT * FROM gorilla_time(1, 'Iyambere')
UNION ALL
SELECT * FROM gorilla_time(1, 'Pablo')
UNION ALL
SELECT * FROM gorilla_time(1, 'Kuryama')
For the record, WITH RECURSIVE isn’t recursive, but it is useful here in allowing us to compare the current row with the previous.
Smathermather's Weblog 
One thought on “A little Gorilla Time”