How to Simulate a Basic IoT Sensor Dataset on PostgreSQL
The Internet of Things (IoT) describes a trend where 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 the data. The datasets generated by these things are generally time series in nature, with relational metadata to describe those things.
Often, it is necessary to simulate IoT sensor data, for example, when testing a new system. This tutorial shows how to simulate a basic IoT sensor dataset on PostgreSQL, or TimescaleDB.
For creating a more advanced simulated IoT dataset, try the Time-series Benchmarking Suite.
Prerequisites
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.
Setting Up Your Tables
First, connect to your database via psql.
Second, create the “sensors” and “sensor_data” tables:
CREATE TABLE sensors(
id SERIAL PRIMARY KEY,
type VARCHAR(50),
location VARCHAR(50)
);CREATE TABLE sensor_data (
time TIMESTAMPTZ NOT NULL,
sensor_id INTEGER,
temperature DOUBLE PRECISION,
cpu DOUBLE PRECISION,
FOREIGN KEY (sensor_id) REFERENCES sensors (id)
);If you are using TimescaleDB, convert the sensor_data table into a hypertable:
SELECT create_hypertable('sensor_data', 'time');
Next, populate the sensors table with four sensors:
INSERT INTO sensors (type, location) VALUES
('a','floor'),
('a', 'ceiling'),
('b','floor'),
('b', 'ceiling');
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 results of the queries as examples, but because the tutorial generates random data every time it is run, your results will look different, but will share the same structure.
Generate a dataset for all of our four sensors and insert 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:
SELECT * FROM sensor_data ORDER BY time;Example output:
time | sensor_id | temperature | cpu
-------------------------------+-----------+---------------------+-----------------------
2024-03-02 21:58:52.415591+00 | 2 | 34.74860895110643 | 0.7382245703719759
2024-03-02 21:58:52.415591+00 | 1 | 78.28644854140863 | 0.0538759700059126
2024-03-02 21:58:52.415591+00 | 4 | 84.34109828273877 | 0.2814163809100949
2024-03-02 21:58:52.415591+00 | 3 | 18.30145613996288 | 0.11061603685747601
2024-03-02 22:03:52.415591+00 | 1 | 28.8350892206531 | 0.5167745613330366
2024-03-02 22:03:52.415591+00 | 2 | 70.38494920417571 | 0.04132443651006601
2024-03-02 22:03:52.415591+00 | 4 | 91.39978680296652 | 0.5797557914264548
2024-03-02 22:03:52.415591+00 | 3 | 89.40911758654491 | 0.6911678473371399
...
You've successfully created a basic IoT sensor dataset! Now let's run some queries.
Running basic queries
This section requires installation of TimescaleDB.
Average temperature, average CPU by 30-minute windows:
SELECT
time_bucket('30 minutes', time) AS period,
AVG(temperature) AS avg_temp,
AVG(cpu) AS avg_cpu
FROM sensor_data
GROUP BY period;
Sample output:
period | avg_temp | avg_cpu
------------------------+--------------------+---------------------
2024-03-03 17:30:00+00 | 43.10768090240475 | 0.44125966982283243
2024-03-03 09:00:00+00 | 46.47069943057713 | 0.44783394066455323
2024-03-03 12:30:00+00 | 50.245161953350795 | 0.4173858962698807
2024-03-03 02:30:00+00 | 41.02991149971461 | 0.5369651925149536
2024-03-03 05:00:00+00 | 45.576655689044905 | 0.5800885979769893
...
Average & 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;
Example output:
period | avg_temp | last_temp | avg_cpu
------------------------+--------------------+--------------------+---------------------
2024-03-03 17:30:00+00 | 43.10768090240475 | 37.897223743142305 | 0.44125966982283243
2024-03-03 09:00:00+00 | 46.47069943057713 | 10.360343839058483 | 0.44783394066455323
2024-03-03 12:30:00+00 | 50.245161953350795 | 13.395952389199483 | 0.4173858962698807
2024-03-03 02:30:00+00 | 41.02991149971461 | 20.85346474997114 | 0.5369651925149536
2024-03-03 05:00:00+00 | 45.576655689044905 | 17.53287790099687 | 0.5800885979769893
...
Using the sensor metadata
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;
Example output:
location | period | avg_temp | last_temp | avg_cpu
----------+------------------------+--------------------+---------------------+---------------------
floor | 2024-03-03 17:00:00+00 | 57.7554057402912 | 70.92230873447865 | 0.5664165557316464
floor | 2024-03-03 13:00:00+00 | 49.10969984873296 | 95.18393076186942 | 0.2998594519486328
ceiling | 2024-03-03 01:30:00+00 | 43.23389752853527 | 84.23455916498523 | 0.415458435988545
ceiling | 2024-03-02 23:30:00+00 | 49.04287048373988 | 29.13487894000726 | 0.5481122939027164
floor | 2024-03-03 14:00:00+00 | 42.197390088314954 | 48.37367352541595 | 0.4302213965771769
ceiling | 2024-03-03 09:00:00+00 | 33.96200524565035 | 50.90505195144848 | 0.5037301372624226
floor | 2024-03-03 11:00:00+00 | 67.50318185485571 | 74.71978857954535 | 0.4930108855000481
ceiling | 2024-03-03 10:00:00+00 | 43.6388721380759 | 18.17060920674267 | 0.44136773995668604
...
Next Steps
Congratulations, you now have a basic IoT sensor dataset you can use for testing in PostgreSQL or TimescaleDB.
To learn more about TimescaleDB, or about the TimescaleDB concepts and functions you just used, please visit these pages in our developer documentation:
