Difference between revisions of "UvAGPS"

From ecology
Jump to: navigation, search
(New page: == UvAGPS Data Model == == UVA_TRACKING_DATA == Tracking data from UvA GPS tags {|border=1 |'''Field'''||'''Type'''||'''Key'''||'''Constraints'''||'''Comments''' |- |device_info_serial...)
 
(→‎UvAGPS Data Model)
Line 1: Line 1:
−
== UvAGPS Data Model ==  
+
== UvAGPS Data Model ==
 +
 
 +
The UvAGPS datamodel uses different tables for diffrent versions of the UvAGPS firmware.
 +
 
 +
<nowiki>
 +
-- 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;
 +
 
 +
</nowiki>
  
 
== UVA_TRACKING_DATA ==
 
== UVA_TRACKING_DATA ==

Revision as of 12:24, 5 January 2010

UvAGPS Data Model

The UvAGPS datamodel uses different tables for diffrent versions of the UvAGPS firmware.

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

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
Retrieved from "https://wiki.e-ecology.nl/index.php?title=UvAGPS&oldid=280"