ESP32/BME280 based Weather Station that pushes data to MySQL database and displays and graphs it

This article describes a weather station I created that uses ESP32s with BME280 sensors to push temperature, humidity and air pressure to a web server where it is displayed online. The local sensors upload data every minute to an online MySQL database, a web page written in PHP displays the most recent data points and plots graphs of the historic values. The system is now in operation with 8 sensors for some months and works very well. I have almost 1.5 million data points logged and still going strong.

Data as presented on the online dashboard. By clicking on any of the sensors the graph is shown.

Graph view of the data. Standard view of 24 hour window.

Background

The basic structure of the setup is based on a tutorial I found on randomnerdtutorials. This was really the starting point for me where I re-created this example and then started to modify it. I learned that the sketch from the tutorial is a proof of concept, but it was not made for real operation. The sensor nodes lacked a lot of fail saves and the display on the web page was very basic. This prompted me to go on and improve it on many fronts. Currently, it is running many months without problems and I am happy with the current status. If you want to re-create my version I would encourage you to first read the tutorial at randomnerdtutorials and then it should be fairly easy for you to make the switch to my code. I am providing the files, but I am not explaining everything in detail.

Key features of the system:

  • ESP32 based with BME280 as temperature/humidity/pressure sensors that push their recorded values to a web page
  • Fail saves that allow long-term operation of the sensor nodes without freezes
  • Sensor nodes implemented with deep-sleep for low power operation
  • Webpage that summarizes most recent sensor values and graphs historic values
  • Dew point calculation and highlighting of sensors that risk condensation and when it is more humid (higher dew point than inside) outside than inside.
Weather station data flow chart. The arrows highlight the main direction of information flow.

Local Sensor Node

On the ESP32 side (the local sensors) I worked on increasing robustness. The basic idea is still the same as from the tutorial, but that code did not run robustly for me. It took some time (months…) to really track down all the errors, as some would only occur once a few days and only on some sensors. Nothing more annoying than thinking finally it works robustly only to have it freeze at some point again….. Finally, I have them running consistently for months.

  • Robustness of ESP32 code improved through:
    • Wifi connection: There was an issue when wifi could not be established it would wait indefinitely for wifi to be started. Added a timeout.
    • BME sensor fail: If the BME 280 sensor can not be initialized correctly it pushes a dummy datapoint to the server. This way one can see whether the ESP32 stopped working or if the BME sensor is broken. Otherwise, all you see is that a sensor is not pushing data, not knowing the reason at all.
    • Added a watchdog timer. It will restart the ESP after some time elapsed. If all went well, the ESP will go to sleep long before the timer is activated. Kind of brute force method, but I think it is really effective.
  • Additional functionality:
    • The code features a deep-sleep mode. Once the data is uploaded it goes to deep-sleep. It supposedly will allow for battery operation, but the dev boards I have are not built for low power operation and thus I ended up having all sensors plugged in.
    • Polynomial correction of the humidity readout based on a calibration. I used the “salts in a jar” method – more on that in another post.

Database

The database itself is quite straightforward, just one spread sheet. I thoughts quite a bit on how to best represent the individual sensors. In the end I decided that each local sensor gets a name hard-coded and will always push that one as well to the database. This has the advantage that it is simple to add/remove sensors, as the webpage does not need to be modified with a new sensor. All you have to check is what unique locations (aka sensor name) are in the database and display them accordingly. The other values temperature, humidity and pressure are easy. The dew point is calculated new every time, as the calculation is anyways only based on temperature and humidity.

Structure of the database. Only one fairly simple sheet needed.

Online Data Display

The web page pulls the data from the database for display. It shows the most recent datapoint from all unique locations (=sensor names) it found in the database. If the latest value is older than 15 min it will grey out the value to highlight it is probably not up to date any more. If the latest value is > 24hrs the sensor will not be shown any more. That is also a very convenient feature to keep the list of locations down to working sensors.

Additionally, the data from each sensor can be plotted. The time window to be plotted can be set, standard is that it shows the last 24hours, but also longer windows are possible. Right now I am not reducing the number of datapoints, therefore the load times get quite long if the window is greater than a few days.

To created the graphs Chart.js is used – works fairly well and does all that is needed.

Files

The following files are included in the the download link below. I tried to comment everything and also highlight where changes are needed, there are quite a few. Mainly to set your own link to the server, the database credentials and the key that is used to check the validity of the sent data.

  • esp32_push_to_cloud.ino: Arduino file for the ESP32.
  • create sql database.sql: Code snippet that creates the database structure. Use phpmyadmin or similar to create the database structure or just manually create it your own.
  • logger-post-esp-data.php: File is called by the ESP and pushes the received data to the database.
  • logger-esp-data.php: Creates the summary table of the latest sensor readings.
  • linegraph.php, linegraph.js: Files to create the line graph. The php file creates a basic html page and the Java Script creates the plot.
  • logger-esp-fetch-data-chart.php: Fetches the data from the database in JSON format to display in linegraph.js.
  • config.php: Contains the parameters for the database etc. and for some customization for the web page displays.
  • Chart.js: Needed to create the linegraph. I used version 2.8.0, download at www.chartjs.org and save in /js subfolder.

Closing Remarks

  • I am not sure how secure the whole setup is. This is also the reason I am not showing the actual link to the operational web page. Not fully trusting it to open it up to the world.
  • Most tricky part of the whole project was to create sensor nodes that work reliably. There are so many unlikely events that can happen that make the sensor freeze. Plus, there is very little feedback you get, only a sensor that has power, but does not push data any more. I think now I found most problems and have reliably working solution.
  • Really happy how the whole system turned out and it is really nice to have it running and showing me the “climate zones” in our house. I’ll plan to write a more in-depth article on what I learned regarding indoor climate.
This entry was posted in Home Improvement, Strom und Spannung. Bookmark the permalink.

One Response to ESP32/BME280 based Weather Station that pushes data to MySQL database and displays and graphs it

  1. John says:

    Hi I tried to open your rar file but it comes up with unknown format error, is there any chance of getting a fresh copy thanks John

Leave a Reply

Your email address will not be published. Required fields are marked *