From Owntrack to QGIS – part 2

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.

1. 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
2. Run py3_env from the prompt. Now you are in the virtual python environment of QGIS.
3. 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.