View source for 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.