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;

Combining bird track data with flow data

If flow data are available for the location of the bird, flow data can be added to the bird track:

 SELECT date_time, latitude, longitude, (uv).uvx, (uv).uvy FROM (
   SELECT date_time, latitude, longitude, location,
          flow.waddenzee_fijn_uv(location, date_time) as uv
   FROM gps.uva_tracking_data101
   WHERE device_info_serial = '373' AND
         date_time >= '2010-06-24 0:00:00' AND
         date_time <= '2010-06-24 0:20:00'
   ORDER BY date_time
 ) q;

Combining bird track data with flow data and wind data

The bird track data can also be combined with weather forecast data:

 SELECT date_time,
     latitude,
     longitude,
     (uv).uvx,
     (uv).uvy,
     (fc).u10,
     (fc).v10
 FROM (
   SELECT date_time, latitude, longitude, location,
          flow.waddenzee_fijn_uv(location, date_time) as uv,
          ecmwf.forecast_interpolate(latitude, longitude, date_time) as fc
   FROM gps.uva_tracking_data101
   WHERE device_info_serial = '373' AND
         date_time >= '2010-06-24 0:00:00' AND
         date_time <= '2010-06-24 0:20:00'
   ORDER BY date_time
 ) q;