UvAGPS
UvAGPS Data Model
For different versions of the UvAGPS firmware different tables are used. For firmware version v0.9.3.6 till v1.0.0.X the tables UVA_ENGINEERING_DATA and UVA_TRACKING_DATA are used. But in the new firmware versions 1.0.1.4 till 1.0.2.4 so much information was added that new tables were introduced. However for the most important tracking tables a view is created so that all similar tracking for all types of firmware can be seen in one view. That is the UVA_TRACKING view.
UVA_TRACKING_DATA
Tracking data from UvA GPS tags
| Field | Type | Key | Constraints | Comments |
| device_info_serial | int4 | PK | NOT NULL | |
| date_time | timestamp | PK | 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 |
UVA_ENGINEERING_DATA
Engineering data from UvA GPS tags
| Field | Type | Key | Constraints | Comments |
| device_info_serial | int4 | PK | NOT NULL | |
| date_time | timestamp | PK | NOT NULL | GMT |
| 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 | |||
| gpsfixtimeout | smallint | |||
| eh | text |
-- New UvA GPS tables for firmware 1.0.2.4 ,1.0.1.4, 1.0.2.3
-- Track firmware version for devices -- drop table gps.uva_device create table gps.uva_device (
device_info_serial integer not null, firmware_version text not null, primary key(device_info_serial)
);
-- Communication table --drop table gps.uva_comm101; create table gps.uva_comm101 (
device_info_serial integer not null, date_time timestamp 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 ?? Question Is this needed? Can't we put everything in status ?? 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 ?)
-- Also add values from S0/S1 to comm table -- S0 sts_time timestamp, -- derived from S0 hh,mi ?? is this required timesynced smallint, -- TimeSynced (0=not sy:wnced, 1=user set, 2=Gps Synced) devicemode smallint, -- DeviceMode (0-99) 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)
-- esw smallint , -- Solar cell switches (0-255). Added in fw1020, removed in fw1022 ??
-- S1 last_gps_fix timestamp, -- timestamp of last fix latitude float, -- Decimal degrees longitude float, -- Decimal degrees primary key(device_info_serial, date_time)
);
--
-- 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.
--drop table gps.uva_settings101;
create table gps.uva_settings101
(
-- C0 device_info_serial integer not null, -- Derive timestamp from Hh:mi fields in C0 combined with computer timestamp in JOINED line date_time timestamp not null, timesynced smallint, timezone_time smallint, swddd integer, -- SpecialUserSwitches
gpsnavmode smallint, -- GpsNavMode 0-7 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
-- C1
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
primary key(device_info_serial, date_time)
);
--drop table gps.uva_tracking_data101; create table gps.uva_tracking_data101 (
device_info_serial integer not null, date_time timestamp not null, latitude float, -- Decimal degrees longitude float, -- Decimal degrees altitude integer, -- heigth above sealevel meters pressure integer, -- in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa) temperature float, -- in celcius degrees satellites_used smallint, -- satellites used for fix gps_fixtime float, -- GpsFixTime in seconds positiondop float, -- Position DOP in units h_accuracy float, -- Horizontal accuracy (units of meter) v_accuracy float, -- Vertical accuracy (units of meter) x_speed float, -- Speed in x-axis (m/s) y_speed float, -- Speed in y-axis (m/s) z_speed float, -- Speed in z-axis (m/s) speed_accuracy float, -- Speed accuracy (m/s) primary key(device_info_serial, date_time)
);
select AddGeometryColumn('gps', 'uva_tracking_data101', 'location', 4326, 'POINT', 2);
create trigger process_uva_tracking_data101 before insert or update on gps.uva_tracking_data101
for each row execute procedure gps.update_location();
--drop table gps.uva_acceleration101; create table gps.uva_acceleration101 (
device_info_serial integer not null, date_time timestamp not null, index smallint not null, 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) primary key(device_info_serial, date_time, index)
);
-- Energy table - E0 lines --drop table gps.uva_energy101; create table gps.uva_energy101 (
device_info_serial integer not null, date_time timestamp 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 primary key(device_info_serial, date_time)
);
-- Cleanup UvAGPS data --delete from gps.uva_device; --delete from gps.uva_energy101; --delete from gps.uva_acceleration101; --delete from gps.uva_tracking_data101; --delete from gps.uva_settings101; --delete from gps.uva_comm101; --delete from gps.UVA_TRACKING_DATA; --delete from gps.UVA_engineering_DATA; --delete from bookkeeping.files where file_type = 'UVAGPS' and filename similar to 'Log_(' || array_to_string(array(select ltrim(to_char(device_info_serial, '0000')) from gps.uva_device where firmware_version in ('v1.0.1.4', 'v1.0.2.3', 'v1.0.2.4')), '|') || ')%'; --delete from bookkeeping.files where file_type = 'UVAGPS'; -- --delete from gps.uva_device where device_info_serial = 188; --delete from gps.uva_energy101 where device_info_serial = 188; --delete from gps.uva_acceleration101 where device_info_serial = 188; --delete from gps.uva_tracking_data101 where device_info_serial = 188; --delete from gps.uva_settings101 where device_info_serial = 188; --delete from gps.uva_comm101 where device_info_serial = 188; --delete from bookkeeping.files where file_type = 'UVAGPS' and filename like 'Log_0188%';
-- drop view gps.uva_tracking;
create or replace view gps.uva_tracking as
select device_info_serial
, date_time
, latitude
, longitude
, altitude::float as altitude
, pressure::float as pressure
, temperature
, h_accuracy
, v_accuracy
, x_speed
, y_speed
, z_speed
, gps_fixtime
, location
from gps.uva_tracking_data101 union select device_info_serial
, date_time
, latitude
, longitude
, altitude
, pressure
, temperature
, h_accuracy
, v_accuracy
, x_speed
, y_speed
, z_speed
, gps_fixtime
, location
from gps.uva_tracking_data;
