Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
244 lines (208 sloc) 11.5 KB

FUNCTIONS

This file provides examples for the most important functions available in the schema tools.

CONTENT

daylight

This function returns the sunrise and sunset times (as a text array) for a spatial point expressed as a WKT, with its associated SRID, a timestamp to give the date and a time zone.

Example 1

SELECT tools.daylight('POINT(11.001 46.001)', 4326, '2012-09-01'::timestamp, 'UTC');

Example for animal 770

SELECT acquisition_time, 
(tools.daylight(ST_AsText(geom), 4326, acquisition_time::timestamp, 'UTC'))[1] as sunset, 
(tools.daylight(ST_AsText(geom), 4326, acquisition_time::timestamp, 'UTC'))[2] as sunrise 
FROM main.gps_data_animals 
WHERE animals_id = 770 AND gps_validity_code = 1 
ORDER BY acquisition_time;
-to content-

age_class

This function returns the age class at the acquisition time of a location. It has two input parameters: the id of the animal and the timestamp. According to the age class at first capture, the function increases the class by 1 every time the animal goes through a defined day of the year (1st April).

Example

SELECT tools.age_class(animals_id, acquisition_time),
animals_id, acquisition_time, geom, longitude, latitude, gps_validity_code  
FROM main.gps_data_animals where animals_id in (770, 771);
-to content-

detect_bursts

This function gives the "bursts" for a defined animal. Bursts are groups of consecutive locations with the same frequency (or time interval). It gets an animal id and a buffer (in seconds) as input parameters and returns a table with the (supposed) schedule of location frequencies. The output table has the fields: "animals_id", "starting_time", "ending_time", "num_locations", "num_locations_null", and "interval_step" (in seconds, approximated according to multiples of the buffer value). A relocation is considered to have a different interval step if the time gap is bigger than the defined buffer (the buffer takes into account the fact that small changes can occur because of the delay in receiving the GPS signal). The default value for the buffer is 600 seconds(10 minutes). The function is directly computed on "main.view_locations_set" (locations_set structure) and on the whole data set of the selected animal.

Examples for animal 770

SELECT * FROM tools.detect_bursts(770); -- default (buffer 60*10 (10 min))
SELECT * FROM tools.detect_bursts(770, 60*5); -- buffer 5 min

Bursts for multiple animals

SELECT (tools.detect_bursts(animals_id, 60*5)).* FROM main.animals WHERE animals_id in (771,770); -- 2 animals 
SELECT (tools.detect_bursts(animals_id, 60*5)).* FROM main.animals WHERE study_areas_id in (1); -- all animals of 1 study area
-to content-

traj_bursts

This function generates a data set to be used to create a ltraj object in R. It gets the animal_id and the buffer (see function tools.bursts_report) and return a set of locations which have a tag for the specific bursts. This function at the moment works on an animal at a time.

An example of use in R

# get the data 
data_traj_raw <- sqlQuery(channel, "select * from tools.traj_bursts(771,60*10);")
# create an ltraj object
data_traj<- as.ltraj(xy=data_traj_raw[,c("x","y")], date=as.POSIXct(data_traj_raw[,"acquisition_epoch"], origin="1970-01-01 01:00:00"), id =data_traj_raw[,"animals_id"], burst=data_traj_raw[,"burst"])
-to content-

regularize

(NOTE: the function only works on gps data that is already in the database) This function creates a complete, regular time series of locations from main.view_locations_set using an individual id, a time interval (in seconds), a buffer time (in seconds, which corresponds to the accepted delay of GPS recording), a starting time (if no value is defined, the first record of the animal data set is taken), an ending time (if no value is defined, the last record of the animal data set is taken). The function checks at every time step if exists a "real" record (with or without coordinates) in the main.view_locations_set table (which is the "locations_set" object of the "main.gps_data_animals" table): if any real data exists (inside a defined time interval buffer from the reference timestamp generated by the function) in main.view_locations_set, the real record is used, otherwise a "virtual" record is created (with empty geometry). The output is a table with the structure "location_set" (animals_id integer, acquisition_time timestamp with time zone, geom geometry).

Examples for animal 770

SELECT animals_id, acquisition_time, st_astext(geom) FROM tools.regularize(770, 60*60*2, 60*10); --2h interval
SELECT animals_id, acquisition_time, st_astext(geom) FROM tools.regularize(770, 60*60*4, 60*10); --4h interval
SELECT animals_id, acquisition_time, st_astext(geom) FROM tools.regularize(770, 60*60*7, 60*10); --7h interval
SELECT animals_id, acquisition_time, st_astext(geom) FROM tools.regularize(770, 60*60*8, 60*10); --8h interval
-- To get the geometry as such
SELECT animals_id, acquisition_time, geom FROM tools.regularize(770, 60*60*4, 60*10);

Examples for multiple animals (770,771) where start_time and end_time are costumized

-- METHOD 1
SELECT 
(tools.regularize(animals_id, 
(60*60*4), (60*10), -- interval, buffer
date_trunc('hour',min(acquisition_time))::timestamp without time zone, -- start_time (first fix)
date_trunc('hour',min(acquisition_time) + interval '365 days 2 hours')::timestamp without time zone)).* -- end_time (1 year later)
FROM main.gps_data_animals 
WHERE animals_id in (770,771) 
GROUP BY animals_id ORDER BY animals_id, acquisition_time;
-- METHOD 2
WITH set_interval AS 
(
	SELECT animals_id, 
	date_trunc('hour',min(acquisition_time)) start_time, -- start_time (first fix)
	date_trunc('hour',min(acquisition_time) + interval '365 days 2 hours') end_time -- end_time (1 year later)
	FROM main.gps_data_animals JOIN main.animals USING (animals_id) 
	WHERE study_areas_id = 1 AND animals_id in (771,770) GROUP BY animals_id
) 
SELECT (tools.regularize(animals_id, (60*60*4), (60*10),start_time,end_time)).* from set_interval ORDER BY animals_id, acquisition_time;

