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