Difference between revisions of "UvAGPS"

From ecology
Jump to: navigation, search
m (→‎UVA_ACCELERATION101 (Table) or UVA_ACCELERATION_LIMITED (View))
(→‎UvA-BiTS Data Model)
 
(19 intermediate revisions by one other user not shown)
Line 3: Line 3:
 
All tables, views and functions related to UvA-BiTS are in the GPS schema in the flysafe database and begin with the '''prefix "uva_"'''.  The tables/views that do not begin with "uva_" are related to a different project.
 
All tables, views and functions related to UvA-BiTS are in the GPS schema in the flysafe database and begin with the '''prefix "uva_"'''.  The tables/views that do not begin with "uva_" are related to a different project.
  
−
Not everybody has access to all data.  Most users will only be granted access to tables/views with the '''postfix "_limited"''' that show data collected with devices specific to their project(s).
+
Not everybody has access to all data.  Most users will only be granted access to views with the '''suffix "_limited"''' that show data collected with devices specific to their project(s).
  
−
'''The difference between a Table and a View:''' The difference is actually quite small. The same data can be queried and viewed in the same way in both Tables and Views. However, the data stored in a table can also be updated, deleted and changed by people with administrative privileges. The purpose of a view is to allow users who only want to view and/or query the data to do so without any possibility of altering the data. For this reason, we recommend that most users access the data through the Views.
+
'''The difference between a Table and a View:''' The difference is actually quite small from the user perspective. The same data can be queried and viewed in the same way in both tables and views. However, the data stored in a table can also be updated, deleted and changed by people with administrative privileges. The purpose of a view is to allow users who only want to view and/or query the data to do so without any possibility of altering the data. For this reason, we recommend that most users access the data through the Views. In addition, views often contain valuable additional data calculated from the raw tracking data.
  
 
'''Where to find the basic tracking data:'''  Depending on the version of UvA-BiTS firmware installed in the GPS device, different database tables/views are used to store tracking data:  
 
'''Where to find the basic tracking data:'''  Depending on the version of UvA-BiTS firmware installed in the GPS device, different database tables/views are used to store tracking data:  
Line 12: Line 12:
 
* UVA_TRACKING_DATA101 table: Introduced to the database to store the data collected with the newest firmware (versions 1.0.1.4 till 1.0.2.4) because the new version is able to collect so much more information.  
 
* UVA_TRACKING_DATA101 table: Introduced to the database to store the data collected with the newest firmware (versions 1.0.1.4 till 1.0.2.4) because the new version is able to collect so much more information.  
  
−
The tables/views in the UvA-BiTS database are described individually in detail below. Each table/view has been placed into one of three sections depending on the type of data they contain:
+
The tables/views in the UvA-BiTS database are described in detail in the sections below. Each table/view has been placed into one of three sections depending on the type of data they contain:
 
* ''Tracking Data:'' Primary tracking data that is of most interest to most users (i.e. device numbers, lat/long, x/y/z acceleration, altitude, etc.).
 
* ''Tracking Data:'' Primary tracking data that is of most interest to most users (i.e. device numbers, lat/long, x/y/z acceleration, altitude, etc.).
 
* ''Bird Data:'' Data about the birds on which the devices are mounted (species, sex, etc.).  
 
* ''Bird Data:'' Data about the birds on which the devices are mounted (species, sex, etc.).  
Line 22: Line 22:
 
=== UVA_TRACK_SESSION or UVA_TRACK_SESSION_LIMITED (Table and View)===  
 
=== UVA_TRACK_SESSION or UVA_TRACK_SESSION_LIMITED (Table and View)===  
  
−
This is essential metadata about GPS tag deployment that is provided by the project manager when the tag is first mounted on a bird. This table includes information about each measurement session and enables inclusion of data from new species and also from the test phase of different tags. To avoid analyzing data that was collected during testing, you should always select data that coincides with the start_data of a particular track session.
+
This is essential metadata about GPS tag deployment that is provided by the project manager when the tag is first mounted on a bird. This table includes information about each measurement session and enables inclusion of data from new species and also from the test phase of different tags. To avoid analyzing data that was collected during testing, you should always include this table/view in your query as a way to select data that was measured after the start_date of a particular track session.
  
 
{|border=1
 
{|border=1
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|-
 
|-
−
|device_info_serial||int4 ||FK||NOT NULL||Serial number of GPS tag
+
|device_info_serial||integer||FK||NOT NULL||Serial number of GPS tag
 
|-
 
|-
−
|ring_number||text ||FK||||Ring number of individual bird. This field is what links this table to the UVA_INDIVIDUAL table. Tests do not have a ring_number.
+
|ring_number||text||FK||NOT NULL||Ring number of individual bird. This field is what links this table to the UVA_INDIVIDUAL table. Tests do not have a ring_number.
 
|-
 
|-
 
|start_date ||timestamp||||||Timestamp without time zone (UTC) when tracking begins in yyyy-mm-dd hh:mm:ss.
 
|start_date ||timestamp||||||Timestamp without time zone (UTC) when tracking begins in yyyy-mm-dd hh:mm:ss.
Line 35: Line 35:
 
|end_date||timestamp||||||Timestamp without time zone (UTC) when tracking session ends, if known, in yyyy-mm-dd hh:mm:ss. This field will be blank if the project is still ongoing.
 
|end_date||timestamp||||||Timestamp without time zone (UTC) when tracking session ends, if known, in yyyy-mm-dd hh:mm:ss. This field will be blank if the project is still ongoing.
 
|-
 
|-
−
|start_latitude ||float8||||NOT NULL||Latitude where tracking begins, unit = dec degress
+
|start_latitude ||double precision||||NOT NULL||Latitude where tracking begins, unit = dec degrees
 
|-
 
|-
−
|start_longitude ||float8||||NOT NULL||Longitude where tracking begins, unit = dec degrees
+
|start_longitude ||double precision||||NOT NULL||Longitude where tracking begins, unit = dec degrees
 
|-
 
|-
 
|project_leader||text|||||| Project leader and point of contact for questions about data
 
|project_leader||text|||||| Project leader and point of contact for questions about data
 
|-
 
|-
 
|remarks||text|||||| Optional field for project manager to add comments.
 
|remarks||text|||||| Optional field for project manager to add comments.
−
|-
 
−
|location ||geometry||||||postgreSQL geometry
 
 
|}
 
|}
  
