MQTT Python Save to SQL Server Database using OwnTracks Data

Started playing around with MQTT this week. Figured out how to save MQTT messages directly to a SQL Server database so I thought I would share how here.

Owntracks

Owntracks is an application that allows you to post your location from your phone to an MQTT broker. You can read about it here: https://owntracks.org/ and download the app. I am using Android.

Mosquitto

Mosquitto is an open source MQTT broker that has clients for Windows and Linux. I am using a Windows broker here in this example but it was easy to install this on a Raspberry Pi with the Linux version. https://mosquitto.org

The Windows install was easy, just next next finish through the setup and then make sure the Windows Service Mosquitto Broker is running

Python 

You will need to install the paho python module using pip. pip -install paho-mqtt should do the trick. Once you get in installed you can create a simple script like so. This is using pyodbc to insert data into SQL Server

Some comments. 

• This creates a subscriber to whatever mqtt message you want to post. You configure the publisher on your smartphone application.
• Notice I have a your server ip in the script. You will need to figure out what your external facing ip address is (just google what is my ip). Then you will need add a port forward to your router config for port 1883 or whatever port you are going to use.
• Create a simple table in SQL Server called mqtt and create two columns
  
• I am using the replace function here because the message payload was almost in JSON format but had some extract characters. The payload started with a b' and ended with a ' so I just remove that out.

Configure OwnTracks
As I mentioned I am using the Android App. Here are some screen shots on how I configured it.

SQL Server

Once data starts flowing into the database you can start querying it or doing whatever you want with it. Earlier I mentioned that I removed some characters off the payload so the resulting data would be in JSON format. Since we did that work here we can use the JSON_VALUE function to extract data out of the payload column.

This SELECT will give you the most recent payload captured and will return it back in some nice useable columns.

Google Maps

Then from there if you want to get real creepy you can select the lat/lon values from your db and display them on a Google Map using the JavaScript Maps API (requires an API key).

I zoomed in so you couldn't really see where I am sitting but that is my most recent position with other information displayed. Internet Type indicates I am on wifi. Pretty cool.