gorilla_time.md

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