Real-Time Precision Agriculture with Streaming SQL: Crops, Irrigation, Livestock, Weather, and Water Quality

Real-Time Precision Agriculture with Streaming SQL: Crops, Irrigation, Livestock, Weather, and Water Quality

·

Updated

·

17 min read

Real-time precision agriculture with streaming SQL means weather station readings, soil probes, NDVI updates, valve states, livestock ear-tag telemetry, and water quality samples are evaluated as they arrive, so frost risk, heat stress, disease pressure, drought stress, irrigation need, fever in a cow, and a turbidity breach in the irrigation supply each surface within minutes instead of in tomorrow morning's recommendations. This guide builds the farm data layer on RisingWave, a PostgreSQL-compatible streaming database, starting with weather impact analysis and extending to crop monitoring, smart irrigation, livestock health, and water quality.

Why Nightly Processing Fails on the Farm

Batch-processed analytics tell you what happened to your crops yesterday. Weather is the largest uncontrolled variable in crop production, and the interventions that matter are timed in minutes. Frost protection irrigation works if the temperature drop is detected 30 to 60 minutes ahead; the same event in a 3-hour-old report leaves burnt foliage. Sustained temperatures above 35 degrees Celsius during flowering cause irreversible yield loss in most grain crops. Downy mildew and grey mold need sustained leaf wetness combined with high humidity, and the spray window closes fast.

The same gap applies everywhere on the farm. By the time an agronomist sees a soil moisture alert from the nightly job, the crop has been under stress for 12 to 18 hours, and moisture stress during flowering or grain fill cuts yield by 10 to 40 percent. Timer-based irrigation runs whether the soil is saturated from overnight rain or critically dry after a heat wave, while agriculture takes roughly 70 percent of global freshwater withdrawals. A cow above 39.5 degrees for 30 minutes is likely febrile, and a flag in tomorrow's report misses the treatment window and risks herd spread. A broken backflow preventer can push fertilizer runoff into a supply line within minutes, and daily lab samples cannot see it.

RisingWave ingests all of these streams from Kafka or MQTT bridges and maintains the agronomic indices, threshold comparisons, and alerts as incrementally updated materialized views. Thresholds live in reference tables, so changing a crop stage or a regulatory limit is an UPDATE, not a code change. Any farm management platform with a PostgreSQL connector reads live status with a SELECT, and alerts flow to Kafka sinks that irrigation controllers, herd management systems, and compliance tools consume. RisingWave is open source under Apache 2.0, written in Rust, and checkpoints state to S3.

Sources and Reference Tables

One weather stream, one soil and canopy sensor stream with probe depth, NDVI updates from satellite or drone processing, valve events from the irrigation controller, livestock telemetry, and water quality telemetry, plus reference tables for fields, crop thresholds, irrigation configuration, water limits, and sensor network topology.

