UvA-BiTS Tracking 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

Each row shows track session's information for all track sessions 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.

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)
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_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_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 inserted into this specific table.

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 table gps.ee_tracking_data101 for the given tracker, 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).