UvA-BiTS Tracking Data
Leaf 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_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 before which the bird is not relevant. |
| end_date | timestamp | NO | Date at which the bird is no longer relevant. |
| remarks | text | YES | Text for user remarks |
| individual_id | int8 | NO | Bird 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 |
| start_longitude | numeric(11,8) | NO | Longitude where the bird was tagged |
| 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. |
Tracking data
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 |
| 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_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.
You will not see anything here for any tracker that has not been attached to a bird yet.
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. |
| longitude | float8 | YES | Longitude of the GPS fix. |
| altitude | int4 | YES | Altitude above sea level measured by GPS tag in meters |
| 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) |
| veast | float8 | YES | - vx * sin(lon) + vy * cos(lon) |
| vdown | float8 | YES | - vx * cos(lat) * cos(lon) - vy * cos(lat) * sin(lon) - vz * sin(lat) |
| speed | float8 | YES | - vx * sin(lon) + vy * cos(lon) |
| speed3d | float8 | YES | - vx * cos(lat) * cos(lon) - vy * cos(lat) * sin(lon) - vz * sin(lat) |
| direction | numeric | YES | Heading in degrees; 0 is north, 90 is east |
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.
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.
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.
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). |
gps.ee_acceleration_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.
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.
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. |
| 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). |
Relation to projects
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
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 |
