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;

This query collects the following variables in one table:

• the position of the bird in geographical coordinates;
• the eastward (uvx) and nordward (uvy) velocity components of the water velocity in m/s;
• the eastward (u10) and nordward (v10) velocity components of the wind at 10m height, in m/s.

The flow data were obtained from the depth-averaged Unstruc model by Deltares. Please keep in mind that the water velocity is the average over the depth and that the flow at the surface may differ.