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, and the 'individual' table for birds does not specify which devices it has. This is logical, because a device may be replaced with another device, so in the tracking data, one bird can have two (or more) devices. Also, device can be used for more than one bird, in the tracking data, one device can have more than one bird. To link devices with birds, the table 'gps.uva_track_session' contains 'sessions' of consecutive periods where one device is attached to one bird. This table can be used to join device information with bird information.

The basic join is as follows:

 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;

This join can be extended with more joins. The following example shows which devices are connected to Lesser Black-Backed Gulls (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.