DB Model 2014

From ecology
Revision as of 07:03, 1 August 2014 by Andera (talk | contribs) (gps.uva_comm101 (table))
Jump to: navigation, search

NOTE: THIS PAGE IS STILL ACTIVELY BEING EDITED. Many things may and will change in the next days. Please visit it regularly to have the latest information.

From the users' perspective, the database is split into two 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 the administration users of e-Ecology.

The following tables are available

Tracking data

Track sessions

A track session links a tracker and a bird for a given period of time. Therefore, it is mandatory for projects to supply this piece of metadata before they can see any tracking data related to a tracker they own.

In order to create a track session, the project must create first an entry in the system for the bird that they are attaching the tracker to, and this is the way for the UvA-BiTS community to make sense of the tracking data that we are receiving. This is why providing the metadata is so important.

By editing the start and end dates, administrators of the project that this track session belongs to, can delimit (and therefore, establish) when the coupling makes sense (or is to be understood as useful).

Whenever a tracker is attached to a bird, a track session must be started to indicate that the tracking data that is received is already live data. If the tracker is separated from the bird, the tracking data ceases to make sense, so the track session's end date should be set to denote it has ended. New track sessions can be started if the tracker is re-attached to a bird (possibly the same as before, or maybe a different one) afterwards.

gps.ee_track_session (table)

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

gps.ee_track_session_limited (view)

Shows information of the track sessions that your memberships allow you to see.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
track_session_id int8 NO Unique identifier of the track session.
key_name varchar NO The project that the track session belongs to.
device_info_serial int8 NO Tracker serial number of the tracker this track session involves.
ring_number varchar NO Ring number of the bird that this track session involves.
track_session_start_date timestamp NO Date before which the track session is not relevant
track_session_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.

gps.ee_shared_track_session_limited (view)

Shows information of the track sessions that other projects are sharing with your projects and within which dates.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
track_session_id int8 NO Unique identifier of the track session.
key_name varchar NO The project that allows you to see the track session (the one it has been shared with).
device_info_serial int8 NO Tracker serial number of the tracker this track session involves.
ring_number varchar NO Ring number of the bird that this track session involves.
track_session_start_date timestamp NO Date before which the track session is not relevant
track_session_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 the track session has been shared with. 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.
sharing_start_date timestamp NO Begin of the time period that this track session has been shared with the project that allows you to see it.
sharing_end_date timestamp NO End of the time period that this track session has been shared with the project that allows you to see it.
sharing_project_key_name varchar NO Key name of the project that owns the track session (and which is sharing it with the project that allows you to see it).
sharing_project_id int8 NO Reference to the project that owns the track session (and which is sharing it with the project that allows you to see it). Included just to enable joins.

Tracking fixes, speeds and accelerations

From the GPS tracker, we receive data files that are parsed and brought into the database. This gives us the GPS fixes and component speeds. Some calculations are made to also provide the 3D speed and the linear speed next to the GPS fix.

From the accelerometers, we receive accelerations from the different acceleration-vector components.

gps.uva_tracking_data101 (table)

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_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

gps.uva_tracking_data (table)

Each row represents a GPS fix as it is interpreted from the data that comes via the GPS tracker. For trackers with firmware version under V.1.0.1.4.

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.
engineering_date_time timestamp YES
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
speed_3d_accuracy float8 YES meters/second
x_acceleration float8 YES x axis acceleration (m/s22) movement to right is + and to left is -
y_acceleration float8 YES y axis acceleration (m/s22) forward movement is + and backwards is -
z_acceleration float8 YES z axis acceleration (m/s22) upwards movement is + and downwards is -
heading float8 YES
heading_accuracy float8 YES
solar_voltage float8 YES Solar voltage at the time of the measurement in volts
battery_voltage float8 YES Battery voltage at the time of the measurement in volts
gps_fixtime float8 YES GPS fix time (also known as Time to Fix) in seconds
gps_fixstatus text YES 2D = latitude and longitude measured; 3D= latitude, longitude and altitude measured; nofix_power = no GPS measurement
userflag int4 YES Acceptable = 0; Data flagged as unacceptable by user if not equal to 0.
location geometry YES postgreSQL geometry; to allow GIS operations

gps.ee_tracking_speed_limited (view)

Shows the tracking data that your memberships 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_shared_tracking_speed_limited (view)

Shows the tracking data for trackers that other projects have shared with your projects.

It has the same fields as gps.ee_tracking_speed_limited.

gps.uva_acc_start102 (table)

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.ee_acc_start_limited (view)

Shows the acceleration start data for trackers that your memberships allow you to see.

It has the same fields as gps.uva_acc_start102.

gps.ee_shared_acc_start_limited (view)

Shows the acceleration start data for trackers that other projects have shared with your projects.

It has the same fields as gps.ee_acc_start_limited.

gps.uva_acceleration101 (table)

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_acceleration_limited (view)

