Difference between revisions of "UvAGPS"

From ecology
Jump to: navigation, search
Line 101: Line 101:
 
|}
 
|}
  
−
 
−
 
−
 
−
-- 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
 
  
 
== UVA_DEVICE ==  
 
== UVA_DEVICE ==  
Line 120: Line 113:
 
| firmware_version || text |||| NOT NULL ||
 
| firmware_version || text |||| NOT NULL ||
 
|}
 
|}
−
 
−
create table gps.uva_device
 
−
(
 
−
  device_info_serial integer not null,
 
−
  firmware_version text not null,
 
−
  primary key(device_info_serial)
 
−
);
 
  
−
-- Communication table
+
== UVA_COMM101 ==
−
--drop table gps.uva_comm101;
+
 
−
create table gps.uva_comm101
+
Communication table indicates status and result of communications with ground stations
−
(
 
−
  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
+
{|border=1
−
  -- S0
+
|'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments'''
−
  sts_time timestamp,       -- derived from S0 hh,mi ?? is this required
+
|-
−
  timesynced smallint,      -- TimeSynced (0=not sy:wnced, 1=user set, 2=Gps Synced)
+
| device_info_serial || integer || PK || NOT NULL ||
−
  devicemode smallint,      -- DeviceMode (0-99)
+
|-
−
  vbat float,                -- Battery voltage in V (0-~4.200V)  
+
| date_time || timestamp || PK || NOT NULL || timestamp of start communication
−
  logb integer,              -- logged bytes, waiting for download (0-4Mbyte)
+
|-
−
  logp smallint,            -- Logged percentage (units 0.1%) of memory waiting to be downloaded (0-1000)
+
| date_time_end || timestamp |||||| timestamp of end communication         
−
  gpsii integer,            -- GpsIval, current (0-65535)
+
|-
−
  comii integer,            -- ComIval, current (0-65535)
+
| date_time_utc || timestamp |||||| date_time in utc (if available)
−
  accii integer,            -- AcceleroIval, current (0-65535)
+
|-
−
  swddd integer,            -- User switches, current (0-65535)  
+
| date_time_end_utc || timestamp |||||| date_time_end in utc (if available)
−
-- esw smallint ,                -- Solar cell switches (0-255). Added in fw1020, removed in fw1022 ??
+
|-
 +
| 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
 +
|}
  
−
  -- S1 
 
−
  last_gps_fix timestamp, -- timestamp of last fix
 
−
  latitude  float,        -- Decimal degrees
 
−
  longitude float,        -- Decimal degrees
 
−
  primary key(device_info_serial, date_time)
 
−
);
 
  
  

Revision as of 13:02, 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

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


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

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