Line 53: Line 51:
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|-
 
|-
−
|device_info_serial||int4||PK||NOT NULL ||Serial number of GPS tag
+
|device_info_serial||integer||PK||NOT NULL ||Serial number of GPS tag
 
|-
 
|-
 
|date_time||timestamp||PK||NOT NULL|| Timestamp of GPS measurement without time zone (UTC)
 
|date_time||timestamp||PK||NOT NULL|| Timestamp of GPS measurement without time zone (UTC)
 
|-
 
|-
−
|latitude||float||||||Latitude measured by GPS tag for this record, unit = dec degress
+
|latitude||float||||||Latitude measured by GPS tag for this record, unit = dec degrees
 
|-
 
|-
−
|longitude||float||||||Longitude measured by GPS tag for this record, unit = dec degress
+
|longitude||float||||||Longitude measured by GPS tag for this record, unit = dec degrees
 
|-
 
|-
 
|altitude||float|||||| Altitude above sea level measured by GPS tag in meters
 
|altitude||float|||||| Altitude above sea level measured by GPS tag in meters
 
|-
 
|-
−
|pressure||integer|||||| Pressure measured by GPS tag in Pascals
+
|pressure||integer|||||| Pressure measured by GPS tag sensor in Pascals
 
|-
 
|-
−
|temperature||float|||||| Temperature measured by GPS tag in Celsius
+
|temperature||float|||||| Temperature measured by GPS tag sensor in Celsius
 
|-
 
|-
−
|h_accuracy||float|||||| Horizontal accuracy in units of 0.1 meter
+
|h_accuracy||float|||||| Horizontal accuracy (meters)
 
|-
 
|-
−
|v_accuracy||float|||||| Vertical accuracy in units of 0.1 meter
+
|v_accuracy||float|||||| Vertical accuracy (meters)
 
|-
 
|-
−
|x_speed||float||||||meters/second (calculated)
+
|x_speed||float||||||meters/second  
 
|-
 
|-
−
|y_speed||float||||||meters/second (calculated)
+
|y_speed||float||||||meters/second  
 
|-
 
|-
−
|z_speed||float||||||meters/second (calculated)
+
|z_speed||float||||||meters/second  
 
|-
 
|-
−
|gps_fixtime||float|||||| GPS fix time (also known as Time to Fix) in 0.1 second units
+
|gps_fixtime||float|||||| GPS fix time (also known as Time to Fix) in seconds
 
|-
 
|-
−
|userflag||integer||||||Flagged as unacceptable by user if not 0
+
|userflag||integer||||||Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
 
|-
 
|-
−
|satellites_used||smallint|||||| How many satellites were used for the measurement
+
|satellites_used||smallint|||||| Number of satellites used for fix the GPS measurement
 
|-
 
|-
 
|positiondop||float||||||Positional DOP (Dilution of Precision), unitless
 
|positiondop||float||||||Positional DOP (Dilution of Precision), unitless
Line 92: Line 90:
 
=== UVA_ACCELERATION101 (Table) or UVA_ACCELERATION_LIMITED (View) ===  
 
=== UVA_ACCELERATION101 (Table) or UVA_ACCELERATION_LIMITED (View) ===  
  
