In the previos post, I went through the basic steps of getting data from Owntracks to a MySQL database. In this post I will show how these data easily can be imported into QGIS.
Our first obstacle is to get some additional python modules installed inside the QGIS environment. We need these modules for extracting GPS data from the MySQL database.
- Open OSGeo4W shell as administrator. It’s in the startmenu, but can’t be started from there. Instead right click on it in
"C:\Program Files\QGIS 3.12\OSGeo4W.bat"
and run it as administrator - Run
py3_env
from the prompt. Now you are in the virtual python environment of QGIS. - Install the needed libraries by running:
python -m pip install sqlalchemy pymysql pandas
Now we can fire op QGIS and open the python-console. In here you can open the editor and paste the following code. When you run it, it will create a method/function for importing data of a specific date.
from sqlalchemy import create_engine import pymysql import pandas as pd def importOwntrack(dbHost, dbDatabase, dbUser, dbPass, gpsDate): # Get sql data into pandas dataframe db_connection = create_engine(f"mysql+pymysql://{dbUser}:{dbPass}@{dbHost}/{dbDatabase}") df = pd.read_sql(f"SELECT * FROM location WHERE time LIKE '{gpsDate}%%'", con=db_connection) df.set_index("time", inplace=True) # Prepare vector layer of lines, with features and their field attributes vl = QgsVectorLayer("point", f"Gps {gpsDate}", "memory") pr = vl.dataProvider() pr.addAttributes([QgsField("time", QVariant.String)]) f = QgsFeature() vl.updateFields() # Traverse gps data and add geometries for each point for index, row in df.iterrows(): lon = float(row["longitute"]) lat = float(row["lattitude"]) f.setGeometry(QgsGeometry.fromPointXY(QgsPointXY(lon, lat))) f.setAttributes([str(index)]) pr.addFeature(f) # Make the geometry layer happen and add it to QGIS project vl.updateExtents() QgsProject.instance().addMapLayer(vl)
The code first connects to the MySQL database and gets all records of a certain date as a pandas dataframe.
Then a temporary (in memory) layer is created and an attribute is defined for the data. The only thing I want to register with each point is the date/time.
Then the rows are traversed and a point-geometry is a added to the layer for each row.
Finally the temporary layer is added to QGIS.
Testing
Now in the python console you can test it on your data, by running the newly defined method:
importOwntrack("automationserver","mysqllogger","reader","reader", "2020-04-17")
Badabing, badaboom – there is a new layer in QGIS with all the points!

For now this is enough for me. A potential part 3 of this blog would be to make a processing-tool with a date-picker.