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
| Signal | Nightly batch | Consequence | Streaming SQL (RisingWave) |
|---|---|---|---|
| Frost | Seen next morning | Burnt foliage | Hourly minimum plus 15-minute drop rate, protection irrigation in time |
| Drought stress | 12 to 18 hours late | 10 to 40 percent yield loss at flowering | 30-minute moisture vs stage threshold |
| Irrigation | Timer schedule | Water waste or stress | 15-minute demand index with ET and rainfall, command only on state change |
| Disease | Scouting after visible symptoms | Spread to adjacent blocks | Leaf wetness hours and NDVI decline, block-level trigger |
| Fever | Daily report | Missed treatment window, herd spread | Per-reading species threshold against a 30-minute baseline |
| Water quality | Daily lab sample | Regulatory penalty, crop damage | Five-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.
