Difference between revisions of "Useful Queries"

From ecology
Jump to: navigation, search
Line 1: Line 1:
 +
= Calculating summary statistics =
 +
== Total distance ==
 
A query to determine the total distance a bird has flown is :  
 
A query to determine the total distance a bird has flown is :  
  
Line 10: Line 12:
  
  
If you want to do this for all ID's at the same time you can do something like :
+
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
 
  select device_info_serial,  (select sum(distance)/1000
Line 22: Line 24:
 
  from gps.uva_device_limited
 
  from gps.uva_device_limited
  
 +
== Total number of measurements per tag ==
  
 
+
Count the total number of measurements for each device using the GROUP BY clause:
The GROUP BY clause in SQL can be used to count the total number of measurements for each device :
 
  
 
  select device_info_serial, count(*)  
 
  select device_info_serial, count(*)  
Line 57: Line 59:
 
   group by device_info_serial, valid  
 
   group by device_info_serial, valid  
 
   order 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 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):

Revision as of 15:23, 7 February 2012

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

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

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; 

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;

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 = 1 
AND t.latitude IS  NOT NULL AND t.userflag <> 1