Useful Queries

Useful Queries

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

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

or

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

If you want to do this for all ID's at the same time you can do something like :

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)

The GROUP BY clause in SQL can be used to count the total number of measurements for each device :

select device_info_serial, count(*) 
from gps.uva_tracking(_limited) 
group by device_info_serial; 

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;

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;