−
Acceleration entries if accelerometer was turned on. Only for newer firmware. Actual measurements have shown that the range of values for x,y and z are larger then indicated by the manufacturer.  For more information about calibration and interpretation of accelerometer data see [[UvAGPS#Accelerometer_calibration|Accelerometer calibration]]
+
Acceleration entries if accelerometer was turned on. Only for newer firmware. Actual measurements have shown that the range of values for x,y and z are larger then indicated by the manufacturer.  For more information about calibration and interpretation of accelerometer data see [[UvAGPS#Accelerometer_calibration|Accelerometer calibration]].
  
 
{|border=1
 
{|border=1
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|-
 
|-
−
|device_info_serial||int4||PK||NOT NULL ||Serial number of GPS tag
+
|device_info_serial||integer||PK||NOT NULL ||Serial number of GPS tag
 
|-
 
|-
 
|date_time||timestamp||PK||NOT NULL|| Timestamp of measurement without time zone (UTC)
 
|date_time||timestamp||PK||NOT NULL|| Timestamp of measurement without time zone (UTC)
 
|-
 
|-
−
|index ||smallint||PK||NOT NULL|| row index (from 0) of accelerometer measurement series
+
|index ||smallint||PK||NOT NULL|| Row index (from 0) of accelerometer measurement series
 
|-
 
|-
−
|x_acceleration ||smallint||||||acceleration in X-axis (-2666 till 2666)
+
|x_acceleration ||smallint||||||Acceleration in X-axis (-2666 till 2666)
 
|-
 
|-
−
|y_acceleration ||smallint||||||acceleration in Y-axis (-2666 till 2666)
+
|y_acceleration ||smallint||||||Acceleration in Y-axis (-2666 till 2666)
 
|-
 
|-
−
|z_acceleration ||smallint||||||acceleration in Z-axis (-2666 till 2666)
+
|z_acceleration ||smallint||||||Acceleration in Z-axis (-2666 till 2666)
 
|}
 
|}
  
Line 131: Line 129:
 
|direction||float|||||| Heading in degrees; 0 is north, 90 is east
 
|direction||float|||||| Heading in degrees; 0 is north, 90 is east
 
|}
 
|}
−
 
  
 
=== UVA_TRACKING_DATA (Table) ===
 
=== UVA_TRACKING_DATA (Table) ===
  
−
Tracking data from UvA GPS tags for older firmware. All of this data is also brought into the UVA_TRACKING view described above.
+
Tracking data from UvA GPS tags using older firmware. All of this data is also brought into the UVA_TRACKING view as described above.
  
 
{|border=1
 
{|border=1
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|-
 
|-
−
|device_info_serial||int4||PK||NOT NULL ||
+
|device_info_serial||integer||PK||NOT NULL || Serial number of GPS tag
 +
|-
 +
|date_time||timestamp||PK||NOT NULL||Timestamp without time zone in UTC
 +
|-
 +
|latitude||double precision||||||Latitude measured by GPS tag for this record, unit = dec degrees
 +
|-
 +
|longitude||double precision||||||Longitude measured by GPS tag for this record, unit = dec degrees
 +
|-
 +
|h_accuracy||double precision||||||Horizontal accuracy (meters)
 +
|-
 +
|v_accuracy||double precision||||||Vertical accuracy (meters)
 +
|-
 +
|altitude ||double precision||||||Altitude above sea level measured by GPS tag in meters
 
|-
 
|-
−
|date_time||timestamp||PK||NOT NULL||UTC
+
|x_acceleration||double precision||||||x axis acceleration (m/s<sup>2</sup>2) movement to right is + and to left is -
 
|-
 
|-
−
|latitude||float8||||||Decimal degrees
+
|y_acceleration||double precision||||||y axis acceleration (m/s<sup>2</sup>2) forward movement is + and backwards is -
 
|-
 
|-
−
|longitude||float8||||||Decimal degrees
+
|z_acceleration||double precision||||||z axis acceleration (m/s<sup>2</sup>2) upwards movement is + and downwards is -
 
|-
 
|-
−
|h_accuracy||float8||||||Horizontal accuracy (m)
+
|x_speed||double precision||||||Speed in x-axis (m/s)
 
|-
 
|-
−
|v_accuracy||float8||||||Vertical accuracy (m)
+
|y_speed ||double precision||||||Speed in y-axis (m/s)
 
|-
 
|-
−
|altitude ||float8||||||m
+
|z_speed ||double precision||||||Speed in z-axis (m/s)
 
|-
 
|-
−
|x_acceleration||Float8||||||x axis acceleration (m/s<sup>2</sup>2) movement to right is + and to left is -
+
|speed_3d ||double precision||||||meters/second
 
|-
 
|-
−
|y_acceleration||Float8||||||y axis acceleration (m/s<sup>2</sup>2) forward movement is + and backwards is -
+
|heading||double precision||||||
 
|-
 
|-
−
|z_acceleration||Float8||||||z axis acceleration (m/s<sup>2</sup>2) upwards movement is + downwards is -
+
|heading_accuracy||double precision||||||
 
|-
 
|-
−
|speed ||float8||||||m/s
+
|pressure||double precision||||||Pressure measured by GPS tag sensor in Pascals
 
|-
 
|-
−
|course||float8||||||Degrees
+
|temperature||double precision||||||Temperature measured by GPS tag sensor in degrees Celsius
 
|-
 
|-
−
|pressure||float8||||||Pressure sensor (pascal)
+
|solar_voltage||double precision||||||Solar voltage at the time of the measurement in volts
 
|-
 
|-
−
|temperature||Float8||||||Degrees celsius
+
|battery_voltage||double precision||||||Battery voltage at the time of the measurement in volts
 
|-
 
|-
−
|solar_voltage||Float8||||||V
+
|gps_fixstatus||text||||||2D = latitude and longitude measured; 3D= latitude, longitude and altitude measured; nofix_power = no GPS measurement
 
|-
 
|-
−
|battery_voltage||Float8||||||V
+
|gps_fixtime||double precision||||||GPS fix time (also known as Time to Fix) in seconds
 
|-
 
|-
−
|userflag||Integer||||||Data flagged as unacceptable by user
+
|location||geometry||||||postgreSQL geometry
 
|-
 
|-
−
| “"location”"||geometry||||||postgreSQL geometry
+
|userflag||integer||||||Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
 
|-
 
|-
 
|}
 
|}
−
 
  
 
=== UVA_TRACKING_DATA101 (Table) ===
 
=== UVA_TRACKING_DATA101 (Table) ===
Line 186: Line 194:
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
 
|-
 
|-
−
|device_info_serial||int4||PK||NOT NULL ||
+
|device_info_serial||integer||PK||NOT NULL ||Serial number of GPS tag
 
|-
 
|-
−
|date_time||timestamp||PK||NOT NULL||UTC
+
|date_time||timestamp||PK||NOT NULL||Timestamp without time zone in UTC
 
|-
 
|-
−
|latitude ||float||||||Decimal degrees
+
|latitude ||double precision|||||||Latitude measured by GPS tag for this record, unit = dec degrees
 
|-
 
|-
−
|longitude ||float||||||Decimal degrees
+
|longitude ||double precision|||||||Longitude measured by GPS tag for this record, unit = dec degrees
 
|-
 
|-
−
|altitude ||integer||||||height above sea level (meters)
+
|altitude ||integer||||||Altitude above sea level measured by GPS tag in meters
 
|-
 
|-
−
|pressure ||integer||||||in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa)
+
|pressure ||integer||||||Pressure measured by GPS tag sensor in Pascals (1mBar = 100Pa, 1pressuremeter = ~12Pa)
 
|-
 
|-
−
|temperature ||float||||||in celcius degrees
+
|temperature ||double precision||||||Temperature measured by GPS tag sensor in degrees Celsius
 
|-
 
|-
−
|satellites_used ||smallint||||||satellites used for fix  
+
|satellites_used ||smallint||||||Number of satellites used for fix the GPS measurement
 
|-
 
|-
−
|gps_fixtime ||float||||||GpsFixTime in seconds  
+
|gps_fixtime ||double precision||||||GPS fix time (also known as Time to Fix) in seconds  
 
|-
 
|-
−
|positiondop ||float||||||Position DOP in units
+
|positiondop ||double precision||||||Positional DOP (Dilution of Precision), unitless
 
|-
 
|-
−
|h_accuracy || float||||||Horizontal accuracy (units of meter)
+
|h_accuracy || double precision||||||Horizontal accuracy (meters)
 
|-
 
|-
−
|v_accuracy || float||||||Vertical accuracy (units of meter)
+
|v_accuracy || double precision||||||Vertical accuracy (meters)
 
|-
 
|-
−
|x_speed ||float||||||Speed in x-axis (m/s)
+
|x_speed ||double precision||||||Speed in x-axis (m/s)
 
|-
 
|-
−
|y_speed ||float||||||Speed in y-axis (m/s)
+
|y_speed ||double precision||||||Speed in y-axis (m/s)
 
|-
 
|-
−
|z_speed ||float||||||Speed in z-axis (m/s)
+
|z_speed ||double precision||||||Speed in z-axis (m/s)
 
|-
 
|-
−
|speed_accuracy || float||||||Speed accuracy (m/s)  
+
|speed_accuracy ||double precision||||||Speed accuracy (m/s)  
 
|-
 
|-
−
|userflag||Integer||||||Data flagged as unacceptable by user if not 0
+
|userflag||integer||||||Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
 
|-
 
|-
−
|location || geometry |||||| lat/long as PostGIS geometry
+
|location || geometry |||||| postgreSQL geometry
 
|}
 
