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 'ee_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.ee_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.ee_tracker ud
 LEFT JOIN gps.uva_track_session ts
   ON ud.id = ts.device;

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.ee_tracker ud
 LEFT JOIN gps.ee_track_session ts
   ON ud.id = ts.device
 LEFT JOIN gps.ee_individual ui
   ON ts.individual = ui.id
 LEFT JOIN gps.ee_species s
   ON ui.species = s.id
 WHERE s.latin_name = '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 using a more complicated query. The example query below retrieves in one table:

• the position of one bird (latitude, longitude);
• the depth-averaged water velocity at the ground location and time of the bird, towards east and north, in m/s, as computed by the 'Waddenzee fijn' model by Deltares (uvx, uvy)
• the wind velocity at 10 m height at the ground location and time of the bird, towards east and north, in m/s, as computed in the ECMWF forecast model (u10, v10).

The results are computed for the bird with device id 373 for June 24th 2010 from 0:00:00 to 0:20:00. To retrieve the data for other birds and for other dates times, replace the current values with the desired values.

The flow velocities are available for the Waddenzee and a strip of North Sea, from April 1st 2010 to July 21st 2010.

 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.