DB Tables 2015

Note: What follows is a list of tables and explanations intended for the database maintainers or otherwise people actively involved in the development of the e-Ecology system itself. Most e-Ecology users should focus on the documentation on the UvA-BiTS Tracking Data page. This page is intended as a reference only.

The database is split into two useful schemas: admin and gps. The gps schema is meant for data that should be readily available for users, and that they may edit via the services (software tools) offered to that effect. The admin schema is meant to host data only accessible for e-Ecology administrators.

Source data

admin.ee_project_limited

Each row represents a project.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	int8	NO	Unique identifier. Primary key
description	text	YES	String for free description. Updatable by the user.
end_date	timestamp	NO	Date at which the project is not relevant any more
key_name	varchar	NO	Unique key name, of the form species_place
lft	int8	NO	"Modified preorder tree traversal" field. Ignore it as a user.
rgt	int8	NO	"Modified preorder tree traversal" field. Ignore it as a user.
start_date	timestamp	NO	Project start date
station_name	varchar	NO	Where the receiving station is
version	int4	YES	Optimistic locking field. Ignore it as a user.
operator_user	int8	NO	Reference to the user who last edited the project
parent	int8	YES	Reference to the parent project. Only the root project has no parent.

admin.ee_user

Each row represents a user.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	int8	NO	Unique identifier. Primary key
address	varchar	YES	String for postal address
email	varchar	NO	e-mail address
encrypted_password	varchar	NO	Encrypted password
login_name	varchar	NO	Login name
mobile_phone	varchar	YES	String for mobile phone number
name	varchar	NO	(first) name
surname	varchar	NO	Surname
version	int4	YES	Optimistic locking field. Ignore it as a user.
operator_user	int8	YES	Id of the user who last edited this user's data.

gps. ee_tracker

Each row represents a tracker.

This table replaces the old gps.uva_device.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	int8	NO	Unique identifier. Primary key.
device_info_serial	int8	NO	Serial number of the tracker
end_date	timestamp	NO	Date at which the device is not relevant any more
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
version	int4	YES	Optimistic locking field. Ignore it as a user.
x_o	numeric(30,6)	YES	Accelerometer calibration: offset on the x (surge) axis
x_s	numeric(30,6)	YES	Accelerometer calibration: sensitivity on the x (surge) axis
y_o	numeric(30,6)	YES	Accelerometer calibration: offset on the y (sway) axis
y_s	numeric(30,6)	YES	Accelerometer calibration: sensitivity on the y (sway) axis
z_o	numeric(30,6)	YES	Accelerometer calibration: offset on the z (heave) axis
z_s	numeric(30,6)	YES	Accelerometer calibration: sensitivity on the y (heave) axis

gps.ee_individual

Each row represents a bird.

This table replaces gps.uva_individual from the old database.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	int8	NO	Unique identifier. Primary key.
colour_ring	varchar	YES	String for ring colour
end_date	timestamp	NO	Date at which the bird is no longer relevant
mass	numeric(5,0)	YES	Mass (grams)
operated_date	timestamp	NO	Recording of the last time the row was edited
remarks	text	YES	Text for user remarks
ring_number	varchar	NO	Ring number
sex	varchar	NO	Sex
start_date	timestamp	NO	Date before which the bird is not relevant
version	int4	YES	Optimistic locking field. Ignore it as user.
operator_user	int8	NO	Reference to the user who last edited the row
species	int8	NO	Reference to the species

gps.ee_track_session

Each row represents a track session.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	int8	NO	Unique identifier. Primary key.
end_date	timestamp	NO	Date at which the track session is not relevant any more
operated_date	timestamp	NO	Recording of the last time the row was edited
remarks	text	YES	Free text for comments
start_date	timestamp	NO	Date before which the track session is not relevant
start_latitude	numeric(11,8)	NO	Latitude where the bird was tagged
start_longitude	numeric(11,8)	NO	Longitude where the bird was tagged
version	int4	YES	Optimistic locking field. Ignore it as a user.
device	int8	NO	Reference to the tracker
individual	int8	NO	Reference to the individual
operator_user	int8	NO	Reference to the user who last edited the row
project	int8	NO	Reference to the project this track session belongs to

Tracking data

gps.uva_tracking_data101

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

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 (WGS1984 datum)
longitude	float8	YES	Longitude of the GPS fix (WGS1984 datum)
altitude	int4	YES	Altitude above sea level measured by GPS tag in meters (WGS1984 datum)
pressure	int4	YES	Pressure measured by GPS tag sensor in Pascals
temperature	float8	YES	Temperature measured by GPS tag sensor in Celsius
h_accuracy	float8	YES	Horizontal accuracy (meters)
v_accuracy	float8	YES	vertical accuracy (meters)
x_speed	float8	YES	meters/second
y_speed	float8	YES	meters/second
z_speed	float8	YES	meters/second
gps_fixtime	float8	YES	GPS fix time (also known as Time to Fix) in seconds
userflag	int4	YES	Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
satellites_used	int2	YES	Number of satellites used for fix the GPS measurement
positiondop	float8	YES	Positional DOP (Dilution of Precision), unitless
speed_accuracy	float8	YES
location	geometry	YES	postgreSQL geometry; to allow GIS operations
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.uva_acc_start102

