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 for older firmware

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 for older firmware

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

UVA_DEVICE

To be able to track the different firmware versions for the different devices the UVA_DEVICE table was added.

Field	Type	Key	Constraints	Comments
device_info_serial	integer	PK	NOT NULL
firmware_version	text		NOT NULL

UVA_COMM101

Communication table indicates status and result of communications with ground stations. Only for newer firmware.

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

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

The tracking dat for newer firmware.

Field	Type	Key	Constraints	Comments
device_info_serial	int4	PK	NOT NULL
date_time	timestamp	PK	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)
location	geometry			lat/long as PostGIS geometry

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)

);

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;