CREATE SOURCE weather_raw (
    station_id     VARCHAR,
    reading_time   TIMESTAMPTZ,
    temp_c         DOUBLE PRECISION,
    humidity_pct   DOUBLE PRECISION,
    wind_speed_ms  DOUBLE PRECISION,
    solar_rad_wm2  DOUBLE PRECISION,
    rainfall_mm    DOUBLE PRECISION,
    et0_mm         DOUBLE PRECISION,   -- reference evapotranspiration for the interval
    leaf_wetness   DOUBLE PRECISION    -- 0.0 dry to 1.0 wet
) WITH (connector = 'kafka', topic = 'agri.weather.raw', properties.bootstrap.server = 'broker:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE field_sensors (
    sensor_id          VARCHAR,
    field_id           VARCHAR,
    valve_zone_id      VARCHAR,      -- irrigation zone served by one valve
    reading_time       TIMESTAMPTZ,
    soil_moisture_pct  DOUBLE PRECISION,   -- volumetric water content
    soil_temp_c        DOUBLE PRECISION,
    canopy_temp_c      DOUBLE PRECISION,
    ec_dscm            DOUBLE PRECISION,   -- electrical conductivity dS/m
    depth_cm           SMALLINT            -- 10, 30, 60
) WITH (connector = 'kafka', topic = 'agri.field.sensors', properties.bootstrap.server = 'broker:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE ndvi_updates (
    field_id        VARCHAR,
    image_time      TIMESTAMPTZ,
    ndvi_score      DOUBLE PRECISION,    -- healthy crop above 0.5
    ndvi_change_7d  DOUBLE PRECISION,
    source          VARCHAR              -- 'SENTINEL2','DRONE'
) WITH (connector = 'kafka', topic = 'agri.ndvi', properties.bootstrap.server = 'broker:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE valve_events (
    valve_id       VARCHAR,
    valve_zone_id  VARCHAR,
    field_id       VARCHAR,
    event_time     TIMESTAMPTZ,
    state          VARCHAR,     -- 'OPEN','CLOSED'
    commanded_by   VARCHAR,     -- 'SCHEDULER','MANUAL','EMERGENCY_CLOSE'
    flow_rate_lpm  DOUBLE PRECISION
) WITH (connector = 'kafka', topic = 'agri.valves', properties.bootstrap.server = 'broker:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE livestock_telemetry (
    tag_id          VARCHAR,        -- ISO 11784 RFID tag
    animal_id       VARCHAR,
    species         VARCHAR,        -- cattle, sheep, pig
    zone_id         VARCHAR,        -- barn or paddock zone
    temp_celsius    DOUBLE PRECISION,
    activity_index  DOUBLE PRECISION,  -- steps per minute equivalent
    rumination_min  DOUBLE PRECISION,  -- rumination minutes in the last hour
    event_time      TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'farm.livestock.telemetry', properties.bootstrap.server = 'broker:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE water_quality_raw (
    sensor_id      VARCHAR,
    node_location  VARCHAR,      -- 'INTAKE','TREATMENT','DISTRIBUTION','ENDPOINT'
    field_id       VARCHAR,
    reading_time   TIMESTAMPTZ,
    turbidity_ntu  DOUBLE PRECISION,
    ph_value       DOUBLE PRECISION,
    ec_dscm        DOUBLE PRECISION,
    do_mgl         DOUBLE PRECISION,   -- dissolved oxygen
    temp_c         DOUBLE PRECISION,
    chlorine_mgl   DOUBLE PRECISION,
    nitrate_mgl    DOUBLE PRECISION
) WITH (connector = 'kafka', topic = 'water.quality.telemetry', properties.bootstrap.server = 'broker:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE TABLE field_registry (
    field_id            VARCHAR PRIMARY KEY,
    farm_id             VARCHAR,
    crop_type           VARCHAR,
    growth_stage        VARCHAR,     -- 'GERMINATION','VEGETATIVE','FLOWERING','MATURITY', updated by the agronomist
    weather_station_id  VARCHAR,
    area_ha             DOUBLE PRECISION
);

CREATE TABLE crop_thresholds (
    crop_type              VARCHAR,
    growth_stage           VARCHAR,
    base_temp_c            DOUBLE PRECISION,   -- growing degree day base
    max_temp_c             DOUBLE PRECISION,   -- growing degree day cap
    frost_threshold_c      DOUBLE PRECISION,
    heat_stress_c          DOUBLE PRECISION,
    max_leaf_wetness_h     DOUBLE PRECISION,   -- disease pressure threshold
    min_moisture_pct       DOUBLE PRECISION,
    max_moisture_pct       DOUBLE PRECISION,
    max_ec_dscm            DOUBLE PRECISION,
    PRIMARY KEY (crop_type, growth_stage)
);

CREATE TABLE field_irrigation_config (
    field_id              VARCHAR,
    valve_zone_id         VARCHAR,
    crop_type             VARCHAR,
    target_moisture_pct   DOUBLE PRECISION,
    stress_threshold_pct  DOUBLE PRECISION,   -- irrigate below this
    saturation_pct        DOUBLE PRECISION,   -- stop above this
    kc_factor             DOUBLE PRECISION,   -- crop coefficient
    PRIMARY KEY (field_id, valve_zone_id)
);

