Accessing tracks.db to generate statistics

Marek Stefanek shared this question 23 days ago
Answered

Hi there,

first of all, I wish you all the best in the forthcoming year 2021.


At the end of the year 2020 I wished to summarize my activities recorded as tracks in Locus. Since there is no functionality available for time series reporting right in the application, I came up with an idea of generating some statistics by myself. I was able to connect the tracks.db, but I am not able to decipher information stored in the table "waypoints" (track length, total elevation and total duration). Although there are separate columns with intuitive names available in the table, apparently they are not used for storing such information (NULL values). I suppose that statistical information is somehow encoded in the column named "statistics".


I would like to ask, whether there is some documentation of the table available. Eventually, if there is some guidance on how to obtain basic track information for my reporting purposes.


Thank you.

Kind Regards,

Marek

Comments (3)

photo
1

Hello Marek,

thanks for this wish ... same to You ;).

Columns in table "tracks" with statistical names are really no longer used. Because it is not simple to delete a column in SQLite database, they just stay in place.

Statistics are really stored in binary format in column "statistics". If you are using Java to access database data, you may use the existing `TrackStats` object from Locus API. Otherwise, you may have to write your own implementation of this object which may be a little complicated. Good luck.

Just info: statistics directly in the app are something, I would really like to finally do this year (on 90%).

Feel free to ask if you will have any questions to this topic.

Menion

photo
1

Hi Menion,

Thank you very much for your swift answer.

I have just started to explore Python, so it took me a while to navigate through the java documentation you have provided and to transform it to the Python.

Finally, I was able to resolve this puzzle (strong resemblance with mystery caches in geocaching is not accidental:-).

The magic is done using one simple command. I dont know where to put this information, but it might be helpful for others:

import struct ... version, nan1 , numOfPoints, startTime, stopTime, totalLength, totalLengthMove, totalTime, totalTimeMove, speedMax, altitudeMax, altitudeMin  = struct.unpack_from('>iiiQQffQQfff', stat, 0)

The most challenging part was to identify that integer 'nan1' field, which contains some value, but I was not able to find it in java documentation.

Thanks again and best luck in adding summarized reporting for activities to Locus application. I am looking forward to use it soon.

Kind Regards,

Marek

On 4. 1. 2021 7:00, Locus Map wrote:

photo
1

Hello Marek,

heh solving mystery caches in practice, nice :). The second mystery number defines the length of the following block with values. It is useful in case, for example, stats for multiple tracks will be stored in a single byte[]. Here, this value is not useful.

Have a nice day!

Jiří M. aka Menion