|}
  
Line 246: Line 254:
 
|-
 
|-
 
|}
 
|}
−
 
  
 
=== UVA_NEST or UVA_NEST_LIMITED (Table and View) ===
 
=== UVA_NEST or UVA_NEST_LIMITED (Table and View) ===
Line 274: Line 281:
 
=== UVA_DEVICE or UVA_DEVICE_LIMITED (Table and View) ===  
 
=== UVA_DEVICE or UVA_DEVICE_LIMITED (Table and View) ===  
  
−
To be able to track the different firmware versions and specific properties for the different devices the UVA_DEVICE table was added. This table also includes essential information needed to calibrate the accelerometers. For more information see the section below called [[UvAGPS|Accelerometer calibration]].
+
Different GPS tags have been used over the years. The UVA_DEVICE table was created to be able to track the different firmware versions and specific properties for the different devices. This table also includes essential information needed to calibrate the accelerometers. For more information see the section below called [[UvAGPS|Accelerometer calibration]]. For more information about the different tags, see the section below called [[UvAGPS|Information about GPS receivers]].  
−
Different GPS chips have been used for the tags over the years for more information see the section below called [[UvAGPS|Information about GPS receivers]].  
 
  
 
{|border=1
 
{|border=1
Line 299: Line 305:
 
|-
 
|-
 
|}
 
|}
−
 
  
 
=== UVA_ACC_START102 (Table) or UVA_ACC_START_LIMITED (View)===
 
=== UVA_ACC_START102 (Table) or UVA_ACC_START_LIMITED (View)===
Line 322: Line 327:
 
|f||smallint||||||Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz)
 
|f||smallint||||||Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz)
 
|}
 
|}
−
 
  
 
=== UVA_ENGINEERING_DATA (Table) ===
 
=== UVA_ENGINEERING_DATA (Table) ===
Line 376: Line 380:
 
|-
 
|-
 
|}
 
|}
−
 
  
 
=== UVA_COMM101 (Table) or UVA_COMM_LIMITED (View)===  
 
=== UVA_COMM101 (Table) or UVA_COMM_LIMITED (View)===  
Line 434: Line 437:
 
|longitude||float||||||Longitude of last fix. Decimal degrees
 
|longitude||float||||||Longitude of last fix. Decimal degrees
 
|}
 
|}
−
 
  
 
=== UVA_SETTINGS101 (Table) or UVA_SETTINGS_LIMITED (View) ===  
 
=== UVA_SETTINGS101 (Table) or UVA_SETTINGS_LIMITED (View) ===  
Line 509: Line 511:
 
|eventnum || text|||||| 12 eventnumbers (0-0xff) only printed if not zero, therfore converted to text
 
|eventnum || text|||||| 12 eventnumbers (0-0xff) only printed if not zero, therfore converted to text
 
|}
 
|}
−
 
  
 