CREATE TABLE water_quality_limits (
    node_location     VARCHAR,
    use_type          VARCHAR,    -- 'POTABLE','IRRIGATION','TREATED_EFFLUENT'
    max_turbidity     DOUBLE PRECISION,
    min_ph            DOUBLE PRECISION,
    max_ph            DOUBLE PRECISION,
    max_ec_dscm       DOUBLE PRECISION,
    min_do_mgl        DOUBLE PRECISION,
    min_chlorine_mgl  DOUBLE PRECISION,
    max_nitrate_mgl   DOUBLE PRECISION,
    PRIMARY KEY (node_location, use_type)
);

CREATE TABLE sensor_network (
    sensor_id        VARCHAR PRIMARY KEY,
    node_location    VARCHAR,
    upstream_sensor  VARCHAR,
    use_type         VARCHAR,
    field_id         VARCHAR
);

Keying thresholds by crop type and growth stage, with the stage stored on the field, means advancing a field from vegetative to flowering is one row update in field_registry and every downstream view re-evaluates against the new limits.

Weather Impact: Indices, Growing Degree Days, and Stress Classes

Hourly weather indices per station come first: averages, extremes, rainfall, and leaf wetness hours as the fraction of readings above 0.5. Joining to fields and thresholds gives the growing degree day contribution, capped at the crop's maximum, and a stress class for frost, heat, disease pressure, and waterlogging.

CREATE MATERIALIZED VIEW weather_indices_1h AS
SELECT station_id, window_start, window_end,
       AVG(temp_c) AS avg_temp_c, MIN(temp_c) AS min_temp_c, MAX(temp_c) AS max_temp_c,
       AVG(humidity_pct) AS avg_humidity, AVG(wind_speed_ms) AS avg_wind_ms,
       SUM(rainfall_mm) AS total_rainfall_mm, AVG(solar_rad_wm2) AS avg_solar_rad, SUM(et0_mm) AS et0_mm,
       COUNT(*) FILTER (WHERE leaf_wetness > 0.5) * 1.0 / NULLIF(COUNT(*), 0) AS leaf_wetness_hours
FROM TUMBLE(weather_raw, reading_time, INTERVAL '1 HOUR')
GROUP BY station_id, window_start, window_end;

CREATE MATERIALIZED VIEW field_soil_1h AS
SELECT field_id, window_start, window_end,
       AVG(soil_moisture_pct) FILTER (WHERE depth_cm = 30) AS avg_moisture_30cm,
       MIN(soil_moisture_pct) FILTER (WHERE depth_cm = 30) AS min_moisture_30cm,
       AVG(ec_dscm) AS avg_ec, MAX(ec_dscm) AS max_ec, AVG(soil_temp_c) AS avg_soil_temp, AVG(canopy_temp_c) AS avg_canopy_temp,
       COUNT(DISTINCT sensor_id) AS active_sensors
FROM TUMBLE(field_sensors, reading_time, INTERVAL '1 HOUR')
GROUP BY field_id, window_start, window_end;

CREATE MATERIALIZED VIEW field_weather_impact_1h AS
SELECT f.field_id, f.farm_id, f.crop_type, f.growth_stage, w.window_start,
       w.avg_temp_c, w.min_temp_c, w.max_temp_c, w.avg_humidity, w.total_rainfall_mm, w.leaf_wetness_hours, s.avg_moisture_30cm,
       GREATEST(0, LEAST(w.avg_temp_c, t.max_temp_c) - t.base_temp_c) / 24.0 AS gdd_contribution,
       CASE WHEN w.min_temp_c <= t.frost_threshold_c                                           THEN 'FROST_RISK'
            WHEN w.max_temp_c >= t.heat_stress_c                                               THEN 'HEAT_STRESS'
            WHEN w.avg_humidity > 85 AND w.leaf_wetness_hours > t.max_leaf_wetness_h            THEN 'DISEASE_PRESSURE'
            WHEN w.total_rainfall_mm > 20 AND s.avg_moisture_30cm > t.max_moisture_pct          THEN 'WATERLOGGING_RISK'
            ELSE 'NORMAL' END AS weather_stress_class
