Useful Queries

Calculating summary statistics

Total distance

A query to determine the total distance a bird has flown is :

select sum(distance)/1000 as total_distance
from gps.get_uvagps_track_speed(<ID>);

or for a fixed time period :

select sum(distance)/1000 as total_distance
from gps.get_uvagps_track_speed(<ID>, '2010-01-01 00:00:00', '2011-01-01 00:00:00');

Total number of measurements per tag

Count the total number of measurements for each device using the GROUP BY clause:

select device_info_serial, count(*) 
from gps.ee_tracking_speed_limited
group by device_info_serial
ORDER BY device_info_serial ASC; 

If you want to count the number of measurements with valid GPS fixes you can add a where clause :

select device_info_serial, count(*) 
from gps.ee_tracking_speed_limited
where latitude is not null 
group by device_info_serial
ORDER BY device_info_serial ASC;

Select tracking data from the beginning of a track session

Select only those points that were collected during a valid track session (i.e. data that begins from the start_date of a track session) including 2D GPS speed. Filter our points where no GPS fix is returned (latitude is not null) and where a user has flagged the data as inappropriate (user_flag <> 1):

SELECT t.device_info_serial, t.date_time, t.latitude, t.longitude, t.altitude, t.temperature, t.speed, t.userflag
FROM gps.ee_tracking_speed_limited t, gps.ee_track_session_limited s
WHERE t.device_info_serial = s.device_info_serial AND t.date_time >= s.start_date AND t.device_info_serial = <ID>
  AND t.latitude IS  NOT NULL AND t.userflag <> 1

How to separate birds or tag deployments when querying data where the same GPS logger has been used more than once or the same individual has been tracked with more than on logger

NOTE: The current query does not work yet and can only be implemented in a testing environment - we expect to have this ready by December 2014

In this query you will select the tracking data related to each deployment of a GPS logger. This query is slightly more complex than the query above. If you have used the same logger on more than one individual bird, or have tracked the same individual with more than one logger, this query will enable you to make that distinction, by showing both GPS logger id (device_info_serial) and a unique identifier for your individual (eg ring_number or colour_ring). Furthermore, as above, this will only select tracking data from the start and end of a particular track session.

 SELECT t.device_info_serial, t.date_time, t.latitude, t.longitude, t.altitude, t.speed, i.colour_ring, s.track_session_id, s.key_name
 FROM webapp.ee_track_session_limited s
 JOIN webapp.ee_individual_limited i
   ON s.individual_id = i.individual_id
 JOIN webapp.ee_tracking_speed_limited t
   ON t.device_info_serial = s.device_info_serial
 AND t.date_time BETWEEN s.start_date AND s.end_date
 WHERE s.key_name = '<Project name>' AND t.userflag = 0
 ORDER BY t.device_info_serial, t.date_time

HINT: WHERE s.key_name = 'HG_TEXEL'