Queries for linking bird tracks to wind and flow data

Combining devices with birds

Devices have no information directly about the bird it is attached to; the table 'gps.uva_track_session' can be used to get this information:

 SELECT ud.device_info_serial, ts.ring_number
 FROM gps.uva_device ud
 LEFT JOIN gps.uva_track_session ts
   ON ud.device_info_serial = ts.device_info_serial;

Join with the table 'gps.uva_individual'; then list only 'Larus fuscus':

 SELECT ud.device_info_serial, ui.species
 FROM gps.uva_device ud
 LEFT JOIN gps.uva_track_session ts
   ON ud.device_info_serial = ts.device_info_serial
 LEFT JOIN gps.uva_individual ui
   ON ts.ring_number = ui.ring_number
 WHERE ui.species = 'Larus fuscus';

Getting the bird track for one device

The following example gets the position of one bird as a function of the time for one day:

 SELECT date_time, latitude, longitude
 FROM gps.uva_tracking_data101
 WHERE device_info_serial = '330' AND
       date_time >= '2010-05-23 0:00:00' AND
       date_time <= '2010-05-24 0:00:00'
 ORDER BY date_time;