=== UVA_ENERGY101 (Table) or UVA_ENERGY_LIMITED (View)===
 
=== UVA_ENERGY101 (Table) or UVA_ENERGY_LIMITED (View)===
Line 550: Line 551:
 
|temperature || float|||||| in celcius degrees   
 
|temperature || float|||||| in celcius degrees   
 
|}
 
|}
−
 
  
 
=== UVA_WARNING102 (Table) or UVA_WARNING_LIMITED (View)===
 
=== UVA_WARNING102 (Table) or UVA_WARNING_LIMITED (View)===
Line 574: Line 574:
 
|line||text||||||The actual line in the file  
 
|line||text||||||The actual line in the file  
 
|}
 
|}
 +
 +
=== uva_access_device (Table) & uva_access_individual (Table) ===
 +
 +
To regulate the access to bird trackers and individuals, access is granted through these tables. These tables grant the rights to see tracking devices and birds, respectively. Only individuals with administrative privileges are allowed to work with these tables. The data entered into these tables produces the "_limited" tables and views.
 +
 +
=== uva_trackingfile_messages (Table) & uva_trackingfile_parsing (Table) ===
 +
 +
These two tables are produced as the raw data is parsed and stored in the database. Only individuals with administrative privileges are allowed to view or work with these tables.
  
 
= UvAGPS functions =
 
= UvAGPS functions =

Latest revision as of 07:12, 31 July 2014

UvA-BiTS Data Model

All tables, views and functions related to UvA-BiTS are in the GPS schema in the flysafe database and begin with the prefix "uva_". The tables/views that do not begin with "uva_" are related to a different project.

Not everybody has access to all data. Most users will only be granted access to views with the suffix "_limited" that show data collected with devices specific to their project(s).

The difference between a Table and a View: The difference is actually quite small from the user perspective. The same data can be queried and viewed in the same way in both tables and views. However, the data stored in a table can also be updated, deleted and changed by people with administrative privileges. The purpose of a view is to allow users who only want to view and/or query the data to do so without any possibility of altering the data. For this reason, we recommend that most users access the data through the Views. In addition, views often contain valuable additional data calculated from the raw tracking data.

Where to find the basic tracking data: Depending on the version of UvA-BiTS firmware installed in the GPS device, different database tables/views are used to store tracking data:

  • UVA_TRACKING view: Created to make it easier for most users to view/query basic tracking data. All tracking data for all types of firmware are pulled from the different tables and seen/queried in this one view.
  • UVA_TRACKING_DATA table: Used to store tracking data collected with devices running firmware version v0.9.3.6 till v1.0.0.X.
  • UVA_TRACKING_DATA101 table: Introduced to the database to store the data collected with the newest firmware (versions 1.0.1.4 till 1.0.2.4) because the new version is able to collect so much more information.

The tables/views in the UvA-BiTS database are described in detail in the sections below. Each table/view has been placed into one of three sections depending on the type of data they contain:

  • Tracking Data: Primary tracking data that is of most interest to most users (i.e. device numbers, lat/long, x/y/z acceleration, altitude, etc.).
  • Bird Data: Data about the birds on which the devices are mounted (species, sex, etc.).
  • Other Data: Data used for administrative, engineering, calibration or data quality purposes and therefore usually not commonly used by most users.


Tracking Data

UVA_TRACK_SESSION or UVA_TRACK_SESSION_LIMITED (Table and View)

This is essential metadata about GPS tag deployment that is provided by the project manager when the tag is first mounted on a bird. This table includes information about each measurement session and enables inclusion of data from new species and also from the test phase of different tags. To avoid analyzing data that was collected during testing, you should always include this table/view in your query as a way to select data that was measured after the start_date of a particular track session.

Field Type Key Constraints Comments
device_info_serial integer FK NOT NULL Serial number of GPS tag
ring_number text FK NOT NULL Ring number of individual bird. This field is what links this table to the UVA_INDIVIDUAL table. Tests do not have a ring_number.
start_date timestamp Timestamp without time zone (UTC) when tracking begins in yyyy-mm-dd hh:mm:ss.
end_date timestamp Timestamp without time zone (UTC) when tracking session ends, if known, in yyyy-mm-dd hh:mm:ss. This field will be blank if the project is still ongoing.
start_latitude double precision NOT NULL Latitude where tracking begins, unit = dec degrees
start_longitude double precision NOT NULL Longitude where tracking begins, unit = dec degrees
project_leader text Project leader and point of contact for questions about data
remarks text Optional field for project manager to add comments.

UVA_TRACKING or UVA_TRACKING_LIMITED (View)

Created to make it easier for most users to view/query all tracking data. All tracking data for all types of firmware are pulled from different data tables (UVA_TRACKING_DATA table & UVA_TRACKING_DATA101 table) and seen/queried in this one view.

Field Type Key Constraints Comments
device_info_serial integer PK NOT NULL Serial number of GPS tag
date_time timestamp PK NOT NULL Timestamp of GPS measurement without time zone (UTC)
latitude float Latitude measured by GPS tag for this record, unit = dec degrees
longitude float Longitude measured by GPS tag for this record, unit = dec degrees
altitude float Altitude above sea level measured by GPS tag in meters
pressure integer Pressure measured by GPS tag sensor in Pascals
temperature float Temperature measured by GPS tag sensor in Celsius
h_accuracy float Horizontal accuracy (meters)
v_accuracy float Vertical accuracy (meters)
x_speed float meters/second
y_speed float meters/second
z_speed float meters/second
gps_fixtime float GPS fix time (also known as Time to Fix) in seconds
userflag integer Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
satellites_used smallint Number of satellites used for fix the GPS measurement
positiondop float Positional DOP (Dilution of Precision), unitless
speed_accuracy float
location geometry postgreSQL geometry

