Monitoring Air Quality Open Data
A few weeks ago I had the opportunity to talk with the people from AsturiasHacking. Apart from other tech and non tech topics, some of us talked a bit about sensors, smart agriculture and pollution.
Some days later, after sharing some messages with Roberto (one of the AsturiasHacking folks), I thought I could make a small device, with an ESP8266 and a MQ135, and install it somewhere outdoors in order to monitor different gases (e.g CO2). So I started porting a MQ135 library to micropython and developed a small program for monitoring temperature, humidity and CO2 (see the code here). The data was published through dweet.io to a dashboard in freeboard.io (I will talk about this in another post).
When I was thinking in building a few devices to share them among colleagues to monitor gases in different parts of the city, I recalled that the air quality data of the city of Gijón was public on Internet. So I thought on making something different, a complete dashboard with the air quality open data. Please, find below details of the development.
Since a few years ago, the city of Gijón publishes different datasets, air quality measurements among them, in an open data portal. This data is provided in different formats under a Creative Commons Attribution 3.0 License.
In fact, they publish 2 different datasets related to air quality:
- The last week “realtime” (non validated) data (Note: It seems that the data in CSV format is not updated as often as the data in JSON format).
- A validated dataset of the whole year, and the data from the current year so far.
You can see the datasets and the formats in this page.
Among the different metrics, they provide measurements in six different points of the city for Benzene, CO, NO, NO2, Ozone, Toluene, MXilene and suspended solids. They also provide data related to the temperature, humidity, precipitation, wind speed and wind direction.
You can see the metrics and the units in the following link.
In order to load the data to the database I developed two small Python programs: one for the CSV and other one for the JSON (remember that the “real time” CSV data was updated less often).
The code basically reads the CSV or JSON file, parses it and uses the Influxdb-python module in order to store the data in the database.
I wanted to use Grafana for the dashboard. Grafana is a powerful tool for monitoring and is well integrated with Influxdb.
I did play first with Chronograf, a monitoring tool made by the people behind Influxdb, but when I found that the data was correctly stored in the database I switched to Grafana.
The dashboard was designed using the Grafana web interface, and when it was ready I exported it to JSON.
I played with different things in the dashboard, from time series to gauges, histograms and heatmaps, also some templating, tables and HTML. The final result can be seen below.
The integration with Influxdb (creation of the data source) and the load of the final dashboard template was made through Grafana’s web API.
The idea was to create a multi container docker application, so I relied in docker and docker-compose for this task.
I used the official Influxdb, Grafana and Python docker images as a base for building the system:
- Influxdb: https://hub.docker.com/_/influxdb/
- Grafana: https://hub.docker.com/r/grafana/grafana/
- Python: https://hub.docker.com/_/python/
and created an “AirQuality” image for parsing the open data, managing the storage in the database and importing the dashboard to Grafana.
How to build everything
Building everything should be easy, just clone or download the repository:
Decide what years you want to insert in the database by default, changing the variable AQ_YEARS in airquality.env (note that loading a complete year takes time, so by default only the current year is stored in the database), and launch:
After some time downloading and composing things, you should be able to connect to http://localhost:3000/dashboard/db/gijon-calidad-del-aire (user: admin / pass: admin) and see the result.
Note: In case you want to load other years once the container has been started, you can use the next command:
docker exec airquality /airquality/aq_load.sh "<<URL>>"
E.g for the data from 2016:
docker exec airquality /airquality/aq_load.sh "http://opendata.gijon.es/descargar.php?id=23&tipo=JSON"
You can find the ids of the files to download here
In case you don’t want to build everything and you just want to see the final result, the next image shows the dashboard for 2017, so far.