Threshold for altitude changes

I did create a sqllite trigger as an prove of concept to find out who well a threshold would work:

ALTER TABLE locations ADD COLUMN elevation_raw FLOAT;
ALTER TABLE locations ADD COLUMN elevation_change FLOAT;

create trigger location_filter_elevation_insert AFTER INSERT ON locations FOR EACH ROW
begin
  -- get previous elevation value into update trigger context
  update locations set elevation_raw=new.elevation, elevation=(SELECT elevation FROM locations WHERE rowid = new.rowid-1) WHERE rowid = new.rowid;
END;

create trigger location_filter_elevation_update AFTER UPDATE OF elevation_raw ON locations FOR EACH ROW
begin
  --new.elevation is the previous record elevation on first elevation_raw update
  update locations set elevation=CASE
	  -- when climb threshold reached or (it was climb and descent threashold not reached)
	  WHEN new.elevation_raw - new.elevation >= 1.0 OR ((SELECT elevation_change FROM locations WHERE rowid < new.rowid AND elevation_change != 0 ORDER BY rowid DESC LIMIT 1) > 0 AND new.elevation_raw-new.elevation > -1.0) THEN
		-- allow climb only
		max(new.elevation_raw, new.elevation)
	  ELSE
		-- allow descent only
		min(new.elevation_raw, new.elevation)
	  END 
  WHERE rowid = new.rowid;
  
  update locations set elevation_change = round((SELECT elevation FROM locations WHERE rowid = new.rowid) - (SELECT elevation FROM locations WHERE rowid = new.rowid-1),1) WHERE rowid = new.rowid;
END;

Insert into locations(elevation) VALUES (100.0),(101.3),(101.7),(101.4),(101.2),(101.9),(100.1);
SELECT elevation,elevation_raw,elevation_change, elevation - elevation_raw FROM locations ORDER BY rowid DESC limit 7;
SELECT SUM(elevationfix), SUM(elevation_change_abs) FROM (SELECT elevation - elevation_raw as elevationfix, abs(elevation_change) as elevation_change_abs FROM locations ORDER BY rowid DESC limit 7);

I did write a trigger to get a partial locus integration. This workarround will work only on saved tracks and isn't efficient because of the limitations of SQLlite Triggers. And I can't use this trigger to fix my track recording screen. I need to save the track to trigger altitude threashold filter.

Currently I work on a tasker solution based on locus/data/seonsors/ files to extract pressure values. This would even work without GPS.

- use only pressure changes (no calebration needed)

- apply pressure to altitute change calculation based on hight

- calculate pressure change by darksky to remove weather related climb and descent meters.

- apply threshold on altitue change calculation

- display the result as popup

I need to use stream based solutions on comandline like awk for this because tasker read file to memory is to slow because it take a while to move 30mb of sensor data to script execution or other tasks.

Basically I will use 2 Workarrounds:

1. Database trigger to force elevation threshold for saved tracks for all available GPS positions
2. locus sensor debug file readout for elevation summaries of active recordings or for saved tracks with missing gps points

Hi Falco, I haven't caught too much about your example and further comments but I totally agree that a filtering threshold could be a solution. As a matter of fact I thought that the existing filtering is based on this concept but maybe not. Cumulative elevation error is one of the major issues with locus., Threshold correction/filtering could be applied diredtly during track recording but also with postprocessing, thus allowing to save the corrected track but also keep the original measurement and to try several corrections with different thresholds .

I did work on the 2nd Workarround "read sensor data log for calculating live recording climb elevation"

Here is the main part, the awk script for parsing locus sensor log:

cat 2018-07-07_06-56-47.data 2018-07-07_16-38-00.data | 
awk 'BEGIN{FS=";";min=2000}
$3 == "SI_PR" {
  time=substr($2,0,10); 
  pr=substr($4,0,(length($4)-2)); 
  if(time!=prevTime && prevTime!=""){ 
    if(valueCount>2) { 
      valueCount-=2; 
      valueSum-=min+max}
    print time" "valueSum/valueCount; 
    valueCount=0;
    valueSum=0;
    min=pr; 
    max=pr
  } 
  valueCount++; 
  valueSum+=pr; 
  prevTime=time; 
  if(pr<min) min=pr; 
  if(pr>max) max=pr; 
}' | awk '{
pr=$2; 
if(NR==1) prevPr=pr;
change=pr-prevPr;
if(NR % 100 == 0){
 if(pr>1025) prmult=8.25;
 else if(pr>900) prmulti=9.16-((pr-900)/125)*0.91;
 else if(pr>800) prmulti=10.08-((pr-800)/100)*0.92;
 else if(pr>700) prmulti=11.22-((pr-700)/100)*1.14;
 else prmulti=11.22;
} 
if((isDesc && change < 0.15) || change < -0.15) isDesc = 1; 
else isDesc=0; 
if((isDesc && change < 0) || !isDesc && change > 0){
  change*=prmulti; 
  prevPr=pr; 
  if(change>0) climb+=change; 
  else desc+=change;
}
} END{print climb" "desc}' 

• locus DB with trigger threashold, track statistics view: 1529hm
• locus trigger gpx export: 1623hm
• awk sensor data result: 1305hm
• SRTM on gpx: 1557hm
• SRTM on gpx smoothed: 1311hm
• reduced gpx: 1500hm
• reduced gpx smoothed: 1200hm
• GPSies reduced gpx: 1750hm (I don't know why GPSies does fail on road tours, it's much better in the mountains, recalculation of elevation did change from 1793 to 1750)
• SRTM on reduced gpx: 1184hm
• SRTM on reduced gpx smoothed: 940hm

As you see it is very close to smoothed SRTM data. I would say SRMT is the correct result because I did select the test tour based on the small very even hills to get a nerly perfect SRTM result as base.

It's interesting to see that even the 1.0m threshold database trigger is not enoght to get close to the SRTM result. And point reduction with Reumann Witkam and 5m corridor does kill the elevation on road tours because there are many segments which goes straight over many small hills like a huge pumptrack.

I did attach 14MB of Sensor data if somebody want to play with. There are 300 000 Lines. And the awk scripts does parse and calculate all of them in just 7 seconds on my sony phone. (5 seconds if you merge both awk scripts and skip the whole print and parse step between them)

If somebody ask why I don't use smoothed SRTM if this is the truth: SRTM does only work on small elevation changes far away from steep trails or even canyon like things. I would to get flat country SRTM accuracy in the middle of the mountains with canyons and trails on the edge of the mountain. And this is only possible with barometric values.

Here is my tasker task for calculating weather related climb and descent errors

It does need %SENSORS_DIR = /sdcard/Locus/data/sensors

and %DARKSKYAPI = free api key for dark sky weather

And here is my Tasker Task for calculating elevation based on sensor pressure (does even work if you turn GPS off) and just for fun a weather data correction of the elevation based on first GPS position and recording timespan :D

I did add pointsPerSec and maxTimeGap to my awk sensor data parser to track if I lost any sensor data to give me the opportunity to judge the output elevation accuracy. For example in a deep power saving mode I get sensor readouts every 5 minutes which could be accurate enoght if the max time gap between 2 values do not exceed 5 minutes

Don't forgett

%SENSORS_DIR = /sdcard/Locus/data/sensors

and %DARKSKYAPI = free api key for dark sky weather

AWK script optimizations did make alot of fun, it was a little bit difficult to find out how to get the first data line (2nd line) without dooing 300.000 times if(linenumer == 2)

Here are some bugfixes, performance improvement for the workarround and a task which get the date of the current selected track. If there is any Date string on current screen the task will use this as date for the calculation if you want to calculate elevation of recorded track from last week with sensor data instead of using track data.

If no date got found it use the current date to support calculation during track recording.

GetLocusDate needs AutoInput plugin to read screen labels. WeatherElevation need a darksky api key and internet and LocusSensorElevation need busybox or similar toolsets to get awk.