FROM field_registry f
JOIN weather_indices_1h w ON f.weather_station_id = w.station_id
JOIN crop_thresholds t ON f.crop_type = t.crop_type AND f.growth_stage = t.growth_stage
LEFT JOIN field_soil_1h s ON f.field_id = s.field_id AND w.window_start = s.window_start;

CREATE MATERIALIZED VIEW field_gdd_season AS
SELECT field_id, crop_type, SUM(gdd_contribution) AS gdd_accumulated, MIN(window_start) AS first_hour, MAX(window_start) AS last_hour
FROM field_weather_impact_1h
WHERE window_start >= DATE_TRUNC('year', NOW())
GROUP BY field_id, crop_type;

Accumulated growing degree days per field, not calendar days, is what predicts the next phenological stage, and the season view keeps the running total without a nightly job. Frost risk from the hourly minimum arrives an hour late for a frost at 3 AM; a 15-minute view over the same source with a projected drop rate, temp_c minus the LAG reading, is the early warning that triggers protection irrigation in time.

Crop Monitoring: Soil, Salinity, and NDVI Decline

Soil moisture at 30 centimeters against the stage threshold catches drought stress and waterlogging. Electrical conductivity drift catches salt stress and nutrient problems that a targeted fertigation fixes cheaply while a late foliar rescue does not. NDVI from Sentinel-2 or drones, joined to the most recent window within four hours, catches a rapid decline across a cluster of fields that indicates early fungal infection spreading to adjacent blocks.

CREATE MATERIALIZED VIEW field_crop_status_30m AS
SELECT s.field_id, f.crop_type, f.growth_stage, window_start, window_end,
       AVG(s.soil_moisture_pct) FILTER (WHERE s.depth_cm = 30) AS avg_moisture_30cm,
       MIN(s.soil_moisture_pct) FILTER (WHERE s.depth_cm = 30) AS min_moisture_30cm,
       AVG(s.ec_dscm) AS avg_ec, MAX(s.ec_dscm) AS max_ec, AVG(s.soil_temp_c) AS avg_soil_temp,
       COUNT(DISTINCT s.sensor_id) AS active_sensors,
       t.min_moisture_pct, t.max_moisture_pct, t.max_ec_dscm
FROM TUMBLE(field_sensors, reading_time, INTERVAL '30 MINUTES') s
JOIN field_registry f ON s.field_id = f.field_id
JOIN crop_thresholds t ON f.crop_type = t.crop_type AND f.growth_stage = t.growth_stage
GROUP BY s.field_id, f.crop_type, f.growth_stage, window_start, window_end, t.min_moisture_pct, t.max_moisture_pct, t.max_ec_dscm;

CREATE MATERIALIZED VIEW latest_ndvi AS
SELECT DISTINCT ON (field_id) field_id, image_time, ndvi_score, ndvi_change_7d, source
FROM ndvi_updates
ORDER BY field_id, image_time DESC;

CREATE MATERIALIZED VIEW crop_health_live AS
SELECT f.field_id, f.crop_type, f.growth_stage, f.window_start, f.avg_moisture_30cm, f.avg_ec, f.active_sensors,
       n.ndvi_score, n.ndvi_change_7d, n.image_time AS ndvi_time,
       CASE WHEN f.avg_moisture_30cm < f.min_moisture_pct THEN 'DROUGHT_STRESS'
            WHEN f.avg_moisture_30cm > f.max_moisture_pct THEN 'WATERLOGGED'
            WHEN f.avg_ec > f.max_ec_dscm                 THEN 'SALT_STRESS'
            WHEN n.ndvi_change_7d < -0.08                 THEN 'RAPID_DECLINE'
            ELSE 'HEALTHY' END AS stress_class
