DB Tables 2015

From Eecology

Jump to: navigation, search

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.

Contents

Source data

admin.ee_project_limited

Each row represents a project.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
idint8NOUnique identifier. Primary key
descriptiontextYESString for free description. Updatable by the user.
end_datetimestampNODate at which the project is not relevant any more
key_namevarcharNOUnique key name, of the form species_place
lftint8NO"Modified preorder tree traversal" field. Ignore it as a user.
rgtint8NO"Modified preorder tree traversal" field. Ignore it as a user.
start_datetimestampNOProject start date
station_namevarcharNOWhere the receiving station is
versionint4YESOptimistic locking field. Ignore it as a user.
operator_userint8NOReference to the user who last edited the project
parentint8YESReference 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
idint8NOUnique identifier. Primary key
addressvarcharYESString for postal address
emailvarcharNOe-mail address
encrypted_passwordvarcharNOEncrypted password
login_namevarcharNOLogin name
mobile_phonevarcharYESString for mobile phone number
namevarcharNO(first) name
surnamevarcharNOSurname
versionint4YESOptimistic locking field. Ignore it as a user.
operator_userint8YESId 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
idint8NOUnique identifier. Primary key.
device_info_serialint8NOSerial number of the tracker
end_datetimestampNODate at which the device is not relevant any more
firmware_versionvarcharYESString for firmware version
massnumeric(4,2)YESMass (in grams)
start_datetimestampNODate before which the device is not relevant
versionint4YESOptimistic locking field. Ignore it as a user.
x_onumeric(30,6)YESAccelerometer calibration: offset on the x (surge) axis
x_snumeric(30,6)YESAccelerometer calibration: sensitivity on the x (surge) axis
y_onumeric(30,6)YESAccelerometer calibration: offset on the y (sway) axis
y_snumeric(30,6)YESAccelerometer calibration: sensitivity on the y (sway) axis
z_onumeric(30,6)YESAccelerometer calibration: offset on the z (heave) axis
z_snumeric(30,6)YESAccelerometer 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
idint8NOUnique identifier. Primary key.
colour_ringvarcharYESString for ring colour
end_datetimestampNODate at which the bird is no longer relevant
massnumeric(5,0)YESMass (grams)
operated_datetimestampNORecording of the last time the row was edited
remarkstextYESText for user remarks
ring_numbervarcharNORing number
sexvarcharNOSex
start_datetimestampNODate before which the bird is not relevant
versionint4YESOptimistic locking field. Ignore it as user.
operator_userint8NOReference to the user who last edited the row
speciesint8NOReference to the species

gps.ee_track_session

Each row represents a track session.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
idint8NOUnique identifier. Primary key.
end_datetimestampNODate at which the track session is not relevant any more
operated_datetimestampNORecording of the last time the row was edited
remarkstextYESFree text for comments
start_datetimestampNODate before which the track session is not relevant
start_latitudenumeric(11,8)NOLatitude where the bird was tagged
start_longitudenumeric(11,8)NOLongitude where the bird was tagged
versionint4YESOptimistic locking field. Ignore it as a user.
deviceint8NOReference to the tracker
individualint8NOReference to the individual
operator_userint8NOReference to the user who last edited the row
projectint8NOReference 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.