Shows the acceleration measurements data for trackers that your memberships allow you to see.

It has the same fields as gps.uva_acceleration101.

gps.ee_shared_acceleration_limited (view)

Shows the acceleration measurements data for trackers that other projects have shared with your projects.

It has the same fields as gps.ee_acceleration_limited.

Bird data

Birds

gps.ee_species (table)

Each row represents a species.

Each bird now holds a reference to a record in this table. Only UvA-BiTS administrators will maintain the catalogue of valid species, so if you need to add a bird for which a species is not yet in the system, you must contact them. This way we ensure that the species' names are consistent.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
id int8 NO Unique identifier. Primary key.
abbr varchar NO Abbreviation of the species. Should be unique. Used as the prefix of the project's key name.
english_name varchar YES Name in English
latin_name varchar NO Name in Latin
version int4 YES Optimistic locking field. Ignore it as a user.

gps.ee_individual (table)

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 (ideally, in 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_individual_limited (view)

Each row represents bird's details that your memberships allow you to see.

This view replaces the old gps.uva_individual_limited.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
key_name varchar NO Project's key name that allows you to see the bird.
ring_number varchar NO Ring number.
species 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 (ideally, in 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

gps.ee_shared_individual_limited (view)

Each row represents bird's details that other projects have shared with projects you belong to.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
key_name varchar NO Project's key name that allows you to see the bird.
ring_number varchar NO Ring number.
species 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 (ideally, in grams)
remarks text YES Text for user remarks
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.
sharing_start_date timestamp NO Date when the sharing period begins.
sharing_end_date timestamp NO Date when the sharing period ends.

Extra data

Administrative data

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.

admin.ee_project

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_membership

Each row represents the fact that a user has a given role in a given project for a given period of time.

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_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

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

gps.ee_tracker_sharing (table)

Projects can now share tracking information from specific time periods with other projects. They can do so by creating a sharing of a tracker they own with another tracker. They can share the same tracker with the same project in multiple different periods.

You can only share trackers you own and within the time periods that your tracker belongs to you.

The motivation here is to let projects decide which trackers to share on the http://uva-bits.nl website. For example, you may want to allow the general public to see your birds' tracking data for the whole year every year except spring, because you may want to hide your birds' nest location. If you want to show a tracker on the website then you need to share it with the project whose key name is WWW. Then, if it's the first time you are going to show a tracker on the public website, contact the UvA-BiTS administrators so that they prepare the dynamic map for your project.

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

gps.ee_tracker_sharing_limited (view)

You can see the trackers you are sharing and the time periods.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
giver_key_name varchar NO Key name of the project that allows you to see this sharing, and that is actually sharing a tracker with some other project
borrower_key_name varchar NO Key name of the project that the tracker is being shared with
device_info_serial int8 NO Serial number of the tracker that is being shared
tracker_sharing_start_date timestamp NO Date at which the tracker begins to be shared
tracker_sharing_end_date timestamp NO Date at which the tracker stops being shared
tracker_sharing_id int8 NO Unique identifier of the tracker sharing. Included just to allow joins.
tracker_ownership_id int8 NO Reference to the tracker ownership that links the tracker and the giver project. Included just to allow joins.

Engineering data

gps. ee_tracker (table)

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 (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

gps.ee_tracker_limited (view)

Each row represents tracker's details that your memberships allow you to see.

This view replaces the old gps.uva_device_limited.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
key_name varchar NO Key name of the project that allows you to see this tracker.
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
ownership_start_date timestamp NO Date at which the tracker information becomes available to the project that allows you to see the tracker
ownership_end_date timestamp NO Date at which the tracker information ceases to be available to the project that allows you to see the tracker
project_id int8 NO Reference to the project this tracker belongs to. Included just to enable joins.
tracker_id int8 NO Tracker unique id. Included just to enable joins.

gps.ee_shared_tracker_limited (view)

Shows the tracker data for trackers that other projects have shared with your projects.

COLUMN_NAME TYPE_NAME IS_NULLABLE DESCRIPTION
key_name varchar NO Key name of the project that allows you to see this tracker.
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
sharing_start_date timestamp NO Date at which the tracker information becomes available to the project that allows you to see the tracker
sharing_end_date timestamp NO Date at which the tracker information ceases to be available to the project that allows you to see the tracker
sharing_project_key_name varchar NO Key name of the project that is sharing the tracker with you.
sharing_project_id int8 NO Reference to the project that is sharing the tracker with you. Included just to enable joins.
project_id int8 NO Reference to the project this tracker belongs to. Included just to enable joins.
tracker_id int8 NO Tracker unique id. Included just to enable joins.

gps.uva_comm101 (table)

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.ee_comm_limited (view)

Shows the communication data for trackers that your memberships allow you to see.

It has the same fields as gps.uva_comm101.

It replaces the old gps.uva_comm_limited view.