Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 39 ยท Projects and Practice

Project: IoT Climate Analysis

Clean, fill, smooth and analyse IoT temperature data: find gaps, interpolate, detect outliers, compute moving averages and daily summaries with portable SQL.

Real sensor data is messy. Devices miss readings, report impossible values, and send in bursts after reconnecting. In this project you take a stream of room-temperature readings, as they might arrive through MQTT, and turn it into clean, analysed data: you find gaps, fill them sensibly, drop outliers, smooth noise and produce daily summaries.

The InfluxDB server cannot run here, so every step uses portable SQL on a generated table. The comments show where Flux (fill(), movingAverage(), aggregateWindow()) or InfluxDB 3 SQL (date_bin, window functions) would do the same job.

The data

Two sensors report every 10 minutes for 24 hours. Faults are built in on purpose:

  • Sensor a misses readings between 06:00 and 07:00.
  • Sensor b produces two impossible spikes (999 degrees, a classic "sensor error" value).
  • Both follow a daily cycle.
SQL Create the data and count what we have

Press Run (or Ctrl + Enter) to see the result here.

Sensor a has 138 readings, b has 144, and b's maximum is clearly wrong.

Step 1: find the gaps

A gap is a spot where the time between consecutive readings exceeds the expected interval. Use LAG:

SQL Detect gaps with LAG

Press Run (or Ctrl + Enter) to see the result here.

In Flux you would find these with elapsed(); in monitoring terms, a long enough gap becomes a deadman alert.

Step 2: remove impossible values

Decide what counts as physically possible for the sensor (for example -40 to 60 degrees Celsius for a room) and filter or flag the rest. Do not silently delete: keep a count of rejects so you can see a failing sensor.

SQL Validate and count rejected readings

Press Run (or Ctrl + Enter) to see the result here.

The raw mean is wrecked by the spike; the clean mean is sensible. Values like -127 and 999 are typical "error codes" sent by hardware: know the codes of your sensors.

Step 3: smooth with a moving average

A moving average reduces noise. In SQL it is a window frame; in Flux it is movingAverage(n: 3). Beware that smoothing delays peaks and flattens them, so keep the raw data for alerting and use the smooth line for charts.

SQL A 3-point moving average

Press Run (or Ctrl + Enter) to see the result here.

The first two rows use fewer than three points because the window is not full yet.

Step 4: fill the gaps

Options for filling:

  • Leave null. The honest choice: a chart shows a gap.
  • Fill previous (carry the last value forward): fine for slow-changing values for a short time.
  • Linear interpolation: estimate between the two neighbours.

Never fill long gaps; you would be inventing data. Mark filled values (a filled field) so analysts can tell. The example interpolates a single missing reading between two known neighbours.

SQL Linear interpolation across a one-reading gap

Press Run (or Ctrl + Enter) to see the result here.

Step 5: daily summaries

Finally, roll up into daily min, max and mean with the sample count (see downsampling), plus a comfort metric: the share of time inside 20 to 24 degrees.

SQL Daily summary with a comfort metric

Press Run (or Ctrl + Enter) to see the result here.

Putting it into InfluxDB

In production the same logic splits across tools:

  1. Telegraf's mqtt_consumer writes raw data to a short-retention bucket.
  2. A Flux task or SQL job validates and rolls up into climate_hourly.
  3. A deadman check watches each sensor.
  4. A dashboard shows the smoothed line, the min/max band and the comfort percentage.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which SQL function finds the previous reading's timestamp so you can measure gaps?

    LAG gives the previous row's value within the window ordering.
  2. 2. Why count rejected readings rather than deleting them silently?

    The count is a health signal for the device.
  3. 3. What is the honest way to treat a six-hour gap?

    Long fills invent data. Leaving a gap keeps charts truthful.
  4. 4. What does a moving average do to a short peak?

    Smoothing trades detail for readability.

Summary

  • Find gaps with LAG (Flux: elapsed()), validate against physical limits, and count rejects.
  • Smooth for charts, keep raw data for alerts.
  • Fill only short gaps, and mark filled values.
  • Summarise daily with min, max, mean, count and a domain metric.
  • The same steps map onto Telegraf, tasks, checks and dashboards.

Next: Project: OHLC Bars from Tick Data