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

Calculate total distance per bird for all ID's at the same time (use this with care if you have access to a lot of data):

select device_info_serial,  (select sum(distance)/1000
  from gps.get_uvagps_track_speed(device_info_serial, '2010-01-01 00:00:00', '2011-01-01 00:00:00')) as total_distance
from gps.uva_device
ORDER BY device_info_serial ASC

For limited users :

select device_info_serial,  (select sum(distance)/1000
  from gps.get_uvagps_track_speed(device_info_serial, '2010-01-01 00:00:00', '2011-01-01 00:00:00')) as total_distance
from gps.uva_device_limited
ORDER BY device_info_serial ASC

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.uva_tracking
group by device_info_serial
ORDER BY device_info_serial ASC; 

and for limited users :

select device_info_serial, count(*) 
from gps.uva_tracking_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.uva_tracking 
where latitude is not null 
group by device_info_serial
ORDER BY device_info_serial ASC;

If you to see want both the count of valid and invalid GPS fixes per device you can do something like :

 select device_info_serial, latitude is not null as valid, count(*) 
 from gps.uva_tracking 
 group by device_info_serial, valid 
 order by device_info_serial, valid;

and for limited users:

 select device_info_serial, latitude is not null as valid, count(*) 
 from gps.uva_tracking_limited 
 group by device_info_serial, valid 
 order by device_info_serial, valid;

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.uva_tracking_speed_limited t, gps.uva_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