FROM field_crop_status_30m f
LEFT JOIN latest_ndvi n ON f.field_id = n.field_id;

CREATE MATERIALIZED VIEW crop_alerts AS
SELECT field_id, crop_type, growth_stage, window_start AS alert_time, stress_class AS alert_type, avg_moisture_30cm, avg_ec, ndvi_score, ndvi_change_7d
FROM crop_health_live
WHERE stress_class <> 'HEALTHY';

Grouping crop_alerts by farm and counting fields in RAPID_DECLINE within the same hour turns a field-level signal into the block-level scouting trigger that matters for disease. An active_sensors count that drops below the expected number is a gateway or battery problem, not a crop problem, and it belongs on the same dashboard.

Smart Irrigation: Demand Index and Valve Decisions

Irrigation decisions combine 15-minute zone moisture against the stress and saturation thresholds with crop evapotranspiration, the crop coefficient times reference ET, and recent rainfall. The controller microservice reads the decision view over PostgreSQL or consumes the command sink, actuates valves, and logs every state change back through valve_events for compliance, billing, and water accounting.

CREATE MATERIALIZED VIEW zone_moisture_15m AS
SELECT s.field_id, s.valve_zone_id, window_start, window_end,
       AVG(s.soil_moisture_pct) FILTER (WHERE s.depth_cm = 30) AS avg_moisture_30cm,
       MIN(s.soil_moisture_pct) FILTER (WHERE s.depth_cm = 30) AS min_moisture_30cm,
       COUNT(DISTINCT s.sensor_id) AS active_sensors,
       c.crop_type, c.target_moisture_pct, c.stress_threshold_pct, c.saturation_pct, c.kc_factor
FROM TUMBLE(field_sensors, reading_time, INTERVAL '15 MINUTES') s
JOIN field_irrigation_config c ON s.field_id = c.field_id AND s.valve_zone_id = c.valve_zone_id
GROUP BY s.field_id, s.valve_zone_id, window_start, window_end, c.crop_type, c.target_moisture_pct, c.stress_threshold_pct, c.saturation_pct, c.kc_factor;

CREATE MATERIALIZED VIEW station_weather_15m AS
SELECT station_id, window_start, window_end, SUM(et0_mm) AS et0_mm, SUM(rainfall_mm) AS rainfall_mm
FROM TUMBLE(weather_raw, reading_time, INTERVAL '15 MINUTES')
GROUP BY station_id, window_start, window_end;

CREATE MATERIALIZED VIEW irrigation_demand AS
SELECT m.field_id, m.valve_zone_id, m.window_start, m.crop_type, m.avg_moisture_30cm, m.stress_threshold_pct, m.saturation_pct,
       w.et0_mm, w.rainfall_mm,
       m.kc_factor * w.et0_mm AS crop_et_mm,
       m.avg_moisture_30cm - m.stress_threshold_pct AS moisture_margin,
       CASE WHEN m.avg_moisture_30cm <= m.stress_threshold_pct AND w.rainfall_mm < 2.0 THEN 'IRRIGATE'
            WHEN m.avg_moisture_30cm >= m.saturation_pct                                THEN 'SATURATED_SKIP'
            WHEN w.rainfall_mm >= 5.0                                                   THEN 'RAIN_SKIP'
            ELSE 'MONITOR' END AS irrigation_decision
FROM zone_moisture_15m m
JOIN field_registry f ON m.field_id = f.field_id
JOIN station_weather_15m w ON f.weather_station_id = w.station_id AND m.window_start = w.window_start;

CREATE MATERIALIZED VIEW valve_current_state AS
SELECT DISTINCT ON (valve_id) valve_id, valve_zone_id, field_id, state, commanded_by, flow_rate_lpm, event_time AS since
FROM valve_events
ORDER BY valve_id, event_time DESC;