This table structure has not changed with respect to our previous database.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
device_info_serialint4NOSerial number of the tracker that reported the fix. Part of the unique identifier of the GPS fix.
date_timetimestampNOTime when the fix was taken. UTC (i.e.: no timezone). Part of the unique identifier of the GPS fix.
latitudefloat8YESLatitude of the GPS fix.
longitudefloat8YESLongitude of the GPS fix.
altitudeint4YESAltitude above sea level measured by GPS tag in meters
pressureint4YESPressure measured by GPS tag sensor in Pascals
temperaturefloat8YESTemperature measured by GPS tag sensor in Celsius
h_accuracyfloat8YESHorizontal accuracy (meters)
v_accuracyfloat8YESvertical accuracy (meters)
x_speedfloat8YESmeters/second
y_speedfloat8YESmeters/second
z_speedfloat8YESmeters/second
gps_fixtimefloat8YESGPS fix time (also known as Time to Fix) in seconds
userflagint4YESAcceptable = 0; Data flagged as unacceptable by user if not equal to 0.
satellites_usedint2YESNumber of satellites used for fix the GPS measurement
positiondopfloat8YESPositional DOP (Dilution of Precision), unitless
speed_accuracyfloat8YES
locationgeometryYESpostgreSQL geometry; to allow GIS operations
speed_3dfloat8YES sqrt(d.x_speed^2 + d.y_speed^2 + d.z_speed^2)
speed_2dfloat8YES Speed on 2D
directionnumericYES Heading in degrees; 0 is north, 90 is east
altitude_aglfloat8YES 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_serialint4NOSerial number of the tracker that reported the acceleration measurement.
date_timetimestampNOTime to mark the start of the acceleration block.
line_counterint4NONumber of lines associated with this set of accelerations.
timesyncedint2YESThe source of the time that marks the acceleration block (0=not synced, 1=user set, 2=Gps Synced).
asciiint4YESAcceleration measurement interval.
accsnint4YESNumber of samples (0-65536).
fint2YESAcceleration 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_serialint4NOSerial number of the tracker that reported the fix. Part of the unique identifier of the GPS fix.
date_timetimestampNOTime of the acceleration block that this measurement belongs to.
indexint2NOSorting order of the measurement within the acceleration block (each block starts at 0).
x_accelerationint2YESAcceleration measured on the x (or surge) axis (range between -2666 and 2666).
y_accelerationint2YESAcceleration measured on the y (or sway) axis (range between -2666 and 2666).
z_accelerationint2YESAcceleration 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
idint8NOUnique id. Primary key.
reference_namevarcharNOA 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.
latitudenumericYESLatitude of the coordinates where the nest was found.
longitudenumericYESLongitude of the coordinates where the nest was found.
start_date_timetimestampNOWhen the nest was spotted.
end_date_timetimestampNOWhen the nest ceases to be relevant.
found_by_whomvarcharYESPlace to credit people involved in finding the nest.
remarkstextYESFree text.
locationbytearrayYESCalculated Geometry of the coordinates where the nest was found.
operated_datetimestampNOWhen was this row last edited.
operator_userint8NOReference 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_idint8NOReference to the nest id.
individual_idint8NOReference 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
idint8NOUnique identifier. Primary key.
commentstextYESFree text for comments
labeltextYESLabels that Willem wants to have for people in projects
rolevarcharNORole that the user holds during this period
statusvarcharNOStatus that the row holds during that period. Look at the attached file to know the allowed transitions.
versionint4YESOptimistic locking field. Ignore it as a user.
member_userint8NOReference to the user that this membership applies to
operated_datetimestampNODate that the row was last modified
operator_userint8NOReference to the user who last edited this row
projectint8NOReference 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
idint8NOUnique identifier. Primary key.
end_datetimestampNODate at which the tracker ceases to belong to the project
operated_datetimestampNORecording of the last time the row was edited
start_datetimestampNODate at which the project begins to own the tracker
versionint4YESOptimistic locking field. Ignore it as a user.
deviceint8NOReference to the tracker
operator_userint8NOReference to the user who last edited the row
projectint8NOReference 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
idint8NOUnique identifier. Primary key.
end_datetimestampNODate at which the individual ceases to belong to the project
operated_datetimestampNORecording of the last time the row was edited
start_datetimestampNODate at which the project begins to own the individual
versionint4YESOptimistic locking field. Ignore it as a user.
individualint8NOReference to the individual
operator_userint8NOReference to the user who last edited the row
projectint8NOReference 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
idint8NOUnique identifier. Primary key.
end_datetimestampNODate at which the tracker information ceases to be available to the receiving project
operated_datetimestampNORecording of the last time the row was edited
start_datetimestampNODate at which the tracker information becomes available to the receiving project
versionint4YESOptimistic locking field. Ignore it as a user.
operator_userint8NOReference to the user who last edited the row
projectint8NOReference to the project that receives access to the tracker information
tracker_ownershipint8NOReference 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
idint8NOUnique identifier of the row
date_reporteddateNODate that the file arrived
filenamevarcharNOName of the file
last_modified_datetimestampNODate that the file was last modified on
device_info_serialint4YESSerial number of the tracker this file comes from
filesizeint8NOSize of the file in KB
parsed_datetimestampYESDate and time that the file was last attempted to be parsed on
versionint4NOOptimistic locking field. Ignore it as a user.
last_found_datetimestampYESLast time that a process that runs to look for new files saw the file
n_linesint4YESCount of lines found in the file last time it was processed
n_data_linesint4YESCount of actual data lines, out of the total lines
n_tracking_recordsint4YESCount of GPS fixes found during the parsing process
n_communication_recordsint4YESCount of communication records found during the parsing process
n_energy_recordsint4YESCount of energy records found during the parsing process
n_acceleration_recordsint4YESCount of acceleration records found during the parsing process
n_acceleration_start_recordsint4YESCount of acceleration start records found during the parsing process
n_errorsint4YESCount of errors encountered during the parsing process
n_settings_recordsint4YESCount of settings records found during the parsing process
filepathtextNODirectory path of the file on the server

