Difference between revisions of "UvAGPS"

From ecology
Jump to: navigation, search
Line 116: Line 116:
 
== UVA_COMM101 ==  
 
== UVA_COMM101 ==  
  
−
Communication table indicates status and result of communications with ground stations
+
Communication table indicates status and result of communications with ground stations. Only for newer firmware.
  
 
{|border=1
 
{|border=1
Line 177: Line 177:
 
same no new records needs to be added  
 
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)  
 
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.  
+
then store all the new settings in one record. Only for newer firmware.
−
 
 
−
Tracking data from UvA GPS tags for older firmware
 
  
 
{|border=1
 
{|border=1
Line 250: Line 248:
 
== UVA_TRACKING_DATA101 ==
 
== UVA_TRACKING_DATA101 ==
  
−
create table gps.uva_tracking_data101
+
The tracking dat for newer firmware.
−
(
+
 
−
  device_info_serial integer not null,
+
 
−
  date_time timestamp not null,
+
{|border=1
−
  latitude float,          -- Decimal degrees
+
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
−
  longitude float,          -- Decimal degrees
+
|-
−
  altitude integer,        -- heigth above sealevel meters
+
|device_info_serial||int4||PK||NOT NULL ||
−
  pressure integer,        -- in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa)
+
|-
−
  temperature float,        -- in celcius degrees
+
|date_time||timestamp||PK||NOT NULL||
−
  satellites_used smallint, -- satellites used for fix  
+
|-
−
  gps_fixtime float,        -- GpsFixTime in seconds  
+
|latitude ||float||||||Decimal degrees
−
  positiondop float,        -- Position DOP in units
+
|-
−
  h_accuracy float,        -- Horizontal accuracy (units of meter)
+
|longitude ||float||||||Decimal degrees
−
  v_accuracy float,        -- Vertical accuracy (units of meter)
+
|-
−
  x_speed float,            -- Speed in x-axis (m/s)
+
|altitude ||integer||||||heigth above sealevel meters
−
  y_speed float,            -- Speed in y-axis (m/s)
+
|-
−
  z_speed float,            -- Speed in z-axis (m/s)
+
|pressure ||integer||||||in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa)
−
  speed_accuracy float,    -- Speed accuracy (m/s)  
+
|-
−
  primary key(device_info_serial, date_time)
+
|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)  
 +
|}
  
 
select AddGeometryColumn('gps', 'uva_tracking_data101', 'location', 4326, 'POINT', 2);
 
select AddGeometryColumn('gps', 'uva_tracking_data101', 'location', 4326, 'POINT', 2);

Revision as of 13:19, 5 January 2010

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)

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;

Retrieved from "https://wiki.e-ecology.nl/index.php?title=UvAGPS&oldid=287"