Agriculture Case Study: Soil Sensor Runs the Valve Itself and Reports in 38 Bytes a Day

A soil sensor in a field usually runs on a battery and talks over a radio that carries a few dozen bytes a day. It cannot wait for a server to tell it when to water. In the Alpha demo the sensor decides for itself when to open the irrigation valve, and it reports to the farm office in one small radio message a day. It runs the AltSql engine on 32 KB of flash with 672 bytes of engine RAM.

The run covers 30 simulated days with a reading every 15 minutes. It rains twice, on day 5 and day 22, and a hot spell from day 9 to day 16 dries the soil about twice as fast as usual. One gateway listens: the farm office, which receives each day’s summary by radio.

The month as the sensor stored it: moisture every 15 minutes, the four times the sensor opened the valve on its own, and the nightly radio messages.

The Set-Up

The rule reads the sensor’s own last hour from its own database:

altsql_append(db, "raw", (int64_t)t, moisture, soil_temp);
altsql_ts_window(db, "raw", "moisture", t - 2700, &h);   /* the last hour: 4 readings */
if (!valve && h.count == 4 && h.avg < 20.0) {
    altsql_put(db, "valve", "open", 4);                  /* decided on the device */
    valve = 1;
} else if (valve && h.avg >= 35.0) {
    altsql_put(db, "valve", "closed", 6);
    valve = 0;
}

What Happened

• Day 1. At installation the sensor’s series definitions and valve setting go to the farm office once, over a local link. From then on the radio carries only daily summaries. Moisture starts at about 31%.
• Day 5. Rain before dawn lifts moisture from 21% to 36%.
• Day 10, 08:00. Moisture has averaged 19.9% over the last hour. The sensor opens the valve, and closes it at 11:15 with moisture back to 35.8%.
• Day 14, 19:00. The hot spell has dried the soil again, and the valve opens until 22:15.
• Day 18, 21:45. A third watering, running past midnight to 01:00 on day 19.
• Day 22. Afternoon rain lifts moisture to 40%, and the next watering waits eight days.
• Day 30, 06:30. The fourth watering, until 09:45.

Every night at 23:45 the sensor wrote the day’s summary to its own flash and sent it in one radio message of 38 bytes. By the end of the month the farm office held all 30 summaries. The valve had opened and closed four times, each time on the sensor’s own reading of its own data.

What the Farm Office Can Answer

The office never received a single raw reading, and it never needed one to see how the month went:

farm-office> SELECT COUNT(*) AS days, MIN(min) AS driest, SUM(irrigation) AS irrigation_minutes FROM day
  days  driest  irrigation_minutes
  30    19.67   780

farm-office> SELECT time / 86400 + 1 AS day, avg, irrigation FROM day WHERE irrigation > 0
  day  avg    irrigation
  10   30.45  195
  14   24.18  195
  18   21.78  120
  19   36.04  75
  30   31.69  195

Four waterings of 3 hours 15 minutes each make 13 hours in all. The one that started on day 18 ran past midnight, so it shows up on two days.

These are the answers the demo gives after a run without power cuts. Both queries are among its ready-made questions.

One Message a Day, Byte by Byte

Long-range radios such as LoRaWAN carry very little. In Europe, at the slowest data rates, which reach the farthest, one message holds at most 51 bytes. A daily summary, as the engine stores it, takes 38. It fits with 13 bytes to spare, and it reaches the farm office as the same 38 bytes, with nothing to translate on the way. Here is the message for day 30:

The demo drops 5% of radio messages at random. A lost summary stays on the sensor and goes again the next night, because sync starts from the last record the office confirmed. In this run none was lost.

The Numbers

A Month in 32 KB

A month of readings does not fit in 32 KB. As the flash filled, the oldest rows rolled over first: 2,228 readings and 23 daily summaries by the end of the month. The sensor still held its last 652 readings, almost a week of detail, while every daily summary was already safe at the farm office. The valve state is a key-value setting, and settings are kept when rows roll over. Once the log wraps, the flash sees about 0.10 erases per sector per day, so flash rated for 10,000 cycles would last over 250 years (computed from the bytes written in the demo).

Next: On Real Hardware

In the Beta the soil sensor becomes an ST NUCLEO-WL55JC1 board (STM32WL55, Arm Cortex-M4) with a built-in LoRa radio, a capacitive soil-moisture probe and a relay standing in for the valve. It sends one LoRaWAN message a day (EU868, at most 51 bytes) through The Things Indoor Gateway to a Linux gateway playing the farm office. Its internal flash is written 8 bytes at a time, one of the things the Beta tests on the chip itself, along with 10,000 real power cuts and the energy each reading costs.

Later: With the Learned Query Optimizer

The planned Learned Query Optimizer would let the sensor learn how fast its soil dries at each soil temperature. It could then predict when moisture will reach 20% and open the valve ahead of time, or wait when the farm system forecasts rain. It could measure more often after rain and less at night, and each day choose what the single 51-byte message should carry: the daily summary, a three-day forecast or an alert. These are expectations, to be tested after the Beta.

Try It Yourself

Open the live demo and press Play. Pull the plug on the soil sensor at any moment. After the reboot it reads the valve state back from its own database, so a valve left open keeps watering until the soil is wet enough, and a closed one stays closed.

• case studies
• agriculture
• alpha demo