Each row represents the start of a block of accelerations when a GPS fix is not available.

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.

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

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, so the 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 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 a specific table called gps.ee_acc_start102. 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.

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

This table holds nests' information. A nest is always for (at least) 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
id	int8	NO	Unique id. Primary key.
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 (WGS1984 datum)
longitude	numeric	YES	Longitude of the coordinates where the nest was found (WGS1984 datum)
start_date_time	timestamp	NO	When the nest was spotted.
end_date_time	timestamp	NO	When the nest ceases to be relevant.
found_by_whom	varchar	YES	Place to credit people involved in finding the nest.
remarks	text	YES	Free text.
location	bytearray	YES	Calculated Geometry of the coordinates where the nest was found.
operated_date	timestamp	NO	When was this row last edited.
operator_user	int8	NO	Reference to a user id. Last one who edited the row.

gps.ee_nest_inhabitant

This table is the materialisation of the many-to-many relation between nests and birds. The primary key of the table is the pair of both columns.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
nest_id	int8	NO	Reference to the nest id.
individual_id	int8	NO	Reference to the individual id.

Relation to projects

admin.ee_membership

Each row represents the fact that a user has a given role in a given project at the moment.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	int8	NO	Unique identifier. Primary key.
comments	text	YES	Free text for comments
label	text	YES	Labels that Willem wants to have for people in projects
role	varchar	NO	Role that the user holds during this period
status	varchar	NO	Status that the row holds during that period. Look at the attached file to know the allowed transitions.
version	int4	YES	Optimistic locking field. Ignore it as a user.
member_user	int8	NO	Reference to the user that this membership applies to
operated_date	timestamp	NO	Date that the row was last modified
operator_user	int8	NO	Reference to the user who last edited this row
project	int8	NO	Reference to the project that this membership applies to

admin.ee_tracker_ownership

Each row represents the fact that a tracker belongs to a project within a specified time period.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	int8	NO	Unique identifier. Primary key.
end_date	timestamp	NO	Date at which the tracker ceases to belong to the project
operated_date	timestamp	NO	Recording of the last time the row was edited
start_date	timestamp	NO	Date at which the project begins to own the tracker
version	int4	YES	Optimistic locking field. Ignore it as a user.
device	int8	NO	Reference to the tracker
operator_user	int8	NO	Reference to the user who last edited the row
project	int8	NO	Reference to the project this tracker belongs to

admin.ee_individual_ownership

Each row represents the fact that an individual belongs to a project within a specified time period.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	int8	NO	Unique identifier. Primary key.
end_date	timestamp	NO	Date at which the individual ceases to belong to the project
operated_date	timestamp	NO	Recording of the last time the row was edited
start_date	timestamp	NO	Date at which the project begins to own the individual
version	int4	YES	Optimistic locking field. Ignore it as a user.
individual	int8	NO	Reference to the individual
operator_user	int8	NO	Reference to the user who last edited the row
project	int8	NO	Reference to the project this individual belongs to

gps.ee_tracker_sharing

Each row represents the fact that a tracker's information within a specified period, can be accessed by users belonging to another project.

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	int8	NO	Unique identifier. Primary key.
end_date	timestamp	NO	Date at which the tracker information ceases to be available to the receiving project
operated_date	timestamp	NO	Recording of the last time the row was edited
start_date	timestamp	NO	Date at which the tracker information becomes available to the receiving project
version	int4	YES	Optimistic locking field. Ignore it as a user.
operator_user	int8	NO	Reference to the user who last edited the row
project	int8	NO	Reference to the project that receives access to the tracker information
tracker_ownership	int8	NO	Reference to the tracker ownership by virtue of which, the tracker information is shared

Engineering data

gps.uva_trackingfile_parsing

Each row on this table represents the status of the last attempt to parse (process) the raw data file that came from a tracker.

It is heavily used by the dashboard to determine how a given file is represented there.

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

COLUMN_NAME	TYPE_NAME	IS_NULLABLE	DESCRIPTION
id	serial	NO	Unique identifier of the row
tracking_bookkeeping_id	int4	YES	Reference to the gps.uva_trackingfile_parsing row for this file
message_type	int4	YES	Code for the type of information being told by the message.
message	text	YES	Textual message from the parsing process telling what it found.
message_code	int4	YES
line_number	int4	YES	Line in the file where the message is reported

gps.uva_warning102

Since firmware version 1.0.2.7 this table was added as catchall 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.uva_warning102 
 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	log file name
linenumber	integer	YES	line number in file
warning	text	YES	The warning message
line	text	YES	The actual line in the file

gps.uva_comm101

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

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

Only for newer firmware.

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

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.

This table structure has not changed from the old database.

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