How Edeva Uses Continuous Aggregations and IoT to Build Smarter Cities

This is an installment of our “Community Member Spotlight” series, where we invite our customers to share their work, shining a light on their success and inspiring others with new ways to use technology to solve problems.

In this edition, John Eskilsson, software architect at Edeva, shares how his team collects huge amounts of data (mainly) from IoT devices to help build safer, smarter cities and leverages continuous aggregations for lightning-fast dashboards.

Founded in 2009 in Linköping, Edeva is a Swedish company that creates powerful solutions for smart cities. It offers managed services and complete systems, including hardware and software platforms.

As the creators of the dynamic speed bump Actibump and the smart city platform EdevaLive, the Edeva team works mainly for municipal, regional, and national road administrations, toll stations, environmental agencies, and law enforcement agencies.

The team also solves many other problems, from obtaining large amounts of environmental data for decision-making to developing a screening scale to help law enforcement agencies assess vehicle overloading. The latter, for instance, decreased the amount of time needed to control each vehicle, speeding up traffic checks and allowing law enforcement agencies to control more vehicles.

About the Team

The team at Edeva is a small but impactful group of 11, working on everything from creating hardware IoT devices to analyzing time-series data and making it accessible to customers—and, sometimes, the public.

As a software architect, I am in charge of building the best possible solution to receive, store, analyze, visualize, and share the customers’ event data. Our team then comes together to create solutions that work and that the customer actually wants.

About the Project

Edeva has created a dynamic speed bump called Actibump and the smart city platform EdevaLive.

The Actibump has been used in Sweden since 2010. Speeding vehicles activate a hatch in the road that lowers a few centimeters, creating an inverted speed bump. Providing good accessibility for public transportation, such as buses and emergency vehicles, the Actibump still ensures a safe speed for pedestrians and other vulnerable road users. It is also an environmentally friendly solution, helping decrease noise and emissions.

The Actibump can be combined with the EdevaLive system, delivering valuable remote monitoring services and statistics to Edeva’s customers.

Most of the data we collect is based on IoT devices:

Traffic flow data: The Actibump measures the speed of oncoming traffic to decide if it needs to activate the speed bump or not. We capture radar data, among others, and send an event to our smart city platform EdevaLive. The data treats the oncoming traffic as a flow rather than a single vehicle to create the smoothest possible traffic flow.

Vehicle classification data (weigh-in-motion): Actibump can be configured with weigh-in-motion. This means that the lid of the speed bump is equipped with a very sensitive high-sampling scale. The scale records several weight measurements when the vehicle passes over the speed bump. This way, it can detect how many axles a vehicle has and classify the type of vehicle. At the same time, it fires off one event for each axle with the scale fingerprint so we can analyze whether the weight measurements are correct.

Vehicle classification data (radar): If we want to classify vehicles in places where we do not yet have an Actibump installed, we can introduce a radar that can classify vehicle types. A roadside server controls the radar, gathers its data, and pushes it to EdevaLive.

Bike and pedestrian data: We use cameras installed above a pedestrian and cycle path. The camera can detect and count pedestrians and bicycles passing in both directions. We push this data to EdevaLive for analysis.

Number plate data: We can use a camera to detect a vehicle's number plate. This way, we can control devices like gates to open automatically. The camera can also be used to look up the number of electric vs. petrol or diesel vehicles passing it or determine if a specific vehicle exceeds the cargo weight limit.

Gyroscopic data: We offer a gyroscopic sensor that can gather data for acceleration in all different planes. This device generates a lot of data that can be uploaded to EdevaLive in batches or as a stream (if the vehicle has an Internet connection). This data is GPS-tagged and can be used to calculate jerk to provide indications on working conditions to a bus driver, for instance. The data can also be used to calculate the wear and tear of vehicles and many other things.

Environmental data: Monitoring environmental data in a smart city platform is important. This is why we use small portable devices that can measure the occurrence of different particle sizes in the air, CO2, and other gases. In addition, they measure the usual things like temperature, wind speed, etc. All this data is pushed to EdevaLive.

Alarm data: Our IoT devices and roadside servers can send alarm information if a sensor or other parts malfunction. All this data comes to EdevaLive in the same way as a regular IoT event, but these events are only used internally so that we can react as quickly as possible if there is a problem.

Status data: If the alarm data detects anomalies, the status data just reports the status of the server or IoT device. The devices run self-checks and report statistical data, like disk utilization, temperature, and load. This is also just for internal use to spot trends or troubleshoot in case any problems arise. For instance, it is incredibly useful to correlate CPU load with the version number of firmware or other software versions.

Administrative data: This is where the power of SQL and time-series data really shines. Let’s say we added a new device, and it has a configuration object that is persistent in a regular table in Timescale. This object keeps some metadata, such as the date it was added to the system or the device's display name. This way, we can use a join easily to pick up metadata about the device and, at the same time, get time-series data for the events that are coming in. There is only one database connection to handle and one query to run.

