Reading particle counting from DYLOS DC1100 data via ESP8266

Capture Data over MQTT Server

  • Make sure the MQTT server has the data
  • After the ESP8266 booted up, and started pushing data to the MQTT server. You can check the data comming in by using this command. If you use another computer to check the message in MQTT server, the IP should be the number like the one below. If you use the command line in the MQTT server, the -h '192.168.1.xx' can be replaced by -h 'localhost'.

    mosquitto_sub -h '192.168.1.xx' -t 'air/dylos' -u 'youruser' -P 'your_mqtt_password'

    The output should be similar to the lower windows in the photo below. The top photo is the log from the command when you run the script in the previous step with turning the flag mqtt_push=True.

    mqtt
  • Set up SQLite database
  • It is simple than it sounds. If you know how to use DB Browser for SQLite, you could create a table directly by running the SQL query like this:

    CREATE TABLE dylos (time timestamp, uptime int, small int, large int);

    Alternatively, you can use the full script here for the first run to initiate a table. For the second run onward, command the first two lines, and uncomment the 8 lines . Emphasis added.

    create_table = 'CREATE TABLE dylos (time timestamp, uptime int, small int, large int);'
    send_DB(create_table)
    # client = mqtt.Client()
    # client.username_pw_set(username=mqtt_user, password=mqtt_pw)
    # client.connect(broker_ip, 1883, 60)
    # client.on_connect = on_connect
    # client.on_message = on_message
    # client.on_disconnect = on_disconnect
    # time.sleep(1)
    # client.loop_forever()
  • Listen to the data
  • Here is the meat where listening and saving data are happening. The script is hosted on Github.

    # code excerpt showing where message is captured and stored.
    def on_message(client, userdata, msg):
        time_ = takeTime()
        try:
            payload = msg.payload.decode('UTF-8')
            if len(payload) > 0:
                payload = json.loads(payload.lower())
            if msg.retain == 0:
                if payload['sensor'] in ['dc1100']:
                    try:
                        small = int(payload['small'])
                        large = int(payload['large'])
                        data = {"sensor": "dylos","time": time_, 'uptime': payload['uptime'] ,'small': small, 'large': large}
                        cmd_ = """INSERT INTO {sensor} VALUES ('{time}', {uptime},{small},{large});""".format(
                            **data)
                        send_DB(cmd_)
                        print("-"*10)
                    except Exception as e:
                        print(f'Error: {e}')
        except Exception as e:
            print("Exception: " + str(e))
            with open(logFile, 'a') as f:
                f.write('{}: {}\n'.format(time_, e))
        return None

    If every goes as planned, you should see an SQLite database named dylos. Using DB Brower for SQLite with a simple SQL query, you could see some visualization like this.

    data-sqlite

    Now, I hope you can see when an database can shine. Querying ~7000 lines of data and visualize them took a breeze.

    Next, we will look into how to use a simple web app to make use of the captured data