Difference between revisions of "Argos Data Model"
(→DEVICE_INFO) |
(→TRACK_SESSION) |
||
| Line 58: | Line 58: | ||
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments''' | |'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments''' | ||
|- | |- | ||
| − | |''id ''||int4 ||PK||NOT NULL||unique ID for each usage of a PTT so test and bird with same ptt_id are two separate IDs/indexes | + | |'''id '''||int4 ||PK||NOT NULL||unique ID for each usage of a PTT so test and bird with same ptt_id are two separate IDs/indexes |
|- | |- | ||
| − | |'' | + | |''pttid''||int4 ||FK||NOT NULL||Link to DEVICE_INFO table |
|- | |- | ||
| − | | | + | |''ring_number''||textint4 ||FK||||link to individual table – tests do not have a ring_number |
|- | |- | ||
|start_date ||timestamptz||||||Date/time (Local) when tracking begins | |start_date ||timestamptz||||||Date/time (Local) when tracking begins | ||
| Line 78: | Line 78: | ||
|time_steps ||text|||||| | |time_steps ||text|||||| | ||
|- | |- | ||
| − | |remarks | + | |remarks||text|||||| |
| − | |||
| − | |||
|- | |- | ||
| + | |location ||geometry||||||postgreSQL geometry | ||
|} | |} | ||
| − | |||
== GPS_TRACKING_DATA == | == GPS_TRACKING_DATA == | ||
Revision as of 13:08, 14 May 2008
Contents
GPS Data Model
Last updated January 22, 2008 Judy Shamoun-Baranes
FLYSAFE TRACKING DATABASE
The tracking database will include data from several species. Some species are trapped and then monitored during the breeding season while others are trapped during the winter season. Information is collected on each tracked bird, including biometric measurements. These measurements may be collected more than once. During the breeding season information is also collected about the gull breeding colony itself, location of nests, state of breeding (number of eggs and juveniles) and then juveniles themselves are marked and measured (although not tracked).
In addition, PTTs are often tested before they are actually placed on the birds. This information is also important during analysis and particularly to establish accuracy of data. This data should also be stored in the database. Therefore it is important that this information is stored properly in the database
Units and descriptions should be filled in for all measurements
One way of looking at this structure is considering 3 categories of tables:
- lookup tables (information stored once)
- session tables (stored occasionally)
- observations – highest frequency of information.
Primary keys are noted by bold italic text. Foreign keys are noted by italic text in the Field column.
Microwave, will under no circumstances provide information about their checksum calculations. If feasible, SARA will duplicate the procedure and include a flag in the representing the microwave filtered data.
TECHNICAL INFORMATION FROM TAGS
DEVICE_INFO
Table including technical information about tag hardware.
| Field | Type | Key | Constraints | Comments |
| ptt_id | int4 | PK | NOT NULL | serial number of PTT provided by Argos and used for communication with Argos satellite |
| serial | int4 | NOT NULL | serial number of tag from manufacturer | |
| weight | int4 | NOT NULL | Unit = grams | |
| type | text | NOT NULL | ||
| manufacturer | text | Device manufacturer | ||
| hexadecimal_id | text | Hexadecimal address, unique for each tag | ||
| PAN_id | Text | Personal area network ground based tags communicates with | ||
| Base_station_id | Int4 | base station ground based tag communicates with | ||
| Remarks | text |
TRACK_SESSION
This table includes information about each measurement session and enables inclusion of data from new species and also from the test phase of different tags
| Field | Type | Key | Constraints | Comments |
| id | int4 | PK | NOT NULL | unique ID for each usage of a PTT so test and bird with same ptt_id are two separate IDs/indexes |
| pttid | int4 | FK | NOT NULL | Link to DEVICE_INFO table |
| ring_number | textint4 | FK | link to individual table – tests do not have a ring_number | |
| start_date | timestamptz | Date/time (Local) when tracking begins | ||
| start_latitude | float8 | NOT NULL | latitude where tracking begins, unit = dec degress | |
| start_longitude | float8 | NOT NULL | longitude where tracking begins, unit = dec degrees | |
| start_altitude | Float8 | Unit = m, include when known, important for testing | ||
| end_date | timestamptz | End of session if known | ||
| time_window | text | |||
| time_steps | text | |||
| remarks | text | |||
| location | geometry | postgreSQL geometry |
GPS_TRACKING_DATA
Tracking data from GPS sensors on PTT/GPS tags
| Field | Type | Key | Constraints | Comments |
| ptt_id | int4 | PK | NOT NULL | |
| date_time | timestamp | PK | NOT NULL | GMT |
| latitude | float8 | Decimal degrees | ||
| longitude | float8 | Decimal degrees | ||
| speed | float8 | m/s | ||
| course | float8 | degrees | ||
| altitude | float8 | m | ||
| alt_status | text | |||
| quality | text | Quality information from sensor | ||
| user_QC | text | Data flagged as unacceptable by user | ||
| parser_QC | text | Data flagged as unacceptable by Microwave parser | ||
| location | geometry | postgreSQL geometry |
ARGOS_TRACKING_DATA
Tracking data from Argos sensors
| Field | Type | Key | Constraints | Comments |
| id | int4 | PK | NOT NULL | autoupdate |
| TRACK_SESSION_id | Int4 | FK | ||
| ptt_id | int4 | NOT NULL | ||
| date_time | timestamp | NOT NULL | GMT | |
| fix | text | |||
| latitude1 | float8 | |||
| longitude1 | float8 | |||
| latitude2 | float8 | |||
| longitude2 | float8 | |||
| msgcount | int4 | |||
| frequency | float8 | |||
| average_ti | float8 | |||
| satellite | text | |||
| max_str | text | |||
| location1 | geometry | postgreSQL geometry | ||
| location2 | geometry | postgreSQL geometry |
PTT_ENGINEERING_DATA
| Field | Type | Key | Constraints | Comments |
| id | int4 | PK | NOT NULL | auto-insert |
| TRACK_SESSION_id | Int4 | FK | ||
| ptt_id | int4 | NOT NULL | ||
| tx_date_time | timestamp | GMT | ||
| ptt_date_time | timestamp | GMT | ||
| satellite_id | text | |||
| activity | int4 | |||
| tx_count | int4 | |||
| temperature | float8 | |||
| battery_voltage | float8 | |||
| gps_fix_time | int4 | |||
| satellite_count | int4 | |||
| hours_reset | int4 | |||
| hours_since_gps_fix | int4 | |||
| mortality_gt | int4 | |||
| seasonal_gt | int4 | |||
| shunt | int4 |
In addition the views GPS_TRACKING_SESSION, ARGOS_TRACKING_SESSION, PTT_ENGINEERING_SESSION are which adds the correct track_session.id to the GPS_TRACKING_DATA, ARGOS_TRACKING_DATA and PTT_ENGINEERING_DATA table data.
UvA_TRACKING_DATA
Tracking data from UvA GPS tags
| Field | Type | Key | Constraints | Comments |
| DEVICE_INFO_serial | int4 | NOT NULL | ||
| date_time | timestamp | NOT NULL | GMT | |
| latitude | float8 | Decimal degrees | ||
| longitude | float8 | Decimal degrees | ||
| h_accuracy | float8 | Horizontal accuracy (m) | ||
| v_accuracy | float8 | Vertical accuracy (m) | ||
| altitude | float8 | m | ||
| x_acceleration | Float8 | x axis acceleration (m/s22) movement to right is + and to left is - | ||
| y_acceleration | Float8 | y axis acceleration (m/s22) forward movement is + and backwards is - | ||
| z_acceleration | Float8 | z axis acceleration (m/s22) upwards movement is + downwards is - | ||
| speed | float8 | m/s | ||
| course | float8 | Degrees | ||
| pressure | float8 | Pressure sensor (pascal) | ||
| temperature | Float8 | Degrees celcius | ||
| solar_voltage | Float8 | V | ||
| battery_voltage | Float8 | V | ||
| quality | text | Data flagged as unacceptable by user | ||
| “"location”" | geometry | postgreSQL geometry |
BIOLOGICAL INFORMATION
INDIVIDUAL
Table includes information used to identify an individual
| Field | Type | Key | Constraints | Comments |
| ring_number | text | PK | NOT NULL | metal ring number |
| Id | int4 | PK | NOT NULL | auto-insert, unique ID for each individual organism |
| NEST_INFO_id | int4 | FK | link to NEST_INFO, not all specimens are linked to a nest | |
| nest_certainty | int4 | 1 = certain, 0 = not completely certain | ||
| species_certainty | int4 | 1 = certain, 0 = not completely certain | ||
| ring_number | text | NOT NULL | metal ring number | |
| color_code | text | code for color ring without punctuation | ||
| species | text | NOT NULL | Scientific name (at least genus) | |
| sex | text | update if or when known | ||
| FATE_CODEE_id | textInt4 | FK | Link to FATE_CODE | |
| Remarks | text |
FATE_code
| Field | Type | Key | Constraints | Comments |
| Id | Int4 | PK | auto-insert | |
| code | int4 | PK | NOT NULL | |
| description | text |
MEASUREMENT
Metadata Provided by Sovon is needed to describe how these measurements are taken.
| Field | Type | Key | Constraints | Comments |
| id | int4 | PK | NOT NULL | auto-insert, unique ID for each observation |
| ring_numberINDIVIDUAL_id | textInt4 | FK | link to individual | |
| FATE_CODE_id | -Int4- | -FK- | --link to FATE_CODES-- | |
| catch_datetime | timestamptz | Local time | ||
| release_datetime | timestamptz | Local time | ||
| mass | float8 | grams | ||
| age | text | AD = adult, JUV = juvenile | ||
| wing_length | float8 | |||
| bill_length1 | float8 | |||
| bill_length2 | float8 | |||
| bill_height1 | float8 | |||
| bill_height2 | float8 | |||
| tarsus_length | float8 | |||
| total_head | float8 | |||
| observer | text | |||
| remarks | text |
Catch_datetime & release_datetime
During ringing of juveniles only the date was recorded and not the time. Birds were all released the same day. Ringing and measuring small chicks in the nest took about 2-5 minutes. Colour ringing and sampling of the larger chicks (from 4 July onwards) took about half an hour (rounding up several chicks and banding, sampling and releasing them together). To avoid leaving fields blank, a standard catch time was noted at 12:00 and a release time of 12:15 before July 4 and 12:30 from July 4 and onwards. 'comments by Kees Oosterbeek'
NEST_INFO
| Field | Type | Key | Constraints | Comments |
| id | int4 | PK | NOT NULL | |
| Species | text | |||
| latitude | float8 | NOT NULL | Decimal degrees | |
| longitude | float8 | NOT NULL | Decimal degrees | |
| year | Int4 | Year of breeding season | ||
| "location" | geometry | postgreSQL geometry | ||
| Remarks | text |
NEST_OBSERVATION
| Field | Type | Key | Constraints | 'Comments' |
| id | int4 | PK | NOT NULL | Auto-insert, unique ID for each observation |
| NEST_INFO_id | int4 | FK | NOT NULL | |
| date | timestamptz | NOT NULL | ||
| number_egg | int4 | |||
| number_young | int4 | |||
| pipping | text | Activity in egg (peeping or signs of hatching) | ||
| attended | text | Activity on nest | ||
| observer | text | |||
| remarks | text |
Comments:
Graphical Data Model
