Difference between revisions of "DB Tables 2015"

From ecology
Jump to: navigation, search
(→‎Relationship with projects)
(→‎gps.uva_tracking_data101)
 
(16 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
== ''Leaf'' data ==
+
'''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|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 ===
 
=== ''admin.''ee_project_limited ===
Line 174: Line 178:
 
|}
 
|}
  
−
== Relationship with projects ==
+
== 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.
 +
 
 +
{| class="wikitable"
 +
! 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.
 +
 
 +
{| class="wikitable"
 +
! 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.
 +
 
 +
{| class="wikitable"
 +
! 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.
 +
 
 +
{| class="wikitable"
 +
! 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.
 +
 
 +
{| class="wikitable"
 +
! 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 ===
 
=== ''admin.''ee_membership ===
Line 232: Line 401:
 
|}
 
|}
  
−
=== ''admin.''ee_individual_ownership ====
+
=== ''admin.''ee_individual_ownership ===
  
 
Each row represents the fact that an individual belongs to a project within a specified time period.
 
Each row represents the fact that an individual belongs to a project within a specified time period.
Line 282: Line 451:
 
|-
 
|-
 
|tracker_ownership||int8||NO||Reference to the tracker ownership by virtue of which, the tracker information is shared
 
|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.
 +
 +
{| class="wikitable"
 +
! 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 ===
 +
 +
{| class="wikitable"
 +
! 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;
 +
 +
{| class="wikitable"
 +
! 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.
 +
 +
{| class="wikitable"
 +
! 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.
 +
 +
{| class="wikitable"
 +
! 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.
 +
 +
{| class="wikitable"
 +
! 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
 
|}
 
|}

Latest revision as of 09:33, 22 January 2021

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
Retrieved from "https://wiki.e-ecology.nl/index.php?title=DB_Tables_2015&oldid=1435"