CREATE MATERIALIZED VIEW irrigation_commands AS
SELECT d.field_id, d.valve_zone_id, d.window_start AS decision_time, d.irrigation_decision, d.avg_moisture_30cm, d.crop_et_mm, d.rainfall_mm,
       v.valve_id, v.state AS current_valve_state
FROM irrigation_demand d
LEFT JOIN valve_current_state v ON d.valve_zone_id = v.valve_zone_id
WHERE (d.irrigation_decision = 'IRRIGATE' AND COALESCE(v.state, 'CLOSED') = 'CLOSED')
   OR (d.irrigation_decision IN ('SATURATED_SKIP', 'RAIN_SKIP') AND v.state = 'OPEN');

CREATE MATERIALIZED VIEW water_applied_daily AS
SELECT field_id, valve_zone_id, window_start::DATE AS day,
       SUM(flow_rate_lpm * EXTRACT(EPOCH FROM (LEAD(event_time) OVER (PARTITION BY valve_id ORDER BY event_time) - event_time)) / 60.0)
           FILTER (WHERE state = 'OPEN') AS liters_applied
FROM TUMBLE(valve_events, event_time, INTERVAL '1 DAY')
GROUP BY field_id, valve_zone_id, window_start::DATE;

The command view only emits when the decision disagrees with the valve's current state, so the controller never receives a redundant open or an open while a manual emergency close is in effect. Liters applied per zone per day, from the open-to-close durations times flow rate, is the water accounting that both the water board and the agronomist want.

Livestock Health: Baselines, Fever, Activity, and Rumination

RFID ear tags and biosensors stream temperature, activity, and rumination per animal. A 30-minute baseline per animal gives the reference; each reading is compared against the species fever threshold, a 40 percent activity drop from baseline, and rumination under 30 minutes per hour, and the flags combine into a severity for the herd management system.

CREATE MATERIALIZED VIEW animal_baselines AS
SELECT animal_id, species, window_start, window_end,
       AVG(temp_celsius) AS avg_temp, AVG(activity_index) AS avg_activity, AVG(rumination_min) AS avg_rumination, COUNT(*) AS reading_count
FROM TUMBLE(livestock_telemetry, event_time, INTERVAL '30 MINUTES')
GROUP BY animal_id, species, window_start, window_end;

CREATE MATERIALIZED VIEW animal_health_scores AS
SELECT t.tag_id, t.animal_id, t.species, t.zone_id, t.event_time, t.temp_celsius, t.activity_index, t.rumination_min, b.avg_temp, b.avg_activity,
       CASE t.species WHEN 'cattle' THEN t.temp_celsius > 39.5 WHEN 'sheep' THEN t.temp_celsius > 40.0
                      WHEN 'pig' THEN t.temp_celsius > 40.5 ELSE t.temp_celsius > 40.0 END AS fever_flag,
       CASE WHEN b.avg_activity > 0 THEN (b.avg_activity - t.activity_index) / b.avg_activity > 0.4 ELSE FALSE END AS low_activity_flag,
       t.rumination_min < 30 AS low_rumination_flag
FROM livestock_telemetry t
LEFT JOIN animal_baselines b ON t.animal_id = b.animal_id AND t.event_time >= b.window_start AND t.event_time < b.window_end;

CREATE MATERIALIZED VIEW livestock_health_alerts AS
SELECT tag_id, animal_id, species, zone_id, event_time, temp_celsius, activity_index, rumination_min, fever_flag, low_activity_flag, low_rumination_flag,
       CASE WHEN fever_flag AND low_activity_flag THEN 'CRITICAL'
            WHEN fever_flag OR (low_activity_flag AND low_rumination_flag) THEN 'WARNING' ELSE 'INFO' END AS severity
FROM animal_health_scores
WHERE fever_flag OR low_activity_flag OR low_rumination_flag;

