Seidor
OpenTrend

25 July 2023

Monitoring smart cities with Grafana, Timescale and Sentilo

Miquel Asensio is a Project Manager at SEIDOR Opentrends and is focused on smart cities, data analytics and IoT (Internet of Things) projects.

Our company builds and maintains Sentilo, an open source sensor and actuator platform for smart cities. As part of this project, we are always looking for ways to integrate open source tools that can save time and money while increasing the efficiency of smart cities.

Like putting together a jigsaw puzzle, we strive to incorporate the open source tool that best fits each use case as new needs arise. We initially developed Sentilo in 2012 as a small IoT project in collaboration with the City Council of Barcelona, and today we have a mature product backed by a diverse community of public and private entities. For example, the Barcelona Sentilo instance manages over 7.5 million messages a day. SEIDOR Opentrends maintains Sentilo for various clients, by means of both on-premises and SaaS deployments through its spin-off Thingtia. Last but not least, we lead the Sentilo Community with the financial support of the City Council of Barcelona.

As part of this growth, and with the aim of expanding all modules making up the platform, we recently incorporated Grafana and TimescaleDB, which have become key components in our smart city solution. In this article, I will explain why a customer's request led us to create new customisable visualisations with Grafana that helped to improve a variety of processes in smart cities.

Integration of Grafana and TimescaleDB

One of the main objectives of Sentilo is to deliver information to its subscribed modules as quickly as possible. These modules are usually other external systems or data sources, such as a power monitoring system or a corporate data lake. To achieve this, the data collected by the smart city sensors (for vehicles, electrical panels and more) is stored in memory, giving us high performance... but we need to remember that memory is expensive. This data repository is only temporary; we needed long-term storage for historical data.

For some of our Sentilo implementations, users have their own data lakes, so we have a set of connectors for different target repositories, such as Kafka, Elasticsearch and the most common relational databases on the market. But this type of configuration does not work for all scenarios due to volume and/or cost. For this reason, when one of our customers asked us to create their historical repository from scratch and integrate it into their Sentilo platform, we had to evaluate several options to provide the right solution for the use case.

We needed something that helped us process information in real time and allowed us to explore and discover historical data through an interactive web-based visual tool. We had some prior experience in using Grafana to monitor the system and we knew about its excellent characteristics and the multiple sources of integrable data. Therefore, after analysing other market options and making several POCs, we decided to opt for the following combination:

  • Timescale. This open source time series database, which is based on PostgreSQL, provides high performance and scalability even when working with large volumes of data. It also provides a well-known query language such as SQL.

  • Grafana. This open source visualisation tool has a large set of powerful dashboards. It also provides interesting features for our use cases such as allowing for embedded content, multi-user support and dashboard sharing.

Sentilo

As can be seen in the architecture diagram, our smart city platform is based on a combination of databases, sensors, connectors and tools. Here is a brief description of how these pieces interact:

  • Sentilo stores sensor data in two different databases: Redis for real-time data and MongoDB for metadata from the sensor catalogue.

  • The Sentilo API provides services to interact with the catalogue and to publish data in real time (1). The data is then stored in the corresponding repository (2).

  • A custom Sentilo connector, based on a typical agent architecture that receives the data stream from Redis, receives the information and stores it in Timescale DB (3).

  • Sensor metadata is synchronised periodically with the Timescale database using another custom module (4).

  • Sentilo provides a web interface for different administrative purposes (5). Administrators can manage their catalogue and view the data in real time. And with Grafana, users can explore the historic database and create specific dashboards (6).

  • Sentilo also includes a citizen-focused web interface, where any citizen can explore the city map, view sensors and publicly accessible data.

Through Grafana, we can offer richer and more interactive visualisations adapted to the specific sector or type that can be integrated seamlessly into the platform's public website.

This solution has been implemented for the Sentilo instances of the Barcelona Smart Region and for the City Council of Eivissa (Spain) through Thingtia. It provides us with an interactive framework for searching and discovering large volumes of data (millions) and extensive time frames (years) with optimum performance and response time.

Sentilo 2

The Grafana dashboards are integrated into the standard Sentilo public display and are linked to different sectors: noise monitoring, air quality, irrigation, water sources and energy monitoring. Our customers can create their own powerful dashboards autonomously and share them with other members of the Sentilo community.

Sentilo 3

Use of Grafana to obtain big data value

Of course, always keep in mind that when working with large volumes of data and applying filters, you should optimise queries, add indexes or limit the data that the dashboard can manage, especially if these functions are open to the public. This is important if you want to keep costs down and deliver quick results when performing your queries.

Even so, the solution has been shown to be a good approach to working with large amounts of data. It has helped us explore and discover data, and we can extract valuable information by combining different sources. It is also a good way to create powerful dashboards that can be easily integrated into other city websites.

For more information on how Sentilo works or how you can add it to your own smart city plans, you can visit the Sentilo community. You can download the code for free, use it as SaaS or get in touch with us to build a custom solution.