The Internet of Things (IoT) describes a trend in which computing is becoming ubiquitous and embedded in more physical things. For many of these things, the purpose of IoT is to collect sensor data about the environment in which it exists, e.g., oil wells, factories, power plants, farms, moving vehicles, office buildings, and homes.
In other words, IoT is all about data. The datasets generated by IoT devices are generally time series in nature, with relational metadata to describe those things. Simulating IoT sensor data, for example, when testing a new system, is often necessary.
This tutorial shows how to simulate a basic IoT sensor dataset on PostgreSQL or TimescaleDB, which supercharges vanilla PostgreSQL with automatic data partitioning, always up-to-date materialized views, and a hybrid row-columnar storage engine.
To complete this tutorial, you need a cursory knowledge of the Structured Query Language (SQL). The tutorial walks you through each SQL command, but it is helpful if you've seen SQL before.
To start, install TimescaleDB. When your installation is complete, you can ingest or create sample data such as the data from the NYC Taxi Cab tutorial, and finish the steps in this guide.
Finally, verify that the sensors were created correctly:
SELECT * FROM sensors;
After running that last SQL statement, you should see something like this:
id | type | location
----+------+----------
1 | a | floor
2 | a | ceiling
3 | b | floor
4 | b | ceiling
(4 rows)
Creating the Simulated IoT Sensor Data
This section shows the query results as examples, but because the tutorial generates random data every time it is run, your results will look different but share the same structure.
Generate a dataset for all of our four sensors and insert it into the sensor_data table:
INSERT INTO sensor_data (time, sensor_id, cpu, temperature)
SELECT
time,
sensor_id,
random() AS cpu,
random()*100 AS temperature
FROM generate_series(now() - interval '24 hour', now(), interval '5 minute') AS g1(time), generate_series(1,4,1) AS g2(sensor_id);
Verify that the simulated sensor data was written correctly:
Average and last temperature, average CPU by 30-minute windows:
What if you don't just want the average temperature for each period but also the last temperature? For example, if you wanted to understand the final temperature value at the end of the interval:
SELECT
time_bucket('30 minutes', time) AS period,
AVG(temperature) AS avg_temp,
last(temperature, time) AS last_temp,
AVG(cpu) AS avg_cpu
FROM sensor_data
GROUP BY period;
Now let's take advantage of some of the metadata you have stored in the sensors table:
SELECT
sensors.location,
time_bucket('30 minutes', time) AS period,
AVG(temperature) AS avg_temp,
last(temperature, time) AS last_temp,
AVG(cpu) AS avg_cpu
FROM sensor_data JOIN sensors on sensor_data.sensor_id = sensors.id
GROUP BY period, sensors.location;
Congratulations, you now have a basic IoT sensor dataset you can use for testing in PostgreSQL or TimescaleDB. To learn more about the TimescaleDB concepts and functions you just used, please visit these pages in our developer documentation:
How OpenSauced Is Building a Copilot for Git History With pgvector and Timescale
