Useful Queries
Contents
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 :: int4, '2010-01-01 00:00:00', '2011-01-01 00:00:00')) as total_distance from gps.ee_tracker 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 :: int4, '2010-01-01 00:00:00', '2011-01-01 00:00:00')) as total_distance from (select distinct device_info_serial from gps.ee_tracker_limited) t 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_speed_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