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

 SELECT date_time, latitude, longitude
 FROM gps.uva_tracking_data101
 WHERE
   device_info_serial = '540' AND
   date_time >= '20
 
 ORDER BY date_time