ecology - User contributions [en]

E-Ecology Documentation and Guidance

2013-05-21T12:09:16Z

Susan branchett:

The e-Ecology Virtual Laboratories represent a collaborative effort between the University of Amsterdam Institute for Biodiversity and Ecosystem Dynamics and SARA Computing to manage bird tracking data and develop tools for data exploration, analysis and visualization. This wiki is designed to facilitate access to the data and tools within the Virtual Laboratories and to provide a forum for collaboration.

== Datasets ==

'''UvA-BiTS'''

:Bird data collected with the UvA-BiTS Bird Tracking System

::* [[Connecting to the UvA-BiTS database]]
::* [[ UvAGPS|UvA-BiTS Data Model]]
::* [[Useful Queries]]
::* [[Queries for linking bird tracks to wind and flow data]]

'''FLYSAFE'''

:Bird data created from weather radar data and bird migration models. Financed by the Royal Netherlands Air Force.

::* [[Connecting to the Flysafe database]]
::* [[GPS|Flysafe GPS Data Model]]
::* [[ RADAR|Radar Data Model]]
::* [[ Wind profile|Wind Profile Data Model]]
::* [[ Fino1|Fino1 Data Model]]

'''ENVIRONMENTAL DATA'''

::* [[ ECMWF|Weather Forecast Data (ECMWF)]]
::* [[ Elevation|Elevation Data ]]

==Services==

* Visualization Tools
:: [[GPS tracks|KMZ Generator]]: <br />
:::Produce kmz files of GPS bird tracking data to be viewed in Google Earth.
:: [[BirdView]]: <br />
:::Access meterological and radar data and overlay with GPS bird tracking data.
:: [[BirdSim]]: <br/ >
:::More dynamic version of BirdView that allows the user to scroll through weather data in time and space.

* [[ Annotation tables ]] for uploading GPS anntotation data into your own schema

* [[Dashboard]]: These web pages allow UvA-BiTS users to get an overview of the raw data files of GPS bird tracking devices.
:: [[ FilesDashboard | Tracker files dashboard ]]
:: [[ BatteriesDashboard | Batteries dashboard ]]

* [[ Bookkeeping]]: Description of tables related to FlySafe data processing and archiving.

[[ Administration]]: Information about user rights and data processing tasks.

[[ ToDo ]]

E-Ecology Documentation and Guidance

2013-05-21T12:08:51Z

Susan branchett:

Queries for linking bird tracks to wind and flow data

2013-05-17T12:07:47Z

Susan branchett:

=== 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 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.

Queries for linking bird tracks to wind and flow data

2013-05-17T12:06:38Z

Susan branchett: