Difference between revisions of "Useful Queries"

From ecology
Jump to: navigation, search
(Total distance)
(Total number of measurements per tag)
Line 24: Line 24:
  
 
  select device_info_serial, count(*)  
 
  select device_info_serial, count(*)  
  from gps.uva_tracking
+
  from gps.ee_tracking_speed_limited
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
 
  group by device_info_serial
 
  ORDER BY device_info_serial ASC;  
 
  ORDER BY device_info_serial ASC;  
Line 39: Line 32:
 
    
 
    
 
  select device_info_serial, count(*)  
 
  select device_info_serial, count(*)  
  from gps.uva_tracking
+
  from gps.ee_tracking_speed_limited
 
  where latitude is not null  
 
  where latitude is not null  
 
  group by device_info_serial
 
  group by device_info_serial
 
  ORDER BY device_info_serial ASC;
 
  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 tracking data from the beginning of a track session ==

Revision as of 10:05, 23 October 2014

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 (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.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.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