DB Model 2014
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
Contents
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. |
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 |
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.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). |
Other 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 |
| 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 |
gps. ee_species
Each row represents a species.
| 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
Each row represents a bird.
| 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_tracker
Each row represents a tracker.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key. |
| device_info_serial | int8 | NO | Serial number of the product |
| 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 |
| x_s | numeric(30,6) | YES | Accelerometer calibration |
| y_o | numeric(30,6) | YES | Accelerometer calibration |
| y_s | numeric(30,6) | YES | Accelerometer calibration |
| z_o | numeric(30,6) | YES | Accelerometer calibration |
| z_s | numeric(30,6) | YES | Accelerometer calibration |
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
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 |
