Difference between revisions of "Useful Queries"

From ecology
Jump to: navigation, search
Line 57: Line 57:
 
   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 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

Revision as of 15:14, 7 February 2012

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;

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