CREATE MATERIALIZED VIEW zone_fever_count_1h AS
SELECT zone_id, window_start, window_end, COUNT(DISTINCT animal_id) FILTER (WHERE fever_flag) AS febrile_animals, COUNT(DISTINCT animal_id) AS animals_seen
FROM TUMBLE(animal_health_scores, event_time, INTERVAL '1 HOUR')
GROUP BY zone_id, window_start, window_end;

A single febrile animal is a treatment case; three in the same barn zone within an hour, from zone_fever_count_1h, is a possible outbreak and a quarantine decision. Estrus detection uses the inverse activity pattern, a sustained rise above baseline, over a longer window, and it is the same view with the sign flipped.

Water Quality: Compliance Windows, Breach Classes, and Source Correlation

Irrigation districts and utilities face regulatory limits on turbidity, pH, conductivity, dissolved oxygen, chlorine, and nitrate. Five-minute windows per sensor, joined to limits by node location and use type, classify each window; the upstream sensor in the topology table tells you where a breach entered the network.

CREATE MATERIALIZED VIEW water_quality_5m AS
SELECT sensor_id, node_location, field_id, window_start, window_end,
       AVG(turbidity_ntu) AS avg_turbidity, MAX(turbidity_ntu) AS max_turbidity,
       AVG(ph_value) AS avg_ph, MIN(ph_value) AS min_ph, MAX(ph_value) AS max_ph,
       AVG(ec_dscm) AS avg_ec, AVG(do_mgl) AS avg_do, AVG(chlorine_mgl) AS avg_chlorine, AVG(nitrate_mgl) AS avg_nitrate, COUNT(*) AS sample_count
FROM TUMBLE(water_quality_raw, reading_time, INTERVAL '5 MINUTES')
GROUP BY sensor_id, node_location, field_id, window_start, window_end;

CREATE MATERIALIZED VIEW water_quality_compliance AS
SELECT q.sensor_id, q.node_location, q.field_id, q.window_start, q.avg_turbidity, q.avg_ph, q.avg_ec, q.avg_do, q.avg_chlorine, q.avg_nitrate, l.use_type, sn.upstream_sensor,
       CASE WHEN q.avg_turbidity > l.max_turbidity          THEN 'TURBIDITY_BREACH'
            WHEN q.avg_ph < l.min_ph OR q.avg_ph > l.max_ph  THEN 'PH_BREACH'
            WHEN q.avg_ec > l.max_ec_dscm                   THEN 'EC_BREACH'
            WHEN q.avg_do < l.min_do_mgl                    THEN 'LOW_DO'
            WHEN q.avg_chlorine < l.min_chlorine_mgl        THEN 'LOW_CHLORINE'
            WHEN q.avg_nitrate > l.max_nitrate_mgl          THEN 'NITRATE_BREACH'
            ELSE 'COMPLIANT' END AS compliance_status
FROM water_quality_5m q
JOIN sensor_network sn USING (sensor_id)
JOIN water_quality_limits l ON q.node_location = l.node_location AND sn.use_type = l.use_type;

CREATE MATERIALIZED VIEW water_quality_alerts AS
SELECT c.sensor_id, c.node_location, c.field_id, c.window_start AS alert_time, c.compliance_status AS alert_type,
       c.avg_turbidity, c.avg_ph, c.avg_ec, c.avg_nitrate,
       u.compliance_status AS upstream_status
FROM water_quality_compliance c
LEFT JOIN water_quality_compliance u ON c.upstream_sensor = u.sensor_id AND c.window_start = u.window_start
WHERE c.compliance_status <> 'COMPLIANT';

If the upstream sensor is compliant in the same window and the downstream one breaches, the problem entered between them. If both breach, look further up. A gradual EC or pH drift that stays inside the limits is sensor fouling or a dosing system degrading, and a LAG comparison across hourly averages flags it before the limit is crossed. The raw readings in Kafka plus the compliance view are the continuous log that the Safe Drinking Water Act and the EU Water Framework Directive reporting expect.

Routing Alerts to the Farm

