Useful Queries
From ecology
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');
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
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
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 group by device_info_serial;
and for limited users :
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;
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;