UVA_ACCELERATION101 (Table) or UVA_ACCELERATION_LIMITED (View)

Acceleration entries if accelerometer was turned on. Only for newer firmware. Actual measurements have shown that the range of values for x,y and z are larger then indicated by the manufacturer. For more information about calibration and interpretation of accelerometer data see Accelerometer calibration.

Field Type Key Constraints Comments
device_info_serial integer PK NOT NULL Serial number of GPS tag
date_time timestamp PK NOT NULL Timestamp of measurement without time zone (UTC)
index smallint PK NOT NULL Row index (from 0) of accelerometer measurement series
x_acceleration smallint Acceleration in X-axis (-2666 till 2666)
y_acceleration smallint Acceleration in Y-axis (-2666 till 2666)
z_acceleration smallint Acceleration in Z-axis (-2666 till 2666)

UVA_TRACKING_SPEED or UVA_TRACKING_SPEED_LIMITED (View)

Additional view that computes the vnorth, veast and vdown speed components as well as the 3D speed in meters/second. These fields are calculated using components from UVA_TRACKING.

In addition to the fields in the UVA_TRACKING view, this view adds :

Field Type Key Constraints Comments
vnorth float - vx * sin(lat) * cos(long) - vy * sin(lat) * sin (long) + vz * cos(lat)
veast float - vx * sin(lon) + vy * cos(lon)
vdown float - vx * cos(lat) * cos(lon) - vy * cos(lat) * sin(lon) - vz * sin(lat)
speed float Sqrt(vnorth^2 + veast^2)
speed3d float Sqrt(vnorth^2 + veast^2 + vdown^2)
direction float Heading in degrees; 0 is north, 90 is east

UVA_TRACKING_DATA (Table)

Tracking data from UvA GPS tags using older firmware. All of this data is also brought into the UVA_TRACKING view as described above.

Field Type Key Constraints Comments
device_info_serial integer PK NOT NULL Serial number of GPS tag
date_time timestamp PK NOT NULL Timestamp without time zone in UTC
latitude double precision Latitude measured by GPS tag for this record, unit = dec degrees
longitude double precision Longitude measured by GPS tag for this record, unit = dec degrees
h_accuracy double precision Horizontal accuracy (meters)
v_accuracy double precision Vertical accuracy (meters)
altitude double precision Altitude above sea level measured by GPS tag in meters
x_acceleration double precision x axis acceleration (m/s22) movement to right is + and to left is -
y_acceleration double precision y axis acceleration (m/s22) forward movement is + and backwards is -
z_acceleration double precision z axis acceleration (m/s22) upwards movement is + and downwards is -
x_speed double precision Speed in x-axis (m/s)
y_speed double precision Speed in y-axis (m/s)
z_speed double precision Speed in z-axis (m/s)
speed_3d double precision meters/second
heading double precision
heading_accuracy double precision
pressure double precision Pressure measured by GPS tag sensor in Pascals
temperature double precision Temperature measured by GPS tag sensor in degrees Celsius
solar_voltage double precision Solar voltage at the time of the measurement in volts
battery_voltage double precision Battery voltage at the time of the measurement in volts
gps_fixstatus text 2D = latitude and longitude measured; 3D= latitude, longitude and altitude measured; nofix_power = no GPS measurement
gps_fixtime double precision GPS fix time (also known as Time to Fix) in seconds
location geometry postgreSQL geometry
userflag integer Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.

UVA_TRACKING_DATA101 (Table)

The tracking data for newer firmware (V 1.0.1.4 and higher). All of this data is also brought into the UVA_TRACKING view described above.

Field Type Key Constraints Comments
device_info_serial integer PK NOT NULL Serial number of GPS tag
date_time timestamp PK NOT NULL Timestamp without time zone in UTC
latitude double precision Latitude measured by GPS tag for this record, unit = dec degrees
longitude double precision Longitude measured by GPS tag for this record, unit = dec degrees
altitude integer Altitude above sea level measured by GPS tag in meters
pressure integer Pressure measured by GPS tag sensor in Pascals (1mBar = 100Pa, 1pressuremeter = ~12Pa)
temperature double precision Temperature measured by GPS tag sensor in degrees Celsius
satellites_used smallint Number of satellites used for fix the GPS measurement
gps_fixtime double precision GPS fix time (also known as Time to Fix) in seconds
positiondop double precision Positional DOP (Dilution of Precision), unitless
h_accuracy double precision Horizontal accuracy (meters)
v_accuracy double precision Vertical accuracy (meters)
x_speed double precision Speed in x-axis (m/s)
y_speed double precision Speed in y-axis (m/s)
z_speed double precision Speed in z-axis (m/s)
speed_accuracy double precision Speed accuracy (m/s)
userflag integer Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
location geometry postgreSQL geometry


Bird Data

UVA_INDIVIDUAL or UVA_INDIVIDUAL_LIMITED (Table and View)

Table includes important information used to identify an individual tracked with a GPS tag. Remarks can also include a URL linking to a file with additional information about the individual (eg additional biometric measurements)

Field Type Key Constraints Comments
ring_number text PK NOT NULL metal ring number
color_ring text code for color ring without punctuation
species text NOT NULL Scientific name of species
sex text update if or when known
mass float mass of bird at start of tracking session
Remarks text If additional information of this individual is available please provide the file with relevant data (e.g. biometric data). This field should include the file name and extension

