From Owntrack to QGIS – part 1

I like to track my trips. I don’t like google (or others) also tracking me! In this blog post I will present a solution where you can track yourself privately on your own server. All this is done with Owntracks installed on the mobile phone

I also wanted to have easy access to the data from QGIS. I will describe all my steps how to do this. You will need some technical skills, because describing every single detail will be to daunting!

Server setup

These things need to be installed on the server

• Mosquitto MQTT server. It should be secured by following all best practices, and then opened a port in the firewall allowing access to it from the internet.
• MySQL server
• Node-Red

SQL server

We need to create the database for the Owntrack data. I created a database “mysqllogger” with 2 tables. One for location changes and one for events:

CREATE DATABASE `mysqllogger`;
USE `mysqllogger`;

CREATE TABLE `location` (
  `deviceid` varchar(3) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `latitude` varchar(20) DEFAULT NULL,
  `longitude` varchar(20) DEFAULT NULL,
  `altitude` int(11) DEFAULT NULL,
  `verticalaccuracy` int(11) DEFAULT NULL,
  `battery` int(11) DEFAULT NULL,
  `triggertype` varchar(1) DEFAULT NULL,
  `connectiontype` varchar(1) DEFAULT NULL,
  `topic` varchar(40) DEFAULT NULL
);

CREATE TABLE `event` (
  `deviceid` varchar(3) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `latitude` varchar(20) DEFAULT NULL,
  `longitude` varchar(20) DEFAULT NULL,
  `verticalaccuracy` int(11) DEFAULT NULL,
  `triggertype` varchar(1) DEFAULT NULL,
  `description` varchar(20) DEFAULT NULL,
  `event` varchar(10) DEFAULT NULL,
  `topic` varchar(40) DEFAULT NULL
) 

Then I created two SQL-users and granted them rights to the tables. One user is for the node-red to write the data, and the other is for QGIS to access the data.

create user 'nodered'@'%' identified by 'nodered';
grant select, insert, update on mysqllogger to 'nodered'@'%';
flush privileges;

create user 'reader'@'%' identified by 'reader';
grant select on mysqllogger to 'reader'@'%';
flush privileges;

Node red

Moving on to Node-Red, I created a flow that attached to the MQTT messages from owntrack on the phone and stores it in the MySQL database. The flow looks like this:

Checking everything

Now we should be able to get all major movements on our phone logged to the mysql database. Do a couple of walkabouts and check the data in the database:

MariaDB [mysqllogger]> select * from location;
+----------+---------------------+---------------------+---------------------+----------+------------------+---------+-------------+----------------+----------------------+
| deviceid | time                | latitude            | longitude           | altitude | verticalaccuracy | battery | triggertype | connectiontype | topic                |
+----------+---------------------+---------------------+---------------------+----------+------------------+---------+-------------+----------------+----------------------+
| AJ       | 2020-04-17 19:16:52 | 56.56072067102097   | 8.863550461836963   |        0 |                3 |      69 |             | m              | owntracks/asj/iphone |
| AJ       | 2020-04-17 19:17:01 | 56.5589844016885    | 8.862264845528511   |        0 |                3 |      69 |             | m              | owntracks/asj/iphone |

Everything seems to work!

In the next part I will show how the data can be imported to QGIS.