Choosing (and Using!) TimescaleDB

We realized we needed a time-series database a few years ago when we started storing our data in MySQL. At the time, we made a move to MongoDB, and it worked well for us but required quite a bit of administration and was harder to onboard other developers.

I looked at InfluxDB but never considered it in the end because it was yet another system to learn, and we had learned that lesson with MongoDB.

Learning from this journey, I looked for a solution that plugged the gaps the previous systems couldn’t. That is when I found Timescale and discovered that there was a hosted solution.

We are a small team that creates software with a big impact. This means that we don’t really have time to put a lot of effort into tweaking and administering every tool we use, but we still like to have control.

"With Timescale, our developers immediately knew how to use the product because most of them already knew SQL"

Also, since TimescaleDB is basically PostgreSQL with time-series functionality on steroids, it is much easier to onboard new developers if needed. With Timescale, our developers immediately knew how to use the product because most of them already knew SQL.

Edeva uses TimescaleDB as the main database in our smart city system. Our clients can control their IoT devices (like the Actibump from EdevaLive) and—as part of that system—see the data that has been captured and quickly get an overview of trends and historical data. We offer many graphs that show data in different time spans, like day, week, month, and year. To get this to render really fast, we use continuous aggregations.

"Timescale is basically PostgreSQL with time-series functionality on steroids, it is much easier to onboard new developers if needed"

Current Deployment and Future Plans

One of the TimescaleDB features that has had the most impact on our work is continuous aggregations. It changed our dashboards from sluggish to lightning-fast. If we are building functionality to make data available for customers, we always aggregate it first to speed up the queries and take the load off the database. It used to take minutes to run some long-term data queries. Now, almost all queries for long-term data are subsecond.

For example, we always struggled with showing the 85th percentile of speed over time. To get accurate percentile data, you had to calculate it based on the raw data instead of aggregating it. If you had 200 million events in a hypertable and wanted several years of data for a specific sensor, it could take you a long time to deliver—users don’t want to wait that long.

"It changed our dashboards from sluggish to lightning-fast"

Now that Timescale introduced percentile_agg and approx_percentile, we can actually query continuous aggregations and get reasonably accurate percentile values without querying raw data.

Note that “vehicles” is a hypertable where actibump_id is the ID of the dynamic speed bump containing several hundred million records.

This is how we build the continuous aggregate:

CREATE MATERIALIZED VIEW view1
 WITH (timescaledb.continuous) AS
 SELECT actibump_id,
 timescaledb_experimental.time_bucket_ng(INTERVAL '1 month', time, 'UTC') AS bucket,
 percentile_agg(vehicle_speed_initial) AS percentile_agg
FROM vehicles
GROUP BY actibump_id, bucket

And this is the query that fetches the data for the graph:

SELECT TIMESCALEDB_EXPERIMENTAL.TIME_BUCKET_NG(INTERVAL '1 month', bucket) AS date,
actibump_id,
APPROX_PERCENTILE(0.85, ROLLUP(PERCENTILE_AGG)) AS p85,
MAX(signpost_speed_max)
FROM vehicles_summary_1_month
WHERE actibump_id in ('16060022')
AND bucket >= '2021-01-30 23:00:00'
AND bucket <= '2022-04-08 21:59:59'
GROUP BY date, actibump_id
ORDER BY date ASC

Here is an example of the graph:

At the moment, we use PHP and Yii 2 to deploy TimescaleDB. We connect to TimescaleDB with Qlik Sense for business analytics. In Qlik Sense, you can easily connect to TimescaleDB using the PostgreSQL integration.

It is especially convenient to be able to connect to the continuous aggregations for long-term data without overloading the system with too much raw data. We often use Qlik Sense to rapidly prototype graphs that we later add to EdevaLive.

Advice and Resources

The next step for us is to come up with a good way of reducing the amount of raw data we store in TimescaleDB. We are looking at how we can integrate it with a data lake. Apart from that, we are really excited to start building even more graphs and map applications.

If you are planning to store time-series data, Timescale is the way to go. It makes it easy to get started because it is “just” SQL, and at the same time, you get the important features needed to work with time-series data. I recommend you have a look, especially at continuous aggregations.

Think about the whole lifecycle when you start. Will your use cases allow you to use features like compression, or do you need to think about how to store long-term data outside of TimescaleDB to make it affordable right from the start? You can always work around things as you go along, but it is good to have a plan for this before you go live.

We’d like to thank John and all the folks from Edeva for sharing their story. We are amazed to see how their work truly impacts the way people live and enjoy their city with a little help from time-series data. 🙌

We’re always keen to feature new community projects and stories on our blog. If you have a story or project you’d like to share, reach out on Slack (@Ana Tavares), and we’ll go from there.