CREATE SINK weather_impact_alerts_sink AS
SELECT field_id, farm_id, crop_type, window_start AS alert_time, weather_stress_class AS alert_type, min_temp_c, max_temp_c, leaf_wetness_hours
FROM field_weather_impact_1h WHERE weather_stress_class <> 'NORMAL'
WITH (connector = 'kafka', topic = 'agri.weather.alerts', properties.bootstrap.server = 'broker:9092') FORMAT PLAIN ENCODE JSON;

CREATE SINK crop_alerts_sink FROM crop_alerts
WITH (connector = 'kafka', topic = 'agri.crop.alerts', properties.bootstrap.server = 'broker:9092') FORMAT PLAIN ENCODE JSON;

CREATE SINK irrigation_commands_sink FROM irrigation_commands
WITH (connector = 'kafka', topic = 'agri.irrigation.commands', properties.bootstrap.server = 'broker:9092') FORMAT PLAIN ENCODE JSON;

CREATE SINK livestock_alerts_sink FROM livestock_health_alerts
WITH (connector = 'kafka', topic = 'farm.health.alerts', properties.bootstrap.server = 'broker:9092') FORMAT PLAIN ENCODE JSON;

CREATE SINK water_quality_alerts_sink FROM water_quality_alerts
WITH (connector = 'kafka', topic = 'water.quality.alerts', properties.bootstrap.server = 'broker:9092') FORMAT PLAIN ENCODE JSON;

CREATE SINK water_applied_sink FROM water_applied_daily
WITH (connector = 'jdbc', jdbc.url = 'jdbc:postgresql://farm-db:5432/ops', table.name = 'water_applied_daily', type = 'upsert', primary_key = 'field_id,valve_zone_id,day');

Farm management platforms such as AgWorld or Trimble, and any custom Python script, query crop_health_live and irrigation_demand with ordinary SELECT statements. Hourly weather indices and compliance windows archived to Iceberg through a sink become the season-over-season dataset for yield models.

Nightly Batch vs Streaming SQL on the Farm

SignalNightly batchConsequenceStreaming SQL (RisingWave)
FrostSeen next morningBurnt foliageHourly minimum plus 15-minute drop rate, protection irrigation in time
Drought stress12 to 18 hours late10 to 40 percent yield loss at flowering30-minute moisture vs stage threshold
IrrigationTimer scheduleWater waste or stress15-minute demand index with ET and rainfall, command only on state change
DiseaseScouting after visible symptomsSpread to adjacent blocksLeaf wetness hours and NDVI decline, block-level trigger
FeverDaily reportMissed treatment window, herd spreadPer-reading species threshold against a 30-minute baseline
Water qualityDaily lab sampleRegulatory penalty, crop damageFive-minute compliance per sensor with upstream correlation

Frequently Asked Questions

Can sensors connect over MQTT instead of Kafka? RisingWave has a native MQTT source connector, so field gateways can publish directly. Kafka remains useful as the buffer when thousands of sensors report and as the fan-out point for other consumers.

How do I handle sensors at different depths? Keep depth_cm on the reading and use FILTER (WHERE depth_cm = 30) for the root zone metric, as the views do. Add a 60-centimeter filter to the same view for deep drainage and a 10-centimeter one for germination.

Does RisingWave compute reference evapotranspiration? The views take et0_mm from the weather station, which most modern stations compute with Penman-Monteith. If yours does not, compute it in a user-defined function from temperature, humidity, wind, and solar radiation, or in a small service that writes it back to the weather topic.

How is a growth stage change applied? Update the row in field_registry. Because every threshold join goes through crop type and growth stage, the weather impact, crop status, and alert views re-evaluate against the new limits on the next window.

How long is the state retained? Windowed views keep state only for open windows plus allowed lateness. The season growing degree day view filters to the current year and resets at the new year. Archive hourly indices and compliance windows to Iceberg through a sink for multi-season history.

The engine for real-time streaming data.
Analyze, search, and decide.
GitHubXLinkedInSlackYouTube
Sign up for our to stay updated.