Example for multiple animals (770,771) where start_time and end_time are costumized and linked back to the main table

WITH regularized AS 
(
	SELECT (tools.regularize(animals_id, 
	(60*60*4), (60*10), -- interval, buffer
	date_trunc('hour',min(acquisition_time))::timestamp without time zone, -- start_time (first fix)
	date_trunc('hour',min(acquisition_time) + interval '365 days 2 hours')::timestamp without time zone)).* -- end_time (1 year later)
	FROM main.gps_data_animals 
	WHERE animals_id in (770,771) and gps_validity_code = 1
	GROUP BY animals_id 
	ORDER BY animals_id, acquisition_time
)
SELECT regularized.*, gps_validity_code FROM regularized LEFT OUTER JOIN main.gps_data_animals USING (animals_id, acquisition_time);
-to content-

interpolate

This function accepts as input an animals_id and a locations_set (by default, the main.view_locations_set). It checks for all locations with NULL geometry. If these locations have a previous and next valid location (according to the gps_validity_code) with a gap smaller than the defined threshold (default is 12 hours), a new geometry is calculated interpolating their geometry.

examples for animal 770

SELECT (tools.interpolate(770)).*; --12h threshold
SELECT (tools.interpolate(770,'main.view_locations_set',60*60*4)).*; -- 4h threshold
SELECT (tools.interpolate(770,'main.view_locations_set',60*60*8)).*; -- 8h threshold

Examples for multiple animals (770,771)

SELECT (tools.interpolate(animals_id,'main.view_locations_set',60*60*8)).* FROM main.animals WHERE animals_id in (770,771); -- for 2 animals 
SELECT (tools.interpolate(animals_id,'main.view_locations_set',60*60*8)).* FROM main.gps_data_animals WHERE animals_id in (770,771) GROUP BY animals_id; -- for 2 animals
SELECT (tools.interpolate(animals_id,'main.view_locations_set',60*60*8)).* FROM main.animals WHERE study_areas_id in (1); -- for all animals of 1 study area
-to content-

geom_parameters

This function creates a table with the geometrical parameters of the data set (reference: previous location): time gap with the previous point, time gap with the previous-previous point, distance to the previous point, speed of the last step, distance from the first point of the data set, absolute angle (previous location), relative anlge (previous and previous-previous location). the parameters are the animals_id, the time gap and the buffer. The time gap select just location that have the previous point at a defined time interval (with a buffer tollerance). All the other points are not included in the output. A "locations_set" class is accepted as input table. it is also possible to specify the starting and ending point of the time series. The output is a table with the structure "geom_parameters".

Example for animal 770

SELECT (tools.geom_parameters(770)).*; -- default settings (interval = 60*60*4 (4h), buffer = 60*5 (10 min)) 
SELECT (tools.geom_parameters(770, 60*60*2, 60*10)).*; -- 2h interval and 10 min buffer   
SELECT (tools.geom_parameters(770, 60*60*8, 60*10)).*; -- 8h interval and 10 min buffer  
SELECT (tools.geom_parameters(770, 60*60*12, 60*10)).*; -- 12h interval and 10 min buffer  

Example for multiple animals (770,771) where start_time and end_time are customized

SELECT 
(tools.geom_parameters(animals_id, 
(60*60*4), (60*10), 'main.view_locations_set', -- interval, buffer, location_set
date_trunc('hour',min(acquisition_time))::timestamp without time zone, -- start_time
date_trunc('hour',min(acquisition_time) + interval '365 days 2 hours')::timestamp without time zone)).* -- end_time 1 year later
FROM main.gps_data_animals 
WHERE animals_id in (770,771) 
GROUP BY animals_id;

Example for multiple animals (770,771) where start_time and end_time are customized and linked back to the main table

WITH geom_parameters AS 
(
	SELECT 
	(tools.geom_parameters(animals_id, 
	(60*60*4), (60*10), 'main.view_locations_set', -- interval, buffer, location_set
	date_trunc('hour',min(acquisition_time))::timestamp without time zone, -- start_time
	date_trunc('hour',min(acquisition_time) + interval '365 days 2 hours')::timestamp without time zone)).* -- end_time 1 year later
	FROM main.gps_data_animals 
	WHERE animals_id in (770,771) 
	GROUP BY animals_id
)
SELECT geom_parameters.*, geom, longitude, latitude  FROM geom_parameters LEFT OUTER JOIN main.gps_data_animals USING (animals_id, acquisition_time); 
-to content-

outlier_detection

Function that detects outliers based on the distance to the previous and next location, and the angle made by the three points and the speed (meters per hour). The animals_id, minimum distance and maximum angle can be provided as input.

Example to detect suspicious locations for the animals of study area 1

WITH a AS 
(
	select tools.outlier_detection(animals_id, -0.985, 5000, 15000) as error from main.animals where study_areas_id = 1
)
SELECT study_areas_id, b.animals_id, error 
FROM a JOIN main.gps_data_animals b ON (error = gps_data_animals_id) JOIN main.animals USING (animals_id) 
ORDER BY study_areas_id, b.animals_id;
-to content-