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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment