Difference between revisions of "UvA-BiTS Tracking Data"

From ecology
Jump to: navigation, search
(→‎gps.ee_acc_start_limited)
(→‎Data processing and filtering)
 
(53 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
== ''gps.''ee_project_limited ==
+
== UvA-BiTS Tracking Data ==
  
−
Each row shows the details of each project, for the projects you are allowed to see.
+
'''Note:''' These views will show no rows for trackers not yet attached to birds.
 +
 
 +
=== ''gps.''ee_tracking_speed_limited ===
 +
 
 +
Shows the tracking data that your projects allow you to see.
 +
 
 +
Each row represents a GPS fix as it is interpreted from the data that comes via the GPS tracker, plus it adds speeds. See [[Reference systems for GPS coordinates and altitudes]] for further details.
 +
 
 +
It replaces old views gps.uva_tracking_limited, gps.uva_tracking_speed_3d_limited and gps.uva_tracking_speed. This table structure has ''not'' changed with respect to our previous database.  
  
 
{| class="wikitable"
 
{| class="wikitable"
Line 9: Line 17:
 
! DESCRIPTION
 
! DESCRIPTION
 
|-
 
|-
−
|key_name||varchar||NO||Unique key name, with the form <species>_<place>
+
|device_info_serial||int4||NO||Serial number of the tracker that reported the fix. Part of the unique identifier of the GPS fix.
 +
|-
 +
|date_time||timestamp||NO||Time when the fix was taken. UTC (i.e.: no timezone). Part of the unique identifier of the GPS fix.
 +
|-
 +
|latitude||float8||YES||Latitude of the GPS fix, decimal degrees (WGS84 datum)
 +
|-
 +
|longitude||float8||YES||Longitude of the GPS fix, decimal degrees (WGS84 datum)
 +
|-
 +
|altitude||int4||YES||Altitude above sea level measured by GPS tag in meters (WGS84 datum)
 +
|-
 +
|pressure||int4||YES||Pressure measured by GPS tag sensor in Pascals
 +
|-
 +
|temperature||float8||YES||Temperature measured by GPS tag sensor in Celsius
 +
|-
 +
|h_accuracy||float8||YES||Horizontal accuracy (meters)
 +
|-
 +
|v_accuracy||float8||YES||vertical accuracy (meters)
 +
|-
 +
|x_speed||float8||YES||meters/second
 +
|-
 +
|y_speed||float8||YES||meters/second
 +
|-
 +
|z_speed||float8||YES||meters/second
 +
|-
 +
|gps_fixtime||float8||YES||GPS fix time (also known as Time to Fix) in seconds
 +
|-
 +
|userflag||int4||YES||Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
 +
|-
 +
|satellites_used||int2||YES||Number of satellites used for fix the GPS measurement
 +
|-
 +
|positiondop||float8||YES||Positional DOP (Dilution of Precision), unitless
 +
|-
 +
|speed_accuracy||float8||YES||
 +
|-
 +
|location||geometry||YES||postgreSQL geometry; to allow GIS operations
 +
|-
 +
|vnorth||float8||YES||- vx * sin(lat) * cos(long) - vy * sin(lat) * sin (long) + vz * cos(lat), m/s
 +
|-
 +
|veast||float8||YES||- vx * sin(lon) + vy * cos(lon), m/s
 
|-
 
|-
−
|station_name||varchar||NO||Where the receiving station is
+
|vdown||float8||YES||- vx * cos(lat) * cos(lon) - vy * cos(lat) * sin(lon) - vz * sin(lat), m/s
 
|-
 
|-
−
|start_date||timestamp||NO||Project start date
+
|speed||float8||YES||- vx * sin(lon) + vy * cos(lon)
 
|-
 
|-
−
|end_date||timestamp||NO||Date at which the project is not relevant any more
+
|speed_3d||float8||YES|| sqrt(d.x_speed^2 + d.y_speed^2 + d.z_speed^2), m/s
 
|-
 
|-
−
|description||text||YES||String for free description. Updatable by the user.
+
|speed_2d||float8||YES|| Speed on 2D, m/s
 
|-
 
|-
−
|project_id||int8||NO||Project unique id. Included just to enable joins.
+
|direction||numeric||YES|| Heading in degrees; 0 is north, 90 is east
 
|-
 
|-
−
|parent_id||int8||YES||Reference to the parent project in the hierarchy.
+
|altitude_agl||float8||YES|| Reported GPS altitude MINUS terrain elevation on the coordinate based on Digital Elevation Model data used in the e-ecology database (for further information see: [[Elevation]]
 
|}
 
|}
  
−
== ''gps.''ee_tracker_limited ==
+
=== ''gps.''ee_acceleration_limited ===
 +
 
 +
Acceleration is measured in blocks of 1-200 measurements (records). The date_time  mentioned in this block is the date and time of the first acceleration measurement. Sequential measurements within a block are numbered with an index 1... (max 200). Every row represents one measurement in 3 directions (X,Y,Z). The order is important within each block, so you should always sort the acceleration measurements by block and index. If you need the exact time for each row, you can calculate it from the index, the sample frequency (which is almost always 20 Hz) and the date_time of the first row in the block.
 +
 
 +
The date_time of the first block is the time of the GPS measurement preceding the accelerometer measurement. If there is no valid GPS measurement, this time is calculated from the last GPS measurement and the time of the internal clock of the tracker.
 +
 
 +
Therefore, to know all valid dates and times that mark a valid block for a tracker, you need to consider all of the values in column date_time in gps.ee_tracking_speed_limited for the given tracker, column date_time on gps.ee_acc_start_limited, and all the values on column date_time for that tracker.
  
−
Each row shows tracker details for all trackers that your projects allow you to see.
 
  
−
This view replaces the old gps.uva_device_limited.
+
This table structure has ''not'' changed with respect to our previous database.
  
 
{| class="wikitable"
 
{| class="wikitable"
Line 36: Line 87:
 
! DESCRIPTION
 
! DESCRIPTION
 
|-
 
|-
−
|device_info_serial||int8||NO||Serial number of the tracker
+
|device_info_serial||int4||NO||Serial number of the tracker that reported the fix. Part of the unique identifier of the GPS fix.
 +
|-
 +
|date_time||timestamp||NO||Time of the acceleration block that this measurement belongs to (time in UTC, no time zone).
 
|-
 
|-
−
|firmware_version||varchar||YES||String for firmware version
+
|index||int2||NO||Sorting order of the measurement within the acceleration block (each block starts at 0).
 
|-
 
|-
−
|mass||numeric(4,2)||YES||Mass (in grams)
+
|x_acceleration||int2||YES||Acceleration measured on the x (or surge) axis (range between -2666 and 2666).
 
|-
 
|-
−
|start_date||timestamp||NO||Date before which the device is not relevant
+
|y_acceleration||int2||YES||Acceleration measured on the y (or sway) axis (range between -2666 and 2666).
 
|-
 
|-
−
|end_date||timestamp||NO||Date at which the device is not relevant any more
+
|z_acceleration||int2||YES||Acceleration measured on the z (or heave) axis (range between -2666 and 2666).
 +
|}
 +
 
 +
=== ''gps.''ee_acc_start_limited ===
 +
 
 +
Each row represents the start of a block of acceleration measurements when a GPS fix is not available at that acceleration block's time.
 +
 
 +
If there is no valid GPS measurement, this time is calculated from the last GPS measurement and the time of the internal clock of the tracker.
 +
 +
To know all valid dates and times that mark a valid block for a tracker, you need to consider all of the values in column date_time on gps.ee_tracking_speed_limited for the given tracker, column date_time on gps.ee_acc_start_limited, and all the values on column date_time for that tracker. And the order is important within each block, so you should sort the acceleration measurements by block and index.
 +
 
 +
 
 +
Added in firmware 1.0.2.7.
 +
 
 +
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 
|-
 
|-
−
|x_o||numeric(30,6)||YES||Accelerometer calibration: offset on the x (or surge) axis
+
|device_info_serial||int4||NO||Serial number of the tracker that reported the acceleration measurement.
 
|-
 
|-
−
|x_s||numeric(30,6)||YES||Accelerometer calibration: sensitivity on the x (or surge) axis
+
|date_time||timestamp||NO||Time to mark the start of the acceleration block.
 
|-
 
|-
−
|y_o||numeric(30,6)||YES||Accelerometer calibration: offset on the y (or sway) axis
+
|line_counter||int4||NO||Number of lines associated with this set of accelerations.
 
|-
 
|-
−
|y_s||numeric(30,6)||YES||Accelerometer calibration: sensitivity on the y (or sway) axis
+
|timesynced||int2||YES||The source of the time that marks the acceleration block (0=not synced, 1=user set, 2=Gps Synced).
 
|-
 
|-
−
|z_o||numeric(30,6)||YES||Accelerometer calibration: offset on the z (or heave) axis
+
|ascii||int4||YES||Acceleration measurement interval.
 
|-
 
|-
−
|z_s||numeric(30,6)||YES||Accelerometer calibration: sensitivity on the y (or heave) axis
+
|accsn||int4||YES||Number of samples (0-65536).
 
|-
 
|-
−
|tracker_id||int8||NO||Tracker unique id. Included just to enable joins.
+
|f||int2||YES||Acceleration sampling frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz).
 
|}
 
|}
  
−
== ''gps.''ee_individual_limited ==
+
== Bird data ==
 +
=== ''gps.''ee_individual_limited ===
  
 
Each row shows bird's details for all birds that your projects allow you to see.
 
Each row shows bird's details for all birds that your projects allow you to see.
Line 83: Line 155:
 
|mass||numeric(5,0)||YES||Mass (grams)
 
|mass||numeric(5,0)||YES||Mass (grams)
 
|-
 
|-
−
|start_date||timestamp||NO||Date before which the bird is not relevant.
+
|start_date||timestamp||NO||Date when the bird was first tracked with an UvA-BiTS logger.
 
|-
 
|-
−
|end_date||timestamp||NO||Date at which the bird is no longer relevant.
+
|end_date||timestamp||NO||Date at which the bird is no longer relevant for the tracking project.
 
|-
 
|-
 
|remarks||text||YES||Text for user remarks
 
|remarks||text||YES||Text for user remarks
Line 92: Line 164:
 
|}
 
|}
  
−
== ''gps.''ee_track_session_limited ==
+
=== ''gps.''ee_species_limited ===
  
−
Each row shows track session's information for all track sessions that your projects allow you to see.  
+
Each row represents a species that you can assign to your birds to include them in your project. No bird in the system can exist without being linked to one of the species from this list. If you are tagging a bird whose species does not exist in the list, please [http://www.uva-bits.nl/contact/ contact] us.
  
 
{| class="wikitable"
 
{| class="wikitable"
Line 102: Line 174:
 
! DESCRIPTION
 
! DESCRIPTION
 
|-
 
|-
−
|key_name||varchar||NO||The project that the track session belongs to.
+
|abbr||varchar||NO||Unique abbreviation for this species.
 +
|-
 +
|english_name||varchar||YES||Name of the species in English.  
 
|-
 
|-
−
|device_info_serial||int8||NO||Serial number of the tracker this track session involves.
+
|latin_name||varchar||NO||Name of the species in Latin. It is always filled in.
 
|-
 
|-
−
|ring_number||varchar||NO||Ring number of the bird that this track session involves.
+
|species_id||int8||NO||Unique id. Added to allow joins.
 +
|}
 +
 
 +
=== ''gps.''ee_nest_limited ===
 +
This view shows nests you have access to. A nest is always for a bird with a tracker (so a track session must exist for that bird), and must contain a short name (a reference name for you to use) and a latitude and longitude pair.
 +
 
 +
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 
|-
 
|-
−
|start_date||timestamp||NO||Date before which the track session is not relevant
+
|reference_name||varchar||NO||A string with a useful reference for you. We recommend its being unique for each nest in a project, but the system does NOT enforce it.
 
|-
 
|-
−
|end_date||timestamp||NO||Date at which the track session is not relevant any more
+
|latitude||numeric||YES||Latitude of the coordinates where the nest was found, decimal degrees (WGS84 datum)
 
|-
 
|-
−
|remarks||text||YES||Free text for comments
+
|longitude||numeric||YES||Longitude of the coordinates where the nest was found, decimal degrees (WGS84 datum)
 
|-
 
|-
−
|start_latitude||numeric(11,8)||NO||Latitude where the bird was tagged
+
|start_date_time||timestamp||NO||When the nest was spotted.
 
|-
 
|-
−
|start_longitude||numeric(11,8)||NO||Longitude where the bird was tagged
+
|end_date_time||timestamp||NO||When the nest ceases to be relevant.
 
|-
 
|-
−
|project_id||int8||NO||Reference to the project this track session belongs to. Included just to enable joins.
+
|found_by_whom||varchar||YES||Place to credit people involved in finding the nest.
 
|-
 
|-
−
|tracker_id||int8||NO||Reference to the tracker this track session involves. Included just to enable joins.
+
|remarks||text||YES||Free text.
 
|-
 
|-
−
|individual_id||int8||NO||Reference to the individual this track session involves. Included just to enable joins.
+
|location||bytearray||YES||Calculated Geometry of the coordinates where the nest was found.
 
|-
 
|-
−
|track_session_id||int8||NO||Unique identifier of the track session. Included just to enable joins.
+
|nest_id||int8||NO||Unique id. Added to allow joins to nest_inhabitant_limited.
 
|}
 
|}
  
−
== ''gps.''ee_tracking_speed_limited ==
+
=== ''gps.''ee_nest_inhabitant_limited ===
−
 
+
This view shows who lives on which nest, for birds you have access to.  
−
Shows the tracking data that your projects allow you to see.
 
−
 
 
−
Each row represents a GPS fix as it is interpreted from the data that comes via the GPS tracker, plus it adds speeds.
 
−
 
 
−
It replaces old views gps.uva_tracking_limited, gps.uva_tracking_speed_3d_limited and gps.uva_tracking_speed. This table structure has ''not'' changed with respect to our previous database.  
 
  
 
{| class="wikitable"
 
{| class="wikitable"
Line 141: Line 220:
 
! DESCRIPTION
 
! DESCRIPTION
 
|-
 
|-
−
|device_info_serial||int4||NO||Serial number of the tracker that reported the fix. Part of the unique identifier of the GPS fix.
+
|reference_name||varchar||NO||A string belonging to the nest that you can use to identify that nest. Because it is NOT enforced to be unique, DO NOT USE THIS FIELD FOR JOINING.
 +
|-
 +
|ring_number||varchar||NO||The ring number of a bird on a nest.
 +
|-
 +
|device_info_serial||int8||NO||Following the link from a bird to her track sessions, and considering the nest's start and end time, the bird's trackers within that period are resolved so that they can be shown here. This is the device_info_serial of a tracker.
 
|-
 
|-
−
|date_time||timestamp||NO||Time when the fix was taken. UTC (i.e.: no timezone). Part of the unique identifier of the GPS fix.  
+
|key_name||varchar||NO||Following the link from a bird to her track sessions, and considering the nest's start and end time, the track sessions' projects within that period are resolved so that they can be shown here. This is the key_name of a project.
 
|-
 
|-
−
|latitude||float8||YES||Latitude of the GPS fix.
+
|nest_id||int8||NO||Reference to the nest id. Added to allow joins.
 
|-
 
|-
−
|longitude||float8||YES||Longitude of the GPS fix.
+
|individual_id||int8||NO||Reference to the individual id. Added to allow joins.
 
|-
 
|-
−
|altitude||int4||YES||Altitude above sea level measured by GPS tag in meters
+
|track_session_id||int8||NO||Reference to a resolved track session id. Added to allow joins.
 +
|}
 +
== Tracker and project data ==
 +
 
 +
=== ''gps.''ee_project_limited ===
 +
 
 +
Each row shows the details of each project, for the projects you are allowed to see.
 +
 
 +
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 
|-
 
|-
−
|pressure||int4||YES||Pressure measured by GPS tag sensor in Pascals
+
|key_name||varchar||NO||Unique key name, with the form <species>_<place>
 
|-
 
|-
−
|temperature||float8||YES||Temperature measured by GPS tag sensor in Celsius
+
|station_name||varchar||NO||Where the receiving station is
 
|-
 
|-
−
|h_accuracy||float8||YES||Horizontal accuracy (meters)
+
|start_date||timestamp||NO||Project start date
 
|-
 
|-
−
|v_accuracy||float8||YES||vertical accuracy (meters)
+
|end_date||timestamp||NO||Date at which the project is not relevant any more
 
|-
 
|-
−
|x_speed||float8||YES||meters/second
+
|description||text||YES||String for free description. Updatable by the user.
 
|-
 
|-
−
|y_speed||float8||YES||meters/second
+
|project_id||int8||NO||Project unique id. Included just to enable joins.
 
|-
 
|-
−
|z_speed||float8||YES||meters/second
+
|parent_id||int8||YES||Reference to the parent project in the hierarchy.
 +
|}
 +
 
 +
=== ''gps.''ee_tracker_limited ===
 +
 
 +
Each row shows tracker details for all trackers that your projects allow you to see.
 +
 
 +
This view replaces the old gps.uva_device_limited.
 +
 
 +
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 
|-
 
|-
−
|gps_fixtime||float8||YES||GPS fix time (also known as Time to Fix) in seconds
+
|device_info_serial||int8||NO||Serial number of the tracker
 
|-
 
|-
−
|userflag||int4||YES||Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
+
|firmware_version||varchar||YES||String for firmware version
 
|-
 
|-
−
|satellites_used||int2||YES||Number of satellites used for fix the GPS measurement
+
|mass||numeric(4,2)||YES||Mass (in grams)
 
|-
 
|-
−
|positiondop||float8||YES||Positional DOP (Dilution of Precision), unitless
+
|start_date||timestamp||NO||Date before which the device is not relevant
 
|-
 
|-
−
|speed_accuracy||float8||YES||
+
|end_date||timestamp||NO||Date at which the device is not relevant any more
 
|-
 
|-
−
|location||geometry||YES||postgreSQL geometry; to allow GIS operations
+
|x_o||numeric(30,6)||YES||Accelerometer calibration: offset on the x (or surge) axis
 
|-
 
|-
−
|vnorth||float8||YES||- vx * sin(lat) * cos(long) - vy * sin(lat) * sin (long) + vz * cos(lat)
+
|x_s||numeric(30,6)||YES||Accelerometer calibration: sensitivity on the x (or surge) axis
 
|-
 
|-
−
|veast||float8||YES||- vx * sin(lon) + vy * cos(lon)
+
|y_o||numeric(30,6)||YES||Accelerometer calibration: offset on the y (or sway) axis
 
|-
 
|-
−
|vdown||float8||YES||- vx * cos(lat) * cos(lon) - vy * cos(lat) * sin(lon) - vz * sin(lat)
+
|y_s||numeric(30,6)||YES||Accelerometer calibration: sensitivity on the y (or sway) axis
 
|-
 
|-
−
|speed||float8||YES||- vx * sin(lon) + vy * cos(lon)
+
|z_o||numeric(30,6)||YES||Accelerometer calibration: offset on the z (or heave) axis
 
|-
 
|-
−
|speed3d||float8||YES||- vx * cos(lat) * cos(lon) - vy * cos(lat) * sin(lon) - vz * sin(lat)
+
|z_s||numeric(30,6)||YES||Accelerometer calibration: sensitivity on the y (or heave) axis
 
|-
 
|-
−
|direction||numeric||YES||Heading in degrees; 0 is north, 90 is east
+
|tracker_id||int8||NO||Tracker unique id. Included just to enable joins.
 
|}
 
|}
  
−
== ''gps.''ee_acceleration_limited ==
+
=== ''gps.''ee_track_session_limited ===
  
−
Each row represents an acceleration measurement as it is interpreted from the data that comes via the accelerometer. Acceleration is looked at in batches for a period of time, for 3 components (X,Y,Z). The blocks are delimited by a starting date and time, and then each observation is numbered consecutively.
+
A track session links a tracker and a bird for a given period of time. Therefore, it is '''mandatory for projects''' to supply this piece of '''metadata''' before they can see any tracking data related to a tracker they own.  
  
−
The logger tries to mark the start of blocks from the GPS fix that coincides with the beginning of the acceleration block. If there's no GPS fix during that block, then it extrapolates the date and time and makes a special record which is interpreted by our accelerometer processing software and you can find it via gps.ee_acc_start_limited. Therefore, to know all valid dates and times that mark a valid block for a tracker, you need to consider all of the values in column date_time on gps.ee_tracking_speed_limited for the given tracker, column date_time on gps.ee_acc_start_limited, and all the values on column date_time for that tracker. And the order is important within each block, so you should sort the acceleration measurements by block and index.
+
In order to create a track session, the project must create first an entry in the system for the bird that they are attaching the tracker to, and this is the way for the UvA-BiTS community to make sense of the tracking data that we are receiving. This is why providing the metadata is so important.
  
−
This table structure has ''not'' changed with respect to our previous database.
+
By editing the start and end dates, administrators of the project that this track session belongs to, can delimit (and therefore, establish) when the coupling makes sense (or is to be understood as useful).
 +
 
 +
Whenever a tracker is attached to a bird, a track session must be started (start_date) to indicate that the tracking data that is received is already live data. If the tracker is separated from the bird, the tracking data ceases to make sense, so the track session's end date should be set to denote it has ended. New track sessions can be started if the tracker is re-attached to a bird (possibly the same as before, or maybe a different one) afterwards.
 +
 
 +
Each row shows track session's information. There exists a row for each track session that your projects allow you to see.  
  
 
{| class="wikitable"
 
{| class="wikitable"
Line 204: Line 316:
 
! DESCRIPTION
 
! DESCRIPTION
 
|-
 
|-
−
|device_info_serial||int4||NO||Serial number of the tracker that reported the fix. Part of the unique identifier of the GPS fix.
+
|key_name||varchar||NO||The project that the track session belongs to.
 +
|-
 +
|device_info_serial||int8||NO||Serial number of the tracker this track session involves.
 +
|-
 +
|ring_number||varchar||NO||Ring number of the bird that this track session involves.
 +
|-
 +
|start_date||timestamp||NO||Date before which the track session is not relevant
 +
|-
 +
|end_date||timestamp||NO||Date at which the track session is not relevant any more
 +
|-
 +
|remarks||text||YES||Free text for comments
 +
|-
 +
|start_latitude||numeric(11,8)||NO||Latitude where the bird was tagged, decimal degrees (WGS84 datum)
 
|-
 
|-
−
|date_time||timestamp||NO||Time of the acceleration block that this measurement belongs to.
+
|start_longitude||numeric(11,8)||NO||Longitude where the bird was tagged, decimal degrees (WGS84 datum)
 
|-
 
|-
−
|index||int2||NO||Sorting order of the measurement within the acceleration block (each block starts at 0).
+
|project_id||int8||NO||Reference to the project this track session belongs to. Included just to enable joins.
 
|-
 
|-
−
|x_acceleration||int2||YES||Acceleration measured on the x (or surge) axis (range between -2666 and 2666).
+
|tracker_id||int8||NO||Reference to the tracker this track session involves. Included just to enable joins.
 
|-
 
|-
−
|y_acceleration||int2||YES||Acceleration measured on the y (or sway) axis (range between -2666 and 2666).
+
|individual_id||int8||NO||Reference to the individual this track session involves. Included just to enable joins.
 
|-
 
|-
−
|z_acceleration||int2||YES||Acceleration measured on the z (or heave) axis (range between -2666 and 2666).
+
|track_session_id||int8||NO||Unique identifier of the track session. Included just to enable joins.
 
|}
 
|}
  
−
== ''gps.''ee_acc_start_limited ==
+
== Relation to projects ==
  
−
Each row represents the start of a block of acceleration measurements when a GPS fix is not available at that acceleration block's time.
+
=== ''gps.''ee_user_limited ===
  
−
The logger tries to mark the start of blocks from the GPS fix that coincides with the begin of the acceleration block. If there's no GPS fix during that block, then it extrapolates the date and time and makes a special record which is interpreted by our accelerometer processing software and made available through this view.  
+
Each row shows a colleague in a project that you belong to, indicating her role and status there.
  
−
Therefore, to know all valid dates and times that mark a valid block for a tracker, you need to consider all of the values in column date_time on gps.ee_tracking_speed_limited for the given tracker, column date_time on gps.ee_acc_start_limited, and all the values on column date_time for that tracker. And the order is important within each block, so you should sort the acceleration measurements by block and index.
+
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 +
|-
 +
|key_name||varchar||NO||Project name
 +
|-
 +
|email||varchar||NO||e-mail address
 +
|-
 +
|login_name||varchar||NO||Login name
 +
|-
 +
|name||varchar||NO||(first) name
 +
|-
 +
|surname||varchar||NO||Surname
 +
|-
 +
|role||varchar||NO||Role in the project
 +
|-
 +
|status||varchar||NO||Status of the membership
 +
|-
 +
|user_id||int8||NO||Unique identifier of the user. Included to enable joins.
 +
|}
 +
 
 +
=== ''gps.''ee_tracker_ownership_limited ===
  
−
Added in firmware 1.0.2.7.
+
Each row that you see expresses that a tracker belongs to one of the projects you are involved with.  
  
 
{| class="wikitable"
 
{| class="wikitable"
Line 233: Line 380:
 
! DESCRIPTION
 
! DESCRIPTION
 
|-
 
|-
−
|device_info_serial||int4||NO||Serial number of the tracker that reported the acceleration measurement.
+
|key_name||varchar||NO||The project that the tracker belongs to.
 +
|-
 +
|device_info_serial||int8||NO||Serial number of the tracker.
 
|-
 
|-
−
|date_time||timestamp||NO||Time to mark the start of the acceleration block.
+
|project_id||int8||NO||Reference to the project. Included just to enable joins.
 
|-
 
|-
−
|line_counter||int4||NO||Number of lines associated with this set of accelerations.
+
|tracker_id||int8||NO||Reference to the tracker. Included just to enable joins.
 +
|}
 +
 
 +
=== ''gps.''ee_individual_ownership_limited ===
 +
 
 +
Each row that you see expresses that a bird belongs to one of the projects you are involved with.  
 +
 
 +
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 
|-
 
|-
−
|timesynced||int2||YES||The source of the time that marks the acceleration block (0=not synced, 1=user set, 2=Gps Synced).
+
|key_name||varchar||NO||The project that the individual belongs to.
 
|-
 
|-
−
|ascii||int4||YES||Acceleration measurement interval.
+
|ring_number||varchar||NO||Ring number of the bird.
 
|-
 
|-
−
|accsn||int4||YES||Number of samples (0-65536).
+
|project_id||int8||NO||Reference to the project. Included just to enable joins.
 
|-
 
|-
−
|f||int2||YES||Acceleration sampling frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz).
+
|individual_id||int8||NO||Reference to the individual. Included just to enable joins.
 
|}
 
|}
  
−
== ''gps.''ee_comm_limited ==
+
== Engineering data ==
 +
 
 +
'''Note:''' These views will show no rows for trackers not yet attached to birds.
 +
 
 +
=== ''gps.''ee_comm_limited ===
  
 
Each row indicates status and result of communications with ground stations for each tracker that your project allows you to see.  
 
Each row indicates status and result of communications with ground stations for each tracker that your project allows you to see.  
Line 308: Line 472:
 
|longitude||float8||YES||Longitude of last fix. Decimal degrees
 
|longitude||float8||YES||Longitude of last fix. Decimal degrees
 
|}
 
|}
 +
 +
=== ''gps.''ee_energy_limited ===
 +
 +
Only for newer firmware.
 +
 +
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 +
|-
 +
|device_info_serial||int4||NO||Serial number of the tracker
 +
|-
 +
|date_time||timestamp||NO||Date and time of the reported data
 +
|-
 +
|timesynced ||int2||YES||TimeSynced (0=not synced, 1=user set, 2=Gps synced)
 +
|-
 +
|devicemode ||int2||YES||(0-99)
 +
|-
 +
|vsll ||float8||YES||Solar Voltage during charging in mV (0-~2500)
 +
|-
 +
|vbat ||float8||YES||Battery Voltage in V (0-~4.200)
 +
|-
 +
|ssw ||int2||YES|| Solar Cell Switches ... (0-255) fw1022 and up
 +
|-
 +
| ||||YES||extended fields if special user switch is on
 +
|-
 +
|vsoo ||float8||YES|| Solar voltage open in V (0-~2.500)
 +
|-
 +
|vdrf ||float8||YES||reference voltage Dac in V (0-~2.435)
 +
|-
 +
|vchr ||float8||YES||charge current voltage
 +
|-
 +
|g ||int2||YES|| Gps battery threshold OK
 +
|-
 +
|c || int2||YES||Com battery threshold OK
 +
|-
 +
|m || int2||YES||Charge Mode (0,1,2 Com battery threshold OK
 +
|-
 +
|pressure || int4||YES|| in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa)
 +
|-
 +
|temperature || float8||YES|| in celcius degrees 
 +
|}
 +
 +
=== ''gps.''ee_settings_limited ===
 +
 +
Settings are either from C0-C1-C9 lines or parsed from uplink communication lines.
 +
If C0-C1 is parsed its values are compared with the previous value and if the settings are the same, then no new records need to be added.
 +
If uplink settings are parsed, then wait until the last setting has been seen (DL command or EOF); then store all the new settings in one record. Only for newer firmware.
 +
 +
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 +
|-
 +
|device_info_serial||int4||NO||
 +
|-
 +
|date_time||timestamp||NO||Derived timestamp from Hh:mi fields in C0 combined with computer timestamp in JOINED line
 +
|-
 +
|timesynced || int2 ||YES||
 +
|-
 +
|timezone_time || int2 ||YES||
 +
|-
 +
|swddd || int4||YES|| SpecialUserSwitches
 +
|-
 +
|gpsnavmode || int2||YES|| GpsNavMode 0-8. This is the Kalman filter used for GPS navigation mode often called the "Dynamic platform model". The following codes are provided by the GPS chip manufacturer for the UBlox GPS chip LEAS4S: 1 = stationary, 2 = pedestrian, 3 = automotive, 4 = sea, 5 = airborne 1G, 6 = airborne 2G, 7 = airborne 3G.  The UBlox GPS chip LEAS6S uses the following codes: 0 = portable, 2 = stationary, 3 = pedestrian, 4 = automotive, 5 = sea, 6 = airborne with < 1g acceleration, 7 = airborne with < 2g acceleration, 8 = airborne with < 4g acceleration
 +
|-
 +
|gpsreceivermode || int2 ||YES|| GpsReceiverNode 0-3
 +
|-
 +
|gpsfixtime || int2||YES|| GpsFixTime 0-255
 +
|-
 +
|gps_ivaln_on_h || int2||YES|| hours Gps IvalN on
 +
|-
 +
|gps_ivaln_on_m || int2||YES|| minutes Gps IvalN on
 +
|-
 +
|gps_ivaln  || int4||YES|| GpsIvalN (0-65530)
 +
|-
 +
|gps_ivale_on_h || int2||YES|| hours Gps IvalE on
 +
|-
 +
|gps_ivale_on_m || int2||YES|| minutes GpS IvalE on
 +
|-
 +
|gps_ivale  || int4||YES|| GpsIvalE
 +
|-
 +
|com_ivaln_on_h || int2||YES|| hours Com IvalN on
 +
|-
 +
|com_ivaln_on_m || int2||YES|| minutes Com IvalN on
 +
|-
 +
|com_ivaln || int4||YES|| ComIvalN (0-65530)
 +
|-
 +
|com_ivale_on_h || int2||YES|| hours Com IvalE on
 +
|-
 +
|com_ivale_on_m || int2||YES|| minutes Com IvalE on
 +
|-
 +
|com_ivale || int4||YES|| ComIvalE
 +
|-
 +
|accii || int4||YES|| Accelero measurement interval       
 +
|-
 +
|accbn || int4||YES|| Accelero number of blocks
 +
|-
 +
|accsn || int4||YES|| Accelero number of samples (0-65536)
 +
|-
 +
|f || int2||YES||Accelero sample frequency  (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz)
 +
|-
 +
|pdp || int2||YES|| PdopMsk (3-250)
 +
|-
 +
|pac||int2||YES|| PaccMsk (10-100)
 +
|-
 +
|tdp ||int2||YES|| TdopMsk (3-250)
 +
|-
 +
|tac ||int2||YES|| TaccMsk (30-300)
 +
|-
 +
|ga || int2||YES|| Additional Fixes GpsIval mode (3-25) before interval fix is logged
 +
|-
 +
|aol || int2||YES||AccMemOffLev (100 - 950) units 0.1%; if Memfull above  this level all Accelero activity will be overruled and switched off untill MemFull < level - 5
 +
|-
 +
|eventnum || text||YES|| 12 eventnumbers (0-0xff) only printed if not zero, therfore converted to text
 +
|}
 +
 +
=== ''gps.''ee_trackingfile_limited ===
 +
 +
Each row shows the status of the last attempt to parse (process) the raw data of a file for a given tracker, for each tracker that your projects allow you to see.
 +
 +
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 +
|-
 +
|id||int8||NO||Unique identifier of the row
 +
|-
 +
|date_reported||date||NO||Date that the file arrived
 +
|-
 +
|filename||varchar||NO||Name of the file
 +
|-
 +
|last_modified_date||timestamp||NO||Date that the file was last modified on
 +
|-
 +
|device_info_serial||int4||YES||Serial number of the tracker this file comes from
 +
|-
 +
|filesize||int8||NO||Size of the file in KB
 +
|-
 +
|parsed_date||timestamp||YES||Date and time that the file was last attempted to be parsed on
 +
|-
 +
|version||int4||NO||Optimistic locking field. Ignore it as a user.
 +
|-
 +
|last_found_date||timestamp||YES||Last time that a process that runs to look for new files saw the file
 +
|-
 +
|n_lines||int4||YES||Count of lines found in the file last time it was processed
 +
|-
 +
|n_data_lines||int4||YES||Count of actual data lines, out of the total lines
 +
|-
 +
|n_tracking_records||int4||YES||Count of GPS fixes found during the parsing process
 +
|-
 +
|n_communication_records||int4||YES||Count of communication records found during the parsing process
 +
|-
 +
|n_energy_records||int4||YES||Count of energy records found during the parsing process
 +
|-
 +
|n_acceleration_records||int4||YES||Count of acceleration records found during the parsing process
 +
|-
 +
|n_acceleration_start_records||int4||YES||Count of acceleration start records found during the parsing process
 +
|-
 +
|n_errors||int4||YES||Count of errors encountered during the parsing process
 +
|-
 +
|n_settings_records||int4||YES||Count of settings records found during the parsing process
 +
|-
 +
|filepath||text||NO||Directory path of the file on the server
 +
|}
 +
 +
=== ''gps.''ee_warning_limited ===
 +
 +
Since firmware version 1.0.2.7 this table was added for events that do not occur very often (C10, C20) or errors that occur during parsing.
 +
 +
For example all CRC failures can be selected with :
 +
 +
  select * from gps.ee_warning_limited
 +
  where warning = 'CRC8 failure'
 +
  order by device_info_serial;
 +
 +
{| class="wikitable"
 +
! COLUMN_NAME
 +
! TYPE_NAME
 +
! IS_NULLABLE
 +
! DESCRIPTION
 +
|-
 +
|device_info_serial||int4||NO||Serial number of the tracker
 +
|-
 +
|filename||text||NO||File name
 +
|-
 +
|linenumber||integer||YES||line number in file
 +
|-
 +
|warning||text||YES||The warning message
 +
|-
 +
|line||text||YES||The actual line contents in the file
 +
|}
 +
 +
== Sharings ==
 +
 +
Projects can now share trackers with other projects during periods of time. This means that people in the receiving projects can access information that these shared trackers provide during those time periods.
 +
 +
The following views allow you to see this shared information:
 +
 +
* Tracking data
 +
** ''gps.''ee_shared_tracking_speed_limited: tracking information (gps, speed), during the shared period, for trackers shared
 +
** ''gps.''ee_shared_acc_start_limited: acceleration start information, during the shared period, for trackers shared
 +
** ''gps.''ee_shared_acceleration_limited: acceleration information for trackers shared
 +
* Metadata
 +
** ''gps.''ee_shared_tracker_limited: information about each shared tracker
 +
** ''gps.''ee_shared_individual_limited: information about birds with shared trackers
 +
** ''gps.''ee_shared_track_session_limited: information about track sessions which involve shared trackers
 +
* Relation to projects
 +
** ''gps.''ee_shared_tracker_sharing_limited: view information for each sharing that your projects allow you to see
 +
 +
== Accelerometer calibration ==
 +
 +
The accelerometer measures the acceleration in 3D (20 times per second). Acceleration is caused by gravity (static component) and by a change in speed (dynamic component).
 +
When the logger is static in horizontal position (solar cells up), gravity (green arrow) shows a  heave (vertical acceleration, z) of +1 g (1 g = 9.81 m.s<sup>-2</sup>) while surge and heave are both 0. When the front of the logger is vertically pointing down (forward acceleration, x), surge is +1g and when it is rolled to the right (sidewards acceleration, y) sway is +1g. Due to gravity, the pitch of a logger is thus translated into the values of heave, surge and sway (or z, x, y).
 +
 +
However, the values are measured in mV instead of g. Before delivering the GPS-loggers, they have been calibrated at UvA. The acceleration (A) expressed in g is calculated from the measured value (M) and the calibration factors offset (''O'') and sensitivity (''S'').
 +
 +
Thus acceleration for heave (z), surge (x) and sway (y) is calculated as:
 +
A<sub>z</sub> = (M<sub>z</sub>-O<sub>z</sub>)/S<sub>z</sub> ;      A<sub>x</sub> = (M<sub>x</sub>-O<sub>x</sub>)/S<sub>x</sub>;      A<sub>y</sub> = (M<sub>y</sub>-O<sub>y</sub>)/S<sub>y</sub> ;
 +
 +
The values of ''O'' (in general ranging from 0-60, sometimes even 300) and ''S'' (ranging from 1300-1450) are available in the device table of the data base. If not available, use the factory defaults ''O'' = 0 and ''S'' = 1365.
 +
 +
{|
 +
| [[File:accelero1.png|300px]]||[[File:accelero2.png|300px]]|
 +
|}
 +
 +
In the figure above heave (z) is shown in green, surge(x) in red and sway (y) in blue, all values are expressed in g (on the y-axis).
 +
 +
== Data processing and filtering ==
 +
The raw log files are processed before being entered into the database. Each firmware version has a dedicated parser for the task. In certain cases data that is found in the log files is not stored in the database.  This can occur for two reasons:
 +
 +
1. The data did not pass the CRC8 checksum (cyclic redundancy check). This is a standard procedure designed to detect accidental changes to raw computer data.
 +
 +
2. If the data has an invalid signature. For example, if a certain data format (eg numeric) or number of values (eg 4 digits) is expected but not returned, this record is not stored in the database.
 +
 +
When a line is deleted, the log file name, line number and the type of error is stored in the table gps.uva_warning102.
 +
 +
== Information about GPS receivers ==
 +
The first series of tags used the UBlox LEAS4S chip (~up to tag 541), newer tags use UBlox LEAS6S. The series developed in 2012 will use the Max6G receivers. From 2012 receiver versions will be stored in the database.
 +
The UBX protocol is used for programming firmware, packaging and processing data.
 +
For more information about the receivers themselves see the ublox documentation.
 +
Ublox 4 Protocol specification [http://www.u-blox.com/images/downloads/Product_Docs/ANTARIS_Protocol_Specification%28GPS.G3-X-03002%29.zip]
 +
Ublox 6 Protocol specification [http://www.u-blox.com/images/downloads/Product_Docs/u-blox6_ReceiverDescriptionProtocolSpec_%28GPS.G6-SW-10018%29.pdf]

Latest revision as of 09:35, 22 January 2021

UvA-BiTS Tracking Data

Note: These views will show no rows for trackers not yet attached to birds.

gps.ee_tracking_speed_limited

Shows the tracking data that your projects allow you to see.

Each row represents a GPS fix as it is interpreted from the data that comes via the GPS tracker, plus it adds speeds. See Reference systems for GPS coordinates and altitudes for further details.

It replaces old views gps.uva_tracking_limited, gps.uva_tracking_speed_3d_limited and gps.uva_tracking_speed. This table structure has not changed with respect to our previous database.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
device_info_serial int4 NO Serial number of the tracker that reported the fix. Part of the unique identifier of the GPS fix.
date_time timestamp NO Time when the fix was taken. UTC (i.e.: no timezone). Part of the unique identifier of the GPS fix.
latitude float8 YES Latitude of the GPS fix, decimal degrees (WGS84 datum)
longitude float8 YES Longitude of the GPS fix, decimal degrees (WGS84 datum)
altitude int4 YES Altitude above sea level measured by GPS tag in meters (WGS84 datum)
pressure int4 YES Pressure measured by GPS tag sensor in Pascals
temperature float8 YES Temperature measured by GPS tag sensor in Celsius
h_accuracy float8 YES Horizontal accuracy (meters)
v_accuracy float8 YES vertical accuracy (meters)
x_speed float8 YES meters/second
y_speed float8 YES meters/second
z_speed float8 YES meters/second
gps_fixtime float8 YES GPS fix time (also known as Time to Fix) in seconds
userflag int4 YES Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
satellites_used int2 YES Number of satellites used for fix the GPS measurement
positiondop float8 YES Positional DOP (Dilution of Precision), unitless
speed_accuracy float8 YES
location geometry YES postgreSQL geometry; to allow GIS operations
vnorth float8 YES - vx * sin(lat) * cos(long) - vy * sin(lat) * sin (long) + vz * cos(lat), m/s
veast float8 YES - vx * sin(lon) + vy * cos(lon), m/s
vdown float8 YES - vx * cos(lat) * cos(lon) - vy * cos(lat) * sin(lon) - vz * sin(lat), m/s
speed float8 YES - vx * sin(lon) + vy * cos(lon)
speed_3d float8 YES sqrt(d.x_speed^2 + d.y_speed^2 + d.z_speed^2), m/s
speed_2d float8 YES Speed on 2D, m/s
direction numeric YES Heading in degrees; 0 is north, 90 is east
altitude_agl float8 YES Reported GPS altitude MINUS terrain elevation on the coordinate based on Digital Elevation Model data used in the e-ecology database (for further information see: Elevation

gps.ee_acceleration_limited

Acceleration is measured in blocks of 1-200 measurements (records). The date_time mentioned in this block is the date and time of the first acceleration measurement. Sequential measurements within a block are numbered with an index 1... (max 200). Every row represents one measurement in 3 directions (X,Y,Z). The order is important within each block, so you should always sort the acceleration measurements by block and index. If you need the exact time for each row, you can calculate it from the index, the sample frequency (which is almost always 20 Hz) and the date_time of the first row in the block.

The date_time of the first block is the time of the GPS measurement preceding the accelerometer measurement. If there is no valid GPS measurement, this time is calculated from the last GPS measurement and the time of the internal clock of the tracker.

Therefore, to know all valid dates and times that mark a valid block for a tracker, you need to consider all of the values in column date_time in gps.ee_tracking_speed_limited for the given tracker, column date_time on gps.ee_acc_start_limited, and all the values on column date_time for that tracker.


This table structure has not changed with respect to our previous database.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
device_info_serial int4 NO Serial number of the tracker that reported the fix. Part of the unique identifier of the GPS fix.
date_time timestamp NO Time of the acceleration block that this measurement belongs to (time in UTC, no time zone).
index int2 NO Sorting order of the measurement within the acceleration block (each block starts at 0).
x_acceleration int2 YES Acceleration measured on the x (or surge) axis (range between -2666 and 2666).
y_acceleration int2 YES Acceleration measured on the y (or sway) axis (range between -2666 and 2666).
z_acceleration int2 YES Acceleration measured on the z (or heave) axis (range between -2666 and 2666).

gps.ee_acc_start_limited

Each row represents the start of a block of acceleration measurements when a GPS fix is not available at that acceleration block's time.

If there is no valid GPS measurement, this time is calculated from the last GPS measurement and the time of the internal clock of the tracker.

To know all valid dates and times that mark a valid block for a tracker, you need to consider all of the values in column date_time on gps.ee_tracking_speed_limited for the given tracker, column date_time on gps.ee_acc_start_limited, and all the values on column date_time for that tracker. And the order is important within each block, so you should sort the acceleration measurements by block and index.


Added in firmware 1.0.2.7.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
device_info_serial int4 NO Serial number of the tracker that reported the acceleration measurement.
date_time timestamp NO Time to mark the start of the acceleration block.
line_counter int4 NO Number of lines associated with this set of accelerations.
timesynced int2 YES The source of the time that marks the acceleration block (0=not synced, 1=user set, 2=Gps Synced).
ascii int4 YES Acceleration measurement interval.
accsn int4 YES Number of samples (0-65536).
f int2 YES Acceleration sampling frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz).

Bird data

gps.ee_individual_limited

Each row shows bird's details for all birds that your projects allow you to see.

This view replaces the old gps.uva_individual_limited.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
ring_number varchar NO Ring number.
species_latin_name varchar NO Latin name of the species of the bird.
colour_ring varchar YES String for ring colour.
sex varchar NO Sex (M=male, F=female, X=unknown/unspecified)
mass numeric(5,0) YES Mass (grams)
start_date timestamp NO Date when the bird was first tracked with an UvA-BiTS logger.
end_date timestamp NO Date at which the bird is no longer relevant for the tracking project.
remarks text YES Text for user remarks
individual_id int8 NO Bird unique id. Included just to enable joins.

gps.ee_species_limited

Each row represents a species that you can assign to your birds to include them in your project. No bird in the system can exist without being linked to one of the species from this list. If you are tagging a bird whose species does not exist in the list, please contact us.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
abbr varchar NO Unique abbreviation for this species.
english_name varchar YES Name of the species in English.
latin_name varchar NO Name of the species in Latin. It is always filled in.
species_id int8 NO Unique id. Added to allow joins.

gps.ee_nest_limited

This view shows nests you have access to. A nest is always for a bird with a tracker (so a track session must exist for that bird), and must contain a short name (a reference name for you to use) and a latitude and longitude pair.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
reference_name varchar NO A string with a useful reference for you. We recommend its being unique for each nest in a project, but the system does NOT enforce it.
latitude numeric YES Latitude of the coordinates where the nest was found, decimal degrees (WGS84 datum)
longitude numeric YES Longitude of the coordinates where the nest was found, decimal degrees (WGS84 datum)
start_date_time timestamp NO When the nest was spotted.
end_date_time timestamp NO When the nest ceases to be relevant.
found_by_whom varchar YES Place to credit people involved in finding the nest.
remarks text YES Free text.
location bytearray YES Calculated Geometry of the coordinates where the nest was found.
nest_id int8 NO Unique id. Added to allow joins to nest_inhabitant_limited.

gps.ee_nest_inhabitant_limited

This view shows who lives on which nest, for birds you have access to.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
reference_name varchar NO A string belonging to the nest that you can use to identify that nest. Because it is NOT enforced to be unique, DO NOT USE THIS FIELD FOR JOINING.
ring_number varchar NO The ring number of a bird on a nest.
device_info_serial int8 NO Following the link from a bird to her track sessions, and considering the nest's start and end time, the bird's trackers within that period are resolved so that they can be shown here. This is the device_info_serial of a tracker.
key_name varchar NO Following the link from a bird to her track sessions, and considering the nest's start and end time, the track sessions' projects within that period are resolved so that they can be shown here. This is the key_name of a project.
nest_id int8 NO Reference to the nest id. Added to allow joins.
individual_id int8 NO Reference to the individual id. Added to allow joins.
track_session_id int8 NO Reference to a resolved track session id. Added to allow joins.

Tracker and project data

gps.ee_project_limited

Each row shows the details of each project, for the projects you are allowed to see.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
key_name varchar NO Unique key name, with the form <species>_<place>
station_name varchar NO Where the receiving station is
start_date timestamp NO Project start date
end_date timestamp NO Date at which the project is not relevant any more
description text YES String for free description. Updatable by the user.
project_id int8 NO Project unique id. Included just to enable joins.
parent_id int8 YES Reference to the parent project in the hierarchy.

gps.ee_tracker_limited

Each row shows tracker details for all trackers that your projects allow you to see.

This view replaces the old gps.uva_device_limited.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
device_info_serial int8 NO Serial number of the tracker
firmware_version varchar YES String for firmware version
mass numeric(4,2) YES Mass (in grams)
start_date timestamp NO Date before which the device is not relevant
end_date timestamp NO Date at which the device is not relevant any more
x_o numeric(30,6) YES Accelerometer calibration: offset on the x (or surge) axis
x_s numeric(30,6) YES Accelerometer calibration: sensitivity on the x (or surge) axis
y_o numeric(30,6) YES Accelerometer calibration: offset on the y (or sway) axis
y_s numeric(30,6) YES Accelerometer calibration: sensitivity on the y (or sway) axis
z_o numeric(30,6) YES Accelerometer calibration: offset on the z (or heave) axis
z_s numeric(30,6) YES Accelerometer calibration: sensitivity on the y (or heave) axis
tracker_id int8 NO Tracker unique id. Included just to enable joins.

gps.ee_track_session_limited

A track session links a tracker and a bird for a given period of time. Therefore, it is mandatory for projects to supply this piece of metadata before they can see any tracking data related to a tracker they own.

In order to create a track session, the project must create first an entry in the system for the bird that they are attaching the tracker to, and this is the way for the UvA-BiTS community to make sense of the tracking data that we are receiving. This is why providing the metadata is so important.

By editing the start and end dates, administrators of the project that this track session belongs to, can delimit (and therefore, establish) when the coupling makes sense (or is to be understood as useful).

Whenever a tracker is attached to a bird, a track session must be started (start_date) to indicate that the tracking data that is received is already live data. If the tracker is separated from the bird, the tracking data ceases to make sense, so the track session's end date should be set to denote it has ended. New track sessions can be started if the tracker is re-attached to a bird (possibly the same as before, or maybe a different one) afterwards.

Each row shows track session's information. There exists a row for each track session that your projects allow you to see.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
key_name varchar NO The project that the track session belongs to.
device_info_serial int8 NO Serial number of the tracker this track session involves.
ring_number varchar NO Ring number of the bird that this track session involves.
start_date timestamp NO Date before which the track session is not relevant
end_date timestamp NO Date at which the track session is not relevant any more
remarks text YES Free text for comments
start_latitude numeric(11,8) NO Latitude where the bird was tagged, decimal degrees (WGS84 datum)
start_longitude numeric(11,8) NO Longitude where the bird was tagged, decimal degrees (WGS84 datum)
project_id int8 NO Reference to the project this track session belongs to. Included just to enable joins.
tracker_id int8 NO Reference to the tracker this track session involves. Included just to enable joins.
individual_id int8 NO Reference to the individual this track session involves. Included just to enable joins.
track_session_id int8 NO Unique identifier of the track session. Included just to enable joins.

Relation to projects

gps.ee_user_limited

Each row shows a colleague in a project that you belong to, indicating her role and status there.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
key_name varchar NO Project name
email varchar NO e-mail address
login_name varchar NO Login name
name varchar NO (first) name
surname varchar NO Surname
role varchar NO Role in the project
status varchar NO Status of the membership
user_id int8 NO Unique identifier of the user. Included to enable joins.

gps.ee_tracker_ownership_limited

Each row that you see expresses that a tracker belongs to one of the projects you are involved with.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
key_name varchar NO The project that the tracker belongs to.
device_info_serial int8 NO Serial number of the tracker.
project_id int8 NO Reference to the project. Included just to enable joins.
tracker_id int8 NO Reference to the tracker. Included just to enable joins.

gps.ee_individual_ownership_limited

Each row that you see expresses that a bird belongs to one of the projects you are involved with.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
key_name varchar NO The project that the individual belongs to.
ring_number varchar NO Ring number of the bird.
project_id int8 NO Reference to the project. Included just to enable joins.
individual_id int8 NO Reference to the individual. Included just to enable joins.

Engineering data

Note: These views will show no rows for trackers not yet attached to birds.

gps.ee_comm_limited

Each row indicates status and result of communications with ground stations for each tracker that your project allows you to see.

Only for newer firmware (v.1.0.1.4 and higher).

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
device_info_serial int4 NO Serial number of the tracker.
date_time timestamp NO timestamp of start communication
date_time_end timestamp YES timestamp of end communication
date_time_utc timestamp YES date_time in utc (if available)
date_time_end_utc timestamp YES date_time_end in utc (if available)
action text YES JOINED / RESCHEDULE  ??
bytes_communicated int4 YES Number of bytes communicated (total of all bytes per DATA line)
status text YES READY, TIMEOUT, LOWPOWER, MISSING
network_join_time int4 YES milliseconds
number_identical_lines int4 YES Number identical lines in data block. Gives indication
non_ascii_char int4 YES Number of non ascii characters (indicated by question-mark ?)
sts_time timestamp YES derived from S0 hh,mi
timesynced int2 YES TimeSynced (0=not sy:wnced, 1=user set, 2=Gps Synced)
devicemode int2 YES DeviceMode (0-99). This describes whether the device is in a special operational mode. 0 = normal operational mode; 80 hex = Memory save mode (memory is almost full and all activity is drastically reduced so the circular memory probably won't be overwritten or, if it does, it will be kept to a minimum)
vbat float8 YES Battery voltage in V (0-~4.200V)
logb int4 YES logged bytes, waiting for download (0-4Mbyte)
logp int2 YES Logged percentage (units 0.1%) of memory waiting to be downloaded (0-1000)
gpsii int4 YES GpsIval, current (0-65535)
comii int4 YES ComIval, current (0-65535)
accii int4 YES AcceleroIval, current (0-65535)
swddd int4 YES User switches, current (0-65535)
last_gps_fix timestamp YES timestamp of last fix
latitude float8 YES Latitude of last fix. Decimal degrees
longitude float8 YES Longitude of last fix. Decimal degrees

gps.ee_energy_limited

Only for newer firmware.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
device_info_serial int4 NO Serial number of the tracker
date_time timestamp NO Date and time of the reported data
timesynced int2 YES TimeSynced (0=not synced, 1=user set, 2=Gps synced)
devicemode int2 YES (0-99)
vsll float8 YES Solar Voltage during charging in mV (0-~2500)
vbat float8 YES Battery Voltage in V (0-~4.200)
ssw int2 YES Solar Cell Switches ... (0-255) fw1022 and up
YES extended fields if special user switch is on
vsoo float8 YES Solar voltage open in V (0-~2.500)
vdrf float8 YES reference voltage Dac in V (0-~2.435)
vchr float8 YES charge current voltage
g int2 YES Gps battery threshold OK
c int2 YES Com battery threshold OK
m int2 YES Charge Mode (0,1,2 Com battery threshold OK
pressure int4 YES in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa)
temperature float8 YES in celcius degrees

gps.ee_settings_limited

Settings are either from C0-C1-C9 lines or parsed from uplink communication lines. If C0-C1 is parsed its values are compared with the previous value and if the settings are the same, then no new records need to be added. If uplink settings are parsed, then wait until the last setting has been seen (DL command or EOF); then store all the new settings in one record. Only for newer firmware.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
device_info_serial int4 NO
date_time timestamp NO Derived timestamp from Hh:mi fields in C0 combined with computer timestamp in JOINED line
timesynced int2 YES
timezone_time int2 YES
swddd int4 YES SpecialUserSwitches
gpsnavmode int2 YES GpsNavMode 0-8. This is the Kalman filter used for GPS navigation mode often called the "Dynamic platform model". The following codes are provided by the GPS chip manufacturer for the UBlox GPS chip LEAS4S: 1 = stationary, 2 = pedestrian, 3 = automotive, 4 = sea, 5 = airborne 1G, 6 = airborne 2G, 7 = airborne 3G. The UBlox GPS chip LEAS6S uses the following codes: 0 = portable, 2 = stationary, 3 = pedestrian, 4 = automotive, 5 = sea, 6 = airborne with < 1g acceleration, 7 = airborne with < 2g acceleration, 8 = airborne with < 4g acceleration
gpsreceivermode int2 YES GpsReceiverNode 0-3
gpsfixtime int2 YES GpsFixTime 0-255
gps_ivaln_on_h int2 YES hours Gps IvalN on
gps_ivaln_on_m int2 YES minutes Gps IvalN on
gps_ivaln int4 YES GpsIvalN (0-65530)
gps_ivale_on_h int2 YES hours Gps IvalE on
gps_ivale_on_m int2 YES minutes GpS IvalE on
gps_ivale int4 YES GpsIvalE
com_ivaln_on_h int2 YES hours Com IvalN on
com_ivaln_on_m int2 YES minutes Com IvalN on
com_ivaln int4 YES ComIvalN (0-65530)
com_ivale_on_h int2 YES hours Com IvalE on
com_ivale_on_m int2 YES minutes Com IvalE on
com_ivale int4 YES ComIvalE
accii int4 YES Accelero measurement interval
accbn int4 YES Accelero number of blocks
accsn int4 YES Accelero number of samples (0-65536)
f int2 YES Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz)
pdp int2 YES PdopMsk (3-250)
pac int2 YES PaccMsk (10-100)
tdp int2 YES TdopMsk (3-250)
tac int2 YES TaccMsk (30-300)
ga int2 YES Additional Fixes GpsIval mode (3-25) before interval fix is logged
aol int2 YES AccMemOffLev (100 - 950) units 0.1%; if Memfull above this level all Accelero activity will be overruled and switched off untill MemFull < level - 5
eventnum text YES 12 eventnumbers (0-0xff) only printed if not zero, therfore converted to text

gps.ee_trackingfile_limited

Each row shows the status of the last attempt to parse (process) the raw data of a file for a given tracker, for each tracker that your projects allow you to see.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
id int8 NO Unique identifier of the row
date_reported date NO Date that the file arrived
filename varchar NO Name of the file
last_modified_date timestamp NO Date that the file was last modified on
device_info_serial int4 YES Serial number of the tracker this file comes from
filesize int8 NO Size of the file in KB
parsed_date timestamp YES Date and time that the file was last attempted to be parsed on
version int4 NO Optimistic locking field. Ignore it as a user.
last_found_date timestamp YES Last time that a process that runs to look for new files saw the file
n_lines int4 YES Count of lines found in the file last time it was processed
n_data_lines int4 YES Count of actual data lines, out of the total lines
n_tracking_records int4 YES Count of GPS fixes found during the parsing process
n_communication_records int4 YES Count of communication records found during the parsing process
n_energy_records int4 YES Count of energy records found during the parsing process
n_acceleration_records int4 YES Count of acceleration records found during the parsing process
n_acceleration_start_records int4 YES Count of acceleration start records found during the parsing process
n_errors int4 YES Count of errors encountered during the parsing process
n_settings_records int4 YES Count of settings records found during the parsing process
filepath text NO Directory path of the file on the server

gps.ee_warning_limited

Since firmware version 1.0.2.7 this table was added for events that do not occur very often (C10, C20) or errors that occur during parsing.

For example all CRC failures can be selected with : 

 select * from gps.ee_warning_limited
 where warning = 'CRC8 failure'
 order by device_info_serial;
COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
device_info_serial int4 NO Serial number of the tracker
filename text NO File name
linenumber integer YES line number in file
warning text YES The warning message
line text YES The actual line contents in the file

Sharings

Projects can now share trackers with other projects during periods of time. This means that people in the receiving projects can access information that these shared trackers provide during those time periods.

The following views allow you to see this shared information:

  • Tracking data
    • gps.ee_shared_tracking_speed_limited: tracking information (gps, speed), during the shared period, for trackers shared
    • gps.ee_shared_acc_start_limited: acceleration start information, during the shared period, for trackers shared
    • gps.ee_shared_acceleration_limited: acceleration information for trackers shared
  • Metadata
    • gps.ee_shared_tracker_limited: information about each shared tracker
    • gps.ee_shared_individual_limited: information about birds with shared trackers
    • gps.ee_shared_track_session_limited: information about track sessions which involve shared trackers
  • Relation to projects
    • gps.ee_shared_tracker_sharing_limited: view information for each sharing that your projects allow you to see

Accelerometer calibration

The accelerometer measures the acceleration in 3D (20 times per second). Acceleration is caused by gravity (static component) and by a change in speed (dynamic component). When the logger is static in horizontal position (solar cells up), gravity (green arrow) shows a heave (vertical acceleration, z) of +1 g (1 g = 9.81 m.s-2) while surge and heave are both 0. When the front of the logger is vertically pointing down (forward acceleration, x), surge is +1g and when it is rolled to the right (sidewards acceleration, y) sway is +1g. Due to gravity, the pitch of a logger is thus translated into the values of heave, surge and sway (or z, x, y).

However, the values are measured in mV instead of g. Before delivering the GPS-loggers, they have been calibrated at UvA. The acceleration (A) expressed in g is calculated from the measured value (M) and the calibration factors offset (O) and sensitivity (S).

Thus acceleration for heave (z), surge (x) and sway (y) is calculated as: Az = (Mz-Oz)/Sz ; Ax = (Mx-Ox)/Sx; Ay = (My-Oy)/Sy ;

The values of O (in general ranging from 0-60, sometimes even 300) and S (ranging from 1300-1450) are available in the device table of the data base. If not available, use the factory defaults O = 0 and S = 1365.

Accelero1.png Accelero2.png|

In the figure above heave (z) is shown in green, surge(x) in red and sway (y) in blue, all values are expressed in g (on the y-axis).

Data processing and filtering

The raw log files are processed before being entered into the database. Each firmware version has a dedicated parser for the task. In certain cases data that is found in the log files is not stored in the database. This can occur for two reasons:

1. The data did not pass the CRC8 checksum (cyclic redundancy check). This is a standard procedure designed to detect accidental changes to raw computer data.

2. If the data has an invalid signature. For example, if a certain data format (eg numeric) or number of values (eg 4 digits) is expected but not returned, this record is not stored in the database.

When a line is deleted, the log file name, line number and the type of error is stored in the table gps.uva_warning102.

Information about GPS receivers

The first series of tags used the UBlox LEAS4S chip (~up to tag 541), newer tags use UBlox LEAS6S. The series developed in 2012 will use the Max6G receivers. From 2012 receiver versions will be stored in the database. The UBX protocol is used for programming firmware, packaging and processing data. For more information about the receivers themselves see the ublox documentation. Ublox 4 Protocol specification [1] Ublox 6 Protocol specification [2]

Retrieved from "https://wiki.e-ecology.nl/index.php?title=UvA-BiTS_Tracking_Data&oldid=1437"