The avatar for @tomlarkworthy
Tom Larkworthy
Tech Lead at Taktile. Formerly Firebase, Google
Published
Building a SMART FARM
Edited
Sep 6, 2022
Fork of Building a SMART FARM EP03: Dashboards over WhatsApp
•
2 forks
10 stars
Building a SMART FARM
Building a SMART FARM EP01: Firebase storage backendBuilding a SMART FARM EP02: Blues Wireless IntegrationBuilding a SMART FARM EP03: Dashboards over WhatsApp
Building a SMART FARM EP04: Firebase to DuckDB SQL
Building a SMART FARM EP05: Lambda ArchitectureBuilding a SMART FARM EP06: Productionization (custom domain, error monitoring)
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
liveViewRaw = Generators.observe((notify) => {
db.ref(`history/sensors/${querySensorName}`)
.orderByKey()
.startAt(Math.floor(Date.now() / 1000 - 60 * 60 * 24) + "") // reduce data returned
.on("value", (snapshot) => {
const data = snapshot.val();
if (data) notify(data);
});
})
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
duckdb = (reset, new DuckDBClient())
Insert cell
Insert cell
fullduck = {
// Refresh fullduck every insertion (causes analytics to be rerun)
doInsertRowToDuckDB;
return duckdb;
}
Insert cell
Insert cell
mutable reset = (daysLookback, 0)
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
fullduck
SELECT * FROM readings WEHRE
WHERE time > ${timeStartIso}
ORDER BY time ;
Insert cell
doCreateTable = {
reset;
await duckdb.query(`DROP TABLE IF EXISTS readings`);
return duckdb.query(
`CREATE TABLE IF NOT EXISTS readings(sensor STRING, time TIMESTAMP, temperature FLOAT)`
);
}
Insert cell
Insert cell
lastAnalyticRow = Generators.observe((notify) => {
reset;
const dataHandler = (snapshot) => {
const data = snapshot.val();
console.log(data);
if (data.body.temperature) {
viewof firebaseRow.send(data);
notify(data);
}
};
const ref = db
.ref(`history/sensors/${querySensorName}`)
.orderByKey()
.startAt(timeStart + ""); // reduce data returned

ref.on("child_added", dataHandler);

invalidation.then(() => {
ref.off("child_added", dataHandler);
});
})
Insert cell
Insert cell
viewof firebaseRow = (doCreateTable,
flowQueue({
timeout_ms: 10000000
}))
Insert cell
firebaseRow
Insert cell
Insert cell
doInsertRowToDuckDB = {
doCreateTable;
return duckdb.query(
`INSERT INTO readings VALUES ('${firebaseRow.device}', '${new Date(
(firebaseRow.when || firebaseRow.tower_when) * 1000
).toISOString()}', ${firebaseRow.body.temperature});`
);
}
Insert cell
finishInsert = {
doInsertRowToDuckDB; // Finsih processing everytime we insert a row
viewof firebaseRow.resolve();
}
Insert cell
Insert cell
fullduck

SELECT sensor, startTime, endTime, avgTemperature, endTime - startTime as duration, temperature > 5 AND temperature < 25 AS isGrowth
FROM (
SELECT sensor, time, temperature,
AVG(temperature) OVER lookback avgTemperature,
MIN(time) OVER lookback startTime,
MAX(time) OVER lookback endTime
FROM readings
WHERE time > ${timeStartIso}
WINDOW lookback AS (
ORDER BY "time"
ROWS 1 PRECEDING)
ORDER BY time
)
Insert cell
Insert cell
fullduck
SELECT sensor, CAST(SUM(EXTRACT(epoch from growthDuration)) AS FLOAT) / (60 * 60) as growthHours
FROM (SELECT *, CASE WHEN isGrowth THEN duration ELSE INTERVAL 0 seconds END growthDuration
FROM (
SELECT sensor, startTime, endTime, avgTemperature, endTime - startTime as duration, temperature > 5 AND temperature < 25 AS isGrowth
FROM (
SELECT sensor, time, temperature,
AVG(temperature) OVER lookback avgTemperature,
MIN(time) OVER lookback startTime,
MAX(time) OVER lookback endTime
FROM readings
WHERE time > ${timeStartIso} /* Our uploaded data is indexed wrong so this correct the misindexing */
WINDOW lookback AS (
ORDER BY "time"
ROWS 1 PRECEDING)
ORDER BY time
)
)
) GROUP BY sensor
Insert cell
Insert cell
writeAggregation = {
await firebase.auth().signInAnonymously();
return db
.ref(
`/aggregations/GDD/${querySensorName}/${new Date()
.toISOString()
.substring(0, 10)}`
)
.set(aggregation[0]);
}
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more