UVA_NEST or UVA_NEST_LIMITED (Table and View)

Table includes information, if available, used to identify the nest of an individual tracked with a GPS tag.

Field Type Key Constraints Comments
ring_number text FK NOT NULL metal ring number
nest_id text FK individual code for nest or territory
date_time timestamp Timestamp without time zone (UTC) when nest first marked yyyy-mm-dd hh:mm:ss
latitude float latitude of nest or territory (decimal degrees)
longitude float longitude of nest or territory (decimal degrees)
Remarks text If additional breeding information is available please provide the file with relevant data. This field should include the file name and extension


Other Data (Engineering, calibration, database administration and data quality)

UVA_DEVICE or UVA_DEVICE_LIMITED (Table and View)

Different GPS tags have been used over the years. The UVA_DEVICE table was created to be able to track the different firmware versions and specific properties for the different devices. This table also includes essential information needed to calibrate the accelerometers. For more information see the section below called Accelerometer calibration. For more information about the different tags, see the section below called Information about GPS receivers.

Field Type Key Constraints Comments
device_info_serial integer PK NOT NULL Serial number of tag
firmware_version text NOT NULL Firmware version installed on tag
device_mass double Mass of tag (g)
x_s double Calibration sensitivity of accelerometer in the x-axis
y_s double Calibration sensitivity of accelerometer in the y-axis
z_s double Calibration sensitivity of accelerometer in the z-axis
x_o double Calibration offset of accelerometer in the x-axis
y_o double Calibration offset of accelerometer in the y-axis
z_o double Calibration offset of accelerometer in the z-axis

UVA_ACC_START102 (Table) or UVA_ACC_START_LIMITED (View)

General acceleration entry associated with a set of accelerations. Added in Firmware 1.0.2.7 because there line numbers were introduced for acceleration lines so that these lines can be tracked.

Field Type Key Constraints Comments
device_info_serial int4 PK NOT NULL
date_time timestamp PK NOT NULL
line_counter integer Number of lines associated with this set of accelerations
timesynced smallint TimeSynced (0=not synced, 1=user set, 2=Gps Synced)
accii integer Accelero measurement interval
accsn integer Number of samples (0-65536)
f smallint Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz)

UVA_ENGINEERING_DATA (Table)

Engineering data from UvA GPS tags for older firmware (up to and including V 1.0.0.3). These tags used the UBlox gps chip LEAS4S.

Field Type Key Constraints Comments
device_info_serial int4 PK NOT NULL
date_time timestamp PK NOT NULL UTC
t_syncsts smallint 0: not synced, 1: user synced, 2: Gps synced
t_entzt smallint 0: UCT time,1: TZT time (timezone time)
gzp_currentrategps int4
gzp_currentratecom int4
gzp_currentratepwr int4
sbm_voltagesolarpanel float8
sbm_voltagebattery float8
sbm_chargemode int2
egzp_mainrategps integer
egzp_mainratecom integer
egzp_mainratepwr integer
smg_on text
smg_off text
smg_esavegpsrate integer
smz_on text
smz_off text
smz_esavecomrate integer
gpsnavigationmode smallint 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 UBlox chip LEAS4S: 1 = stationary, 2 = pedestrian, 3 = automotive, 4 = sea, 5 = airborne 1G, 6 = airborne 2G, 7 = airborne 3G
gpsfixtimeout smallint
eh text

UVA_COMM101 (Table) or UVA_COMM_LIMITED (View)

Communication table indicates status and result of communications with ground stations. Only for newer firmware (V 1.0.1.4 and higher).

Field Type Key Constraints Comments
device_info_serial integer PK NOT NULL
date_time timestamp PK NOT NULL timestamp of start communication
date_time_end timestamp timestamp of end communication
date_time_utc timestamp date_time in utc (if available)
date_time_end_utc timestamp date_time_end in utc (if available)
action text JOINED / RESCHEDULE  ??
bytes_communicated integer Number of bytes communicated (total of all bytes per DATA line)
status text READY, TIMEOUT, LOWPOWER, MISSING
network_join_time integer milliseconds
number_identical_lines integer Number identical lines in data block. Gives indication
non_ascii_char integer Number of non ascii characters (indicated by question-mark ?)
sts_time timestamp derived from S0 hh,mi
timesynced smallint TimeSynced (0=not sy:wnced, 1=user set, 2=Gps Synced)
devicemode smallint 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 float Battery voltage in V (0-~4.200V)
logb integer logged bytes, waiting for download (0-4Mbyte)
logp smallint Logged percentage (units 0.1%) of memory waiting to be downloaded (0-1000)
gpsii integer GpsIval, current (0-65535)
comii integer ComIval, current (0-65535)
accii integer AcceleroIval, current (0-65535)
swddd integer User switches, current (0-65535)
last_gps_fix timestamp timestamp of last fix
latitude float Latitude of last fix. Decimal degrees
longitude float Longitude of last fix. Decimal degrees

UVA_SETTINGS101 (Table) or UVA_SETTINGS_LIMITED (View)

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 no new records needs to be added If uplink settings are parsed wait till the last setting has been seen, (DL command or EOF) then store all the new settings in one record. Only for newer firmware.