gps.uva_trackingfile_messages

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
idserialNOUnique identifier of the row
tracking_bookkeeping_idint4YESReference to the gps.uva_trackingfile_parsing row for this file
message_typeint4YESCode for the type of information being told by the message.
messagetextYESTextual message from the parsing process telling what it found.
message_codeint4YES
line_numberint4YESLine 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_serialint4NOSerial number of the tracker
filenametextNOlog file name
linenumberintegerYESline number in file
warningtextYESThe warning message
linetextYESThe 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 textYES 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 int4YESmilliseconds
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 int2YES TimeSynced (0=not sy:wnced, 1=user set, 2=Gps Synced)
devicemode int2YESDeviceMode (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 int4YES ComIval, current (0-65535)
accii int4YES AcceleroIval, current (0-65535)
swddd int4YES User switches, current (0-65535)
last_gps_fix timestampYEStimestamp of last fix
latitudefloat8YESLatitude of last fix. Decimal degrees
longitudefloat8YESLongitude 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_serialint4NOSerial number of the tracker
date_timetimestampNODate and time of the reported data
timesynced int2YESTimeSynced (0=not synced, 1=user set, 2=Gps synced)
devicemode int2YES(0-99)
vsll float8YESSolar Voltage during charging in mV (0-~2500)
vbat float8YESBattery Voltage in V (0-~4.200)
ssw int2YES Solar Cell Switches ... (0-255) fw1022 and up
YESextended fields if special user switch is on
vsoo float8YES Solar voltage open in V (0-~2.500)
vdrf float8YESreference voltage Dac in V (0-~2.435)
vchr float8YEScharge current voltage
g int2YES Gps battery threshold OK
c int2YESCom battery threshold OK
m int2YESCharge Mode (0,1,2 Com battery threshold OK
pressure int4YES in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa)
temperature float8YES 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_serialint4NO
date_timetimestampNODerived timestamp from Hh:mi fields in C0 combined with computer timestamp in JOINED line
timesynced int2 YES
timezone_time int2 YES
swddd int4YES SpecialUserSwitches
gpsnavmode int2YES 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 int2YES GpsFixTime 0-255
gps_ivaln_on_h int2YES hours Gps IvalN on
gps_ivaln_on_m int2YES minutes Gps IvalN on
gps_ivaln int4YES GpsIvalN (0-65530)
gps_ivale_on_h int2YES hours Gps IvalE on
gps_ivale_on_m int2YES minutes GpS IvalE on
gps_ivale int4YES GpsIvalE
com_ivaln_on_h int2YES hours Com IvalN on
com_ivaln_on_m int2YES minutes Com IvalN on
com_ivaln int4YES ComIvalN (0-65530)
com_ivale_on_h int2YES hours Com IvalE on
com_ivale_on_m int2YES minutes Com IvalE on
com_ivale int4YES ComIvalE
accii int4YES Accelero measurement interval
accbn int4YES Accelero number of blocks
accsn int4YES Accelero number of samples (0-65536)
f int2YESAccelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz)
pdp int2YES PdopMsk (3-250)
pacint2YES PaccMsk (10-100)
tdp int2YES TdopMsk (3-250)
tac int2YES TaccMsk (30-300)
ga int2YES Additional Fixes GpsIval mode (3-25) before interval fix is logged
aol int2YESAccMemOffLev (100 - 950) units 0.1%; if Memfull above this level all Accelero activity will be overruled and switched off untill MemFull < level - 5
eventnum textYES 12 eventnumbers (0-0xff) only printed if not zero, therfore converted to text
Personal tools