UvAGPS
Contents
- 1 UvA-BiTS Data Model
- 1.1 Tracking Data
- 1.1.1 UVA_TRACK_SESSION or UVA_TRACK_SESSION_LIMITED (Table and View)
- 1.1.2 UVA_TRACKING or UVA_TRACKING_LIMITED (View)
- 1.1.3 UVA_ACCELERATION101 (Table) or UVA_ACCELERATION_LIMITED (View)
- 1.1.4 UVA_TRACKING_SPEED or UVA_TRACKING_SPEED_LIMITED (View)
- 1.1.5 UVA_TRACKING_DATA (Table)
- 1.1.6 UVA_TRACKING_DATA101 (Table)
- 1.2 Bird Data
- 1.3 Other Data (Engineering, calibration, database administration and data quality)
- 1.3.1 UVA_DEVICE or UVA_DEVICE_LIMITED (Table and View)
- 1.3.2 UVA_ACC_START102 (Table) or UVA_ACC_START_LIMITED (View)
- 1.3.3 UVA_ENGINEERING_DATA (Table)
- 1.3.4 UVA_COMM101 (Table) or UVA_COMM_LIMITED (View)
- 1.3.5 UVA_SETTINGS101 (Table) or UVA_SETTINGS_LIMITED (View)
- 1.3.6 UVA_ENERGY101 (Table) or UVA_ENERGY_LIMITED (View)
- 1.3.7 UVA_WARNING102 (Table) or UVA_WARNING_LIMITED (View)
- 1.3.8 uva_access_device (Table) & uva_access_individual (Table)
- 1.3.9 uva_trackingfile_messages (Table) & uva_trackingfile_parsing (Table)
- 1.1 Tracking Data
- 2 UvAGPS functions
- 3 Accelerometer calibration
- 4 Data processing and filtering
- 5 Information about GPS receivers
UvA-BiTS Data Model
All tables, views and functions related to UvA-BiTS are in the GPS schema in the flysafe database and begin with the prefix "uva_". The tables/views that do not begin with "uva_" are related to a different project.
Not everybody has access to all data. Most users will only be granted access to views with the suffix "_limited" that show data collected with devices specific to their project(s).
The difference between a Table and a View: The difference is actually quite small from the user perspective. The same data can be queried and viewed in the same way in both tables and views. However, the data stored in a table can also be updated, deleted and changed by people with administrative privileges. The purpose of a view is to allow users who only want to view and/or query the data to do so without any possibility of altering the data. For this reason, we recommend that most users access the data through the Views. In addition, views often contain valuable additional data calculated from the raw tracking data.
Where to find the basic tracking data: Depending on the version of UvA-BiTS firmware installed in the GPS device, different database tables/views are used to store tracking data:
- UVA_TRACKING view: Created to make it easier for most users to view/query basic tracking data. All tracking data for all types of firmware are pulled from the different tables and seen/queried in this one view.
- UVA_TRACKING_DATA table: Used to store tracking data collected with devices running firmware version v0.9.3.6 till v1.0.0.X.
- UVA_TRACKING_DATA101 table: Introduced to the database to store the data collected with the newest firmware (versions 1.0.1.4 till 1.0.2.4) because the new version is able to collect so much more information.
The tables/views in the UvA-BiTS database are described in detail in the sections below. Each table/view has been placed into one of three sections depending on the type of data they contain:
- Tracking Data: Primary tracking data that is of most interest to most users (i.e. device numbers, lat/long, x/y/z acceleration, altitude, etc.).
- Bird Data: Data about the birds on which the devices are mounted (species, sex, etc.).
- Other Data: Data used for administrative, engineering, calibration or data quality purposes and therefore usually not commonly used by most users.
Tracking Data
UVA_TRACK_SESSION or UVA_TRACK_SESSION_LIMITED (Table and View)
This is essential metadata about GPS tag deployment that is provided by the project manager when the tag is first mounted on a bird. 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. To avoid analyzing data that was collected during testing, you should always include this table/view in your query as a way to select data that was measured after the start_date of a particular track session.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | integer | FK | NOT NULL | Serial number of GPS tag |
| ring_number | text | FK | NOT NULL | Ring number of individual bird. This field is what links this table to the UVA_INDIVIDUAL table. Tests do not have a ring_number. |
| start_date | timestamp | Timestamp without time zone (UTC) when tracking begins in yyyy-mm-dd hh:mm:ss. | ||
| end_date | timestamp | Timestamp without time zone (UTC) when tracking session ends, if known, in yyyy-mm-dd hh:mm:ss. This field will be blank if the project is still ongoing. | ||
| start_latitude | double precision | NOT NULL | Latitude where tracking begins, unit = dec degrees | |
| start_longitude | double precision | NOT NULL | Longitude where tracking begins, unit = dec degrees | |
| project_leader | text | Project leader and point of contact for questions about data | ||
| remarks | text | Optional field for project manager to add comments. |
UVA_TRACKING or UVA_TRACKING_LIMITED (View)
Created to make it easier for most users to view/query all tracking data. All tracking data for all types of firmware are pulled from different data tables (UVA_TRACKING_DATA table & UVA_TRACKING_DATA101 table) and seen/queried in this one view.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | integer | PK | NOT NULL | Serial number of GPS tag |
| date_time | timestamp | PK | NOT NULL | Timestamp of GPS measurement without time zone (UTC) |
| latitude | float | Latitude measured by GPS tag for this record, unit = dec degrees | ||
| longitude | float | Longitude measured by GPS tag for this record, unit = dec degrees | ||
| altitude | float | Altitude above sea level measured by GPS tag in meters | ||
| pressure | integer | Pressure measured by GPS tag sensor in Pascals | ||
| temperature | float | Temperature measured by GPS tag sensor in Celsius | ||
| h_accuracy | float | Horizontal accuracy (meters) | ||
| v_accuracy | float | Vertical accuracy (meters) | ||
| x_speed | float | meters/second | ||
| y_speed | float | meters/second | ||
| z_speed | float | meters/second | ||
| gps_fixtime | float | GPS fix time (also known as Time to Fix) in seconds | ||
| userflag | integer | Acceptable = 0; Data flagged as unacceptable by user if not equal to 0. | ||
| satellites_used | smallint | Number of satellites used for fix the GPS measurement | ||
| positiondop | float | Positional DOP (Dilution of Precision), unitless | ||
| speed_accuracy | float | |||
| location | geometry | postgreSQL geometry |
UVA_ACCELERATION101 (Table) or UVA_ACCELERATION_LIMITED (View)
Acceleration entries if accelerometer was turned on. Only for newer firmware. Actual measurements have shown that the range of values for x,y and z are larger then indicated by the manufacturer. For more information about calibration and interpretation of accelerometer data see Accelerometer calibration.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | integer | PK | NOT NULL | Serial number of GPS tag |
| date_time | timestamp | PK | NOT NULL | Timestamp of measurement without time zone (UTC) |
| index | smallint | PK | NOT NULL | Row index (from 0) of accelerometer measurement series |
| x_acceleration | smallint | Acceleration in X-axis (-2666 till 2666) | ||
| y_acceleration | smallint | Acceleration in Y-axis (-2666 till 2666) | ||
| z_acceleration | smallint | Acceleration in Z-axis (-2666 till 2666) |
UVA_TRACKING_SPEED or UVA_TRACKING_SPEED_LIMITED (View)
Additional view that computes the vnorth, veast and vdown speed components as well as the 3D speed in meters/second. These fields are calculated using components from UVA_TRACKING.
In addition to the fields in the UVA_TRACKING view, this view adds :
| Field | Type | Key | Constraints | Comments |
| vnorth | float | - vx * sin(lat) * cos(long) - vy * sin(lat) * sin (long) + vz * cos(lat) | ||
| veast | float | - vx * sin(lon) + vy * cos(lon) | ||
| vdown | float | - vx * cos(lat) * cos(lon) - vy * cos(lat) * sin(lon) - vz * sin(lat) | ||
| speed | float | Sqrt(vnorth^2 + veast^2) | ||
| speed3d | float | Sqrt(vnorth^2 + veast^2 + vdown^2) | ||
| direction | float | Heading in degrees; 0 is north, 90 is east |
UVA_TRACKING_DATA (Table)
Tracking data from UvA GPS tags using older firmware. All of this data is also brought into the UVA_TRACKING view as described above.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | integer | PK | NOT NULL | Serial number of GPS tag |
| date_time | timestamp | PK | NOT NULL | Timestamp without time zone in UTC |
| latitude | double precision | Latitude measured by GPS tag for this record, unit = dec degrees | ||
| longitude | double precision | Longitude measured by GPS tag for this record, unit = dec degrees | ||
| h_accuracy | double precision | Horizontal accuracy (meters) | ||
| v_accuracy | double precision | Vertical accuracy (meters) | ||
| altitude | double precision | Altitude above sea level measured by GPS tag in meters | ||
| x_acceleration | double precision | x axis acceleration (m/s22) movement to right is + and to left is - | ||
| y_acceleration | double precision | y axis acceleration (m/s22) forward movement is + and backwards is - | ||
| z_acceleration | double precision | z axis acceleration (m/s22) upwards movement is + and downwards is - | ||
| x_speed | double precision | Speed in x-axis (m/s) | ||
| y_speed | double precision | Speed in y-axis (m/s) | ||
| z_speed | double precision | Speed in z-axis (m/s) | ||
| speed_3d | double precision | meters/second | ||
| heading | double precision | |||
| heading_accuracy | double precision | |||
| pressure | double precision | Pressure measured by GPS tag sensor in Pascals | ||
| temperature | double precision | Temperature measured by GPS tag sensor in degrees Celsius | ||
| solar_voltage | double precision | Solar voltage at the time of the measurement in volts | ||
| battery_voltage | double precision | Battery voltage at the time of the measurement in volts | ||
| gps_fixstatus | text | 2D = latitude and longitude measured; 3D= latitude, longitude and altitude measured; nofix_power = no GPS measurement | ||
| gps_fixtime | double precision | GPS fix time (also known as Time to Fix) in seconds | ||
| location | geometry | postgreSQL geometry | ||
| userflag | integer | Acceptable = 0; Data flagged as unacceptable by user if not equal to 0. |
UVA_TRACKING_DATA101 (Table)
The tracking data for newer firmware (V 1.0.1.4 and higher). All of this data is also brought into the UVA_TRACKING view described above.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | integer | PK | NOT NULL | Serial number of GPS tag |
| date_time | timestamp | PK | NOT NULL | Timestamp without time zone in UTC |
| latitude | double precision | Latitude measured by GPS tag for this record, unit = dec degrees | ||
| longitude | double precision | Longitude measured by GPS tag for this record, unit = dec degrees | ||
| altitude | integer | Altitude above sea level measured by GPS tag in meters | ||
| pressure | integer | Pressure measured by GPS tag sensor in Pascals (1mBar = 100Pa, 1pressuremeter = ~12Pa) | ||
| temperature | double precision | Temperature measured by GPS tag sensor in degrees Celsius | ||
| satellites_used | smallint | Number of satellites used for fix the GPS measurement | ||
| gps_fixtime | double precision | GPS fix time (also known as Time to Fix) in seconds | ||
| positiondop | double precision | Positional DOP (Dilution of Precision), unitless | ||
| h_accuracy | double precision | Horizontal accuracy (meters) | ||
| v_accuracy | double precision | Vertical accuracy (meters) | ||
| x_speed | double precision | Speed in x-axis (m/s) | ||
| y_speed | double precision | Speed in y-axis (m/s) | ||
| z_speed | double precision | Speed in z-axis (m/s) | ||
| speed_accuracy | double precision | Speed accuracy (m/s) | ||
| userflag | integer | Acceptable = 0; Data flagged as unacceptable by user if not equal to 0. | ||
| location | geometry | postgreSQL geometry |
Bird Data
UVA_INDIVIDUAL or UVA_INDIVIDUAL_LIMITED (Table and View)
Table includes important information used to identify an individual tracked with a GPS tag. Remarks can also include a URL linking to a file with additional information about the individual (eg additional biometric measurements)
| Field | Type | Key | Constraints | Comments |
| ring_number | text | PK | NOT NULL | metal ring number |
| color_ring | text | code for color ring without punctuation | ||
| species | text | NOT NULL | Scientific name of species | |
| sex | text | update if or when known | ||
| mass | float | mass of bird at start of tracking session | ||
| Remarks | text | If additional information of this individual is available please provide the file with relevant data (e.g. biometric data). This field should include the file name and extension |
UVA_NEST or UVA_NEST_LIMITED (Table and View)
Table includes information, if available, used to identify the nest of an individual tracked with a GPS tag.
| Field | Type | Key | Constraints | Comments |
| ring_number | text | FK | NOT NULL | metal ring number |
| nest_id | text | FK | individual code for nest or territory | |
| date_time | timestamp | Timestamp without time zone (UTC) when nest first marked yyyy-mm-dd hh:mm:ss | ||
| latitude | float | latitude of nest or territory (decimal degrees) | ||
| longitude | float | longitude of nest or territory (decimal degrees) | ||
| Remarks | text | If additional breeding information is available please provide the file with relevant data. This field should include the file name and extension |
Other Data (Engineering, calibration, database administration and data quality)
UVA_DEVICE or UVA_DEVICE_LIMITED (Table and View)
Different GPS tags have been used over the years. The UVA_DEVICE table was created to be able to track the different firmware versions and specific properties for the different devices. This table also includes essential information needed to calibrate the accelerometers. For more information see the section below called Accelerometer calibration. For more information about the different tags, see the section below called Information about GPS receivers.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | integer | PK | NOT NULL | Serial number of tag |
| firmware_version | text | NOT NULL | Firmware version installed on tag | |
| device_mass | double | Mass of tag (g) | ||
| x_s | double | Calibration sensitivity of accelerometer in the x-axis | ||
| y_s | double | Calibration sensitivity of accelerometer in the y-axis | ||
| z_s | double | Calibration sensitivity of accelerometer in the z-axis | ||
| x_o | double | Calibration offset of accelerometer in the x-axis | ||
| y_o | double | Calibration offset of accelerometer in the y-axis | ||
| z_o | double | Calibration offset of accelerometer in the z-axis |
UVA_ACC_START102 (Table) or UVA_ACC_START_LIMITED (View)
General acceleration entry associated with a set of accelerations. Added in Firmware 1.0.2.7 because there line numbers were introduced for acceleration lines so that these lines can be tracked.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | int4 | PK | NOT NULL | |
| date_time | timestamp | PK | NOT NULL | |
| line_counter | integer | Number of lines associated with this set of accelerations | ||
| timesynced | smallint | TimeSynced (0=not synced, 1=user set, 2=Gps Synced) | ||
| accii | integer | Accelero measurement interval | ||
| accsn | integer | Number of samples (0-65536) | ||
| f | smallint | Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz) |
UVA_ENGINEERING_DATA (Table)
Engineering data from UvA GPS tags for older firmware (up to and including V 1.0.0.3). These tags used the UBlox gps chip LEAS4S.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | int4 | PK | NOT NULL | |
| date_time | timestamp | PK | NOT NULL | UTC |
| t_syncsts | smallint | 0: not synced, 1: user synced, 2: Gps synced | ||
| t_entzt | smallint | 0: UCT time,1: TZT time (timezone time) | ||
| gzp_currentrategps | int4 | |||
| gzp_currentratecom | int4 | |||
| gzp_currentratepwr | int4 | |||
| sbm_voltagesolarpanel | float8 | |||
| sbm_voltagebattery | float8 | |||
| sbm_chargemode | int2 | |||
| egzp_mainrategps | integer | |||
| egzp_mainratecom | integer | |||
| egzp_mainratepwr | integer | |||
| smg_on | text | |||
| smg_off | text | |||
| smg_esavegpsrate | integer | |||
| smz_on | text | |||
| smz_off | text | |||
| smz_esavecomrate | integer | |||
| gpsnavigationmode | smallint | 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 UBlox chip LEAS4S: 1 = stationary, 2 = pedestrian, 3 = automotive, 4 = sea, 5 = airborne 1G, 6 = airborne 2G, 7 = airborne 3G | ||
| gpsfixtimeout | smallint | |||
| eh | text |
UVA_COMM101 (Table) or UVA_COMM_LIMITED (View)
Communication table indicates status and result of communications with ground stations. Only for newer firmware (V 1.0.1.4 and higher).
| Field | Type | Key | Constraints | Comments |
| device_info_serial | integer | PK | NOT NULL | |
| date_time | timestamp | PK | NOT NULL | timestamp of start communication |
| date_time_end | timestamp | timestamp of end communication | ||
| date_time_utc | timestamp | date_time in utc (if available) | ||
| date_time_end_utc | timestamp | date_time_end in utc (if available) | ||
| action | text | JOINED / RESCHEDULE ?? | ||
| bytes_communicated | integer | Number of bytes communicated (total of all bytes per DATA line) | ||
| status | text | READY, TIMEOUT, LOWPOWER, MISSING | ||
| network_join_time | integer | milliseconds | ||
| number_identical_lines | integer | Number identical lines in data block. Gives indication | ||
| non_ascii_char | integer | Number of non ascii characters (indicated by question-mark ?) | ||
| sts_time | timestamp | derived from S0 hh,mi | ||
| timesynced | smallint | TimeSynced (0=not sy:wnced, 1=user set, 2=Gps Synced) | ||
| devicemode | smallint | 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 | float | Battery voltage in V (0-~4.200V) | ||
| logb | integer | logged bytes, waiting for download (0-4Mbyte) | ||
| logp | smallint | Logged percentage (units 0.1%) of memory waiting to be downloaded (0-1000) | ||
| gpsii | integer | GpsIval, current (0-65535) | ||
| comii | integer | ComIval, current (0-65535) | ||
| accii | integer | AcceleroIval, current (0-65535) | ||
| swddd | integer | User switches, current (0-65535) | ||
| last_gps_fix | timestamp | timestamp of last fix | ||
| latitude | float | Latitude of last fix. Decimal degrees | ||
| longitude | float | Longitude of last fix. Decimal degrees |
UVA_SETTINGS101 (Table) or UVA_SETTINGS_LIMITED (View)
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 no new records needs to be added If uplink settings are parsed wait till the last setting has been seen, (DL command or EOF) then store all the new settings in one record. Only for newer firmware.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | int4 | PK | NOT NULL | |
| date_time | timestamp | PK | NOT NULL | Derive timestamp from Hh:mi fields in C0 combined with computer timestamp in JOINED line |
| timesynced | smallint | |||
| timezone_time | smallint | |||
| swddd | integer | SpecialUserSwitches | ||
| gpsnavmode | smallint | 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 | smallint | GpsReceiverNode 0-3 | ||
| gpsfixtime | smallint | GpsFixTime 0-255 | ||
| gps_ivaln_on_h | smallint | hours Gps IvalN on | ||
| gps_ivaln_on_m | smallint | minutes Gps IvalN on | ||
| gps_ivaln | integer | GpsIvalN (0-65530) | ||
| gps_ivale_on_h | smallint | hours Gps IvalE on | ||
| gps_ivale_on_m | smallint | minutes GpS IvalE on | ||
| gps_ivale | integer | GpsIvalE | ||
| com_ivaln_on_h | smallint | hours Com IvalN on | ||
| com_ivaln_on_m | smallint | minutes Com IvalN on | ||
| com_ivaln | integer | ComIvalN (0-65530) | ||
| com_ivale_on_h | smallint | hours Com IvalE on | ||
| com_ivale_on_m | smallint | minutes Com IvalE on | ||
| com_ivale | integer | ComIvalE | ||
| accii | integer | Accelero measurement interval | ||
| accbn | integer | Accelero number of blocks | ||
| accsn | integer | Accelero number of samples (0-65536) | ||
| f | smallint | Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz) | ||
| pdp | smallint | PdopMsk (3-250) | ||
| pac | smallint | PaccMsk (10-100) | ||
| tdp | smallint | TdopMsk (3-250) | ||
| tac | smallint | TaccMsk (30-300) | ||
| ga | smallint | Additional Fixes GpsIval mode (3-25) before interval fix is logged | ||
| aol | smallint | 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 | 12 eventnumbers (0-0xff) only printed if not zero, therfore converted to text |
UVA_ENERGY101 (Table) or UVA_ENERGY_LIMITED (View)
Only for newer firmware.
| Field | Type | Key | Constraints | Comments |
| device_info_serial | int4 | PK | NOT NULL | |
| date_time | timestamp | PK | NOT NULL | |
| timesynced | smallint | TimeSynced (0=not synced, 1=user set, 2=Gps synced) | ||
| devicemode | smallint | (0-99) | ||
| vsll | float | Solar Voltage during charging in mV (0-~2500) | ||
| vbat | float | Battery Voltage in V (0-~4.200) | ||
| ssw | smallint | Solar Cell Switches ... (0-255) fw1022 and up | ||
| extended fields if special user switch is on | ||||
| vsoo | float | Solar voltage open in V (0-~2.500) | ||
| vdrf | float | reference voltage Dac in V (0-~2.435) | ||
| vchr | float | charge current voltage | ||
| g | smallint | Gps battery threshold OK | ||
| c | smallint | Com battery threshold OK | ||
| m | smallint | Charge Mode (0,1,2 Com battery threshold OK | ||
| pressure | integer | in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa) | ||
| temperature | float | in celcius degrees |
UVA_WARNING102 (Table) or UVA_WARNING_LIMITED (View)
Since firmware version 1.0.2.7 this table was added as catchall for tags 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;
| Field | Type | Key | Constraints | Comments |
| device_info_serial | integer | PK | NOT NULL | |
| filename | text | PK | NOT NULL | UvAGPS log filename |
| linenumber | integer | PK | NOT NULL | linenumber in file |
| warning | text | The warming message or tag | ||
| line | text | The actual line in the file |
uva_access_device (Table) & uva_access_individual (Table)
To regulate the access to bird trackers and individuals, access is granted through these tables. These tables grant the rights to see tracking devices and birds, respectively. Only individuals with administrative privileges are allowed to work with these tables. The data entered into these tables produces the "_limited" tables and views.
uva_trackingfile_messages (Table) & uva_trackingfile_parsing (Table)
These two tables are produced as the raw data is parsed and stored in the database. Only individuals with administrative privileges are allowed to view or work with these tables.
UvAGPS functions
get_uvagps_track_speed
Compute distance (meters), time-interval (time stamp hh:mm:ss), speed (meters/second) and heading (degrees) for a series of subsequent tracking entries with specific device_info_serial. For the first entry in the sequence these values will always be missing because they are computed as the difference between subsequent rows.
Usage examples :
select * from gps.get_uvagps_track_speed(119, '2008-01-01 00:00:00', '2010-01-01 00:00:00'); select * from gps.get_uvagps_track_speed(119); select t.*, a.distance, a.interval, a.speed, a.direction from gps.get_uvagps_track_speed(119, '2008-01-01 00:00:00', '2010-01-01 00:00:00') a join gps.uva_tracking t using(device_info_serial, date_time);
Distances and heading are calculated with spherical correction with the Haversine formula as described here. The heading is the initial heading. PostGIS does not calculate the heading, so therefore we don't use PostGIS.
In these functions the records that have been flagged as invalid by setting the userflag to 1 are not taken into account and discarded. If speed calculations are required for all records an additional boolean argument get raw data can be given. i.e.
select * from gps.get_uvagps_track_speed(119, '2008-01-01 00:00:00', '2010-01-01 00:00:00', true); select * from gps.get_uvagps_track_speed(119, true);
get_uvagps_track_distance
Compute distance (meters) for UvAGPS tracks to arbitrary point in lat/long or geometry location
Example usage :
select * from gps.get_uvagps_track_distance(119, 52.1897017, 6.1996606); select * from gps.get_uvagps_track_distance(119, (select location from gps.uva_tracking where device_info_serial = 119 and date_time = '2009-05-07 19:55:08'));
Here the distance is calculated with the PostGIS distance_sphere function which uses spherical correction.
get_uvagps_track_distance_direction
Compute distance (meters) and direction (degrees) for UvAGPS tracks from arbitrary point in lat/long or geometry location
Example usage:
select * from gps.get_uvagps_track_distance_direction(119, 52.1897017, 6.1996606);
Distances and direction are calculated with spherical correction with the Haversine formula as described here. The direction is the direction from the given point.
In this function the records where the userflag has been set are discarded. To see these records a additional boolean argument for show raw data should be added like :
select * from gps.get_uvagps_track_distance_direction(119, 52.1897017, 6.1996606, true);
Accelerometer calibration
The accelerometer measures the acceleration in 3D (20 times per second). Acceleration is caused by gravity (static component) and by a change in speed (dynamic component). When the logger is static in horizontal position (solar cells up), gravity (green arrow) shows a heave (vertical acceleration, z) of +1 g (1 g = 9.81 m.s-2) while surge and heave are both 0. When the front of the logger is vertically pointing down (forward acceleration, x), surge is +1g and when it is rolled to the right (sidewards acceleration, y) sway is +1g. Due to gravity, the pitch of a logger is thus translated into the values of heave, surge and sway (or z, x, y).
However, the values are measured in mV instead of g. Before delivering the GPS-loggers, they have been calibrated at UvA. The acceleration (A) expressed in g is calculated from the measured value (M) and the calibration factors offset (O) and sensitivity (S).
Thus acceleration for heave (z), surge (x) and sway (y) is calculated as: Az = (Mz-Oz)/Sz ; Ax = (Mx-Ox)/Sx; Ay = (My-Oy)/Sy ;
The values of O (in general ranging from 0-60, sometimes even 300) and S (ranging from 1300-1450) are available in the device table of the data base. If not available, use the factory defaults O = 0 and S = 1365.
 |
 |
|
In the figure above heave (z) is shown in green, surge(x) in red and sway (y) in blue, all values are expressed in g (on the y-axis).
Data processing and filtering
The raw log files are processed before being entered into the database. Each firmware version has a dedicated parser for the task. In certain cases data that is found in the log files is not stored in the database. This can occur for two reasons:
1. The data did not pass the CRC8 checksum (cyclic redundancy check). This is a standard procedure designed to detect accidental changes to raw computer data.
2. If the data has an invalid signature. For example, if a certain data format (eg numeric) or number of values (eg 4 digits) is expected but not returned, this record is not stored in the database.
When a line is deleted, the log file name, line number and the type of error is stored in the table gps.uva_warning102
Information about GPS receivers
The first series of tags used the UBlox LEAS4S chip (~up to tag 541), newer tags use UBlox LEAS6S. The series developed in 2012 will use the Max6G receivers. From 2012 receiver versions will be stored in the database. The UBX protocol is used for programming firmware, packaging and processing data. For more information about the receivers themselves see the ublox documentation. Ublox 4 Protocol specification [1] Ublox 6 Protocol specification [2]