Field Type Key Constraints Comments
device_info_serial int4 PK NOT NULL
date_time timestamp PK NOT NULL Derive timestamp from Hh:mi fields in C0 combined with computer timestamp in JOINED line
timesynced smallint
timezone_time smallint
swddd integer SpecialUserSwitches
gpsnavmode smallint 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 smallint GpsReceiverNode 0-3
gpsfixtime smallint GpsFixTime 0-255
gps_ivaln_on_h smallint hours Gps IvalN on
gps_ivaln_on_m smallint minutes Gps IvalN on
gps_ivaln integer GpsIvalN (0-65530)
gps_ivale_on_h smallint hours Gps IvalE on
gps_ivale_on_m smallint minutes GpS IvalE on
gps_ivale integer GpsIvalE
com_ivaln_on_h smallint hours Com IvalN on
com_ivaln_on_m smallint minutes Com IvalN on
com_ivaln integer ComIvalN (0-65530)
com_ivale_on_h smallint hours Com IvalE on
com_ivale_on_m smallint minutes Com IvalE on
com_ivale integer ComIvalE
accii integer Accelero measurement interval
accbn integer Accelero number of blocks
accsn integer Accelero number of samples (0-65536)
f smallint Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz)
pdp smallint PdopMsk (3-250)
pac smallint PaccMsk (10-100)
tdp smallint TdopMsk (3-250)
tac smallint TaccMsk (30-300)
ga smallint Additional Fixes GpsIval mode (3-25) before interval fix is logged
aol smallint 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 12 eventnumbers (0-0xff) only printed if not zero, therfore converted to text

UVA_ENERGY101 (Table) or UVA_ENERGY_LIMITED (View)

Only for newer firmware.

Field Type Key Constraints Comments
device_info_serial int4 PK NOT NULL
date_time timestamp PK NOT NULL
timesynced smallint TimeSynced (0=not synced, 1=user set, 2=Gps synced)
devicemode smallint (0-99)
vsll float Solar Voltage during charging in mV (0-~2500)
vbat float Battery Voltage in V (0-~4.200)
ssw smallint Solar Cell Switches ... (0-255) fw1022 and up
extended fields if special user switch is on
vsoo float Solar voltage open in V (0-~2.500)
vdrf float reference voltage Dac in V (0-~2.435)
vchr float charge current voltage
g smallint Gps battery threshold OK
c smallint Com battery threshold OK
m smallint Charge Mode (0,1,2 Com battery threshold OK
pressure integer in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa)
temperature float in celcius degrees

UVA_WARNING102 (Table) or UVA_WARNING_LIMITED (View)

Since firmware version 1.0.2.7 this table was added as catchall for tags 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.uva_warning102 
 where warning = 'CRC8 failure'
 order by device_info_serial;
Field Type Key Constraints Comments
device_info_serial integer PK NOT NULL
filename text PK NOT NULL UvAGPS log filename
linenumber integer PK NOT NULL linenumber in file
warning text The warming message or tag
line text The actual line in the file

uva_access_device (Table) & uva_access_individual (Table)

To regulate the access to bird trackers and individuals, access is granted through these tables. These tables grant the rights to see tracking devices and birds, respectively. Only individuals with administrative privileges are allowed to work with these tables. The data entered into these tables produces the "_limited" tables and views.

uva_trackingfile_messages (Table) & uva_trackingfile_parsing (Table)

These two tables are produced as the raw data is parsed and stored in the database. Only individuals with administrative privileges are allowed to view or work with these tables.

UvAGPS functions

get_uvagps_track_speed

Compute distance (meters), time-interval (time stamp hh:mm:ss), speed (meters/second) and heading (degrees) for a series of subsequent tracking entries with specific device_info_serial. For the first entry in the sequence these values will always be missing because they are computed as the difference between subsequent rows.

Usage examples : 

select * from gps.get_uvagps_track_speed(119, '2008-01-01 00:00:00', '2010-01-01 00:00:00');

select * from gps.get_uvagps_track_speed(119);

select t.*, a.distance, a.interval, a.speed, a.direction 
from gps.get_uvagps_track_speed(119, '2008-01-01 00:00:00', '2010-01-01 00:00:00') a 
join gps.uva_tracking t using(device_info_serial, date_time);

Distances and heading are calculated with spherical correction with the Haversine formula as described here. The heading is the initial heading. PostGIS does not calculate the heading, so therefore we don't use PostGIS.

In these functions the records that have been flagged as invalid by setting the userflag to 1 are not taken into account and discarded. If speed calculations are required for all records an additional boolean argument get raw data can be given. i.e. 

select * from gps.get_uvagps_track_speed(119, '2008-01-01 00:00:00', '2010-01-01 00:00:00', true);

select * from gps.get_uvagps_track_speed(119, true);

get_uvagps_track_distance

Compute distance (meters) for UvAGPS tracks to arbitrary point in lat/long or geometry location

Example usage : 

select * from gps.get_uvagps_track_distance(119, 52.1897017, 6.1996606); 

select * from gps.get_uvagps_track_distance(119, (select location from gps.uva_tracking where device_info_serial = 119 and date_time = '2009-05-07 19:55:08'));

Here the distance is calculated with the PostGIS distance_sphere function which uses spherical correction.

get_uvagps_track_distance_direction

Compute distance (meters) and direction (degrees) for UvAGPS tracks from arbitrary point in lat/long or geometry location

Example usage: 

select * from gps.get_uvagps_track_distance_direction(119, 52.1897017, 6.1996606);

Distances and direction are calculated with spherical correction with the Haversine formula as described here. The direction is the direction from the given point.

In this function the records where the userflag has been set are discarded. To see these records a additional boolean argument for show raw data should be added like : 

select * from gps.get_uvagps_track_distance_direction(119, 52.1897017, 6.1996606, true);

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=UvAGPS&oldid=1005"