UvA-BiTS Tracking Data

Meta 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 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_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.
longitude	numeric	YES	Longitude of the coordinates where the nest was found.
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.

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.

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

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.

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)
speed_3d	float8	YES	sqrt(d.x_speed^2 + d.y_speed^2 + d.z_speed^2)
speed_2d	float8	YES	Speed on 2D
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

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

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.
longitude	numeric	YES	Longitude of the coordinates where the nest was found.
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.

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.

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:

• Leaf data
  • 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
• 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
• Relation to projects
  • gps.ee_shared_tracker_sharing_limited: view information for each sharing that your projects allow you to see