Skip to content

Instantly share code, notes, and snippets.

Avatar
💭
Happily Coding!

Gary A. Stafford garystafford

💭
Happily Coding!
View GitHub Profile
View healthy_home.csv
We can make this file beautiful and searchable if this error is corrected: Unclosed quoted field in line 7.
"ts_utc","ts_local","wirelessdeviceid","battery","board_temp","rh","env_temp","voc","iaq","eco2","rssi","snr","sensor_location"
"2021-04-12T18:38:52.000Z","2021-04-12T14:38:52.000-04:00","3a3c05d6-a3a3-434f-c5f9-47aa04d41117","3.6","69.8","53","68.0","1","82","730","-73","9.25","Second floor office"
"2021-04-12T18:39:21.000Z","2021-04-12T14:39:21.000-04:00","1ec1fe6d-9db0-2334-dee3-48a58d821704","3.6","68.0","57","66.2","1","93","1088","-78","10.0","First floor living room"
"2021-04-12T18:43:52.000Z","2021-04-12T14:43:52.000-04:00","3a3c05d6-a3a3-434f-c5f9-47aa04d41117","3.6","69.8","52","68.0","1","81","715","-73","10.0","Second floor office"
"2021-04-12T18:44:21.000Z","2021-04-12T14:44:21.000-04:00","1ec1fe6d-9db0-2334-dee3-48a58d821704","3.6","68.0","56","66.2","1","95","1104","-76","7.0","First floor living room"
"2021-04-12T18:48:52.000Z","2021-04-12T14:48:52.000-04:00","3a3c05d6-a3a3-434f-c5f9-47aa04d41117","3.6","69.8","52","68.0","1","93","814","-75","9.0","Second floor office"
"2021-04-12T18:49:21.00
View tbhv110_decoder.py
def dict_from_payload(base64_input: str, fport: int = None):
""" Healthy Home Sensor IAQ (TBHV110) binary payload decoder """
decoded = base64.b64decode(base64_input)
# Byte 0, bit 0
status = decoded[0] & 0b00000001 # (1 << 1) - 1
# Byte 1, bits 3:0
battery = decoded[1] & 0b00001111 # (1 << 4) - 1
View decoding_lorawan.txt
# base64 encoded binary message
AAs0LNsCAQB/ADM=
# base64 decoded binary message
b'\x00\x0b4,\xdb\x02\x01\x00\x7f\x003'
bytes hex dec binary
--------------------------------
1 00 0 00000000
2 0b 11 00001011
View decoder_method.py
def dict_from_payload(base64_input: str, fport: int = None):
""" Healthy Home Sensor IAQ (TBHV110) binary payload decoder """
decoded = base64.b64decode(base64_input)
# Byte 1
status = decoded[0] & 0b00000001
# Byte 2
battery = decoded[1] & 0b00001111
View lorawan_message_raw.json
{
"WirelessDeviceId": "3a3c05d6-a3a3-434f-c5f9-47aa04d41117",
"PayloadData": "AAs0LNsCAQB/ADM=",
"WirelessMetadata": {
"LoRaWAN": {
"DataRate": "3",
"DevEui": "40bb42d1c49fa3c1",
"FCnt": 1426,
"FPort": 103,
"Frequency": "904500000",
View lorawan_message_final.json
{
"WirelessDeviceId": "3a3c05d6-a3a3-434f-c5f9-47aa04d41117",
"PayloadData": {
"Status": 0,
"Battery": 3.6,
"BoardTemp": 20,
"RH": 44,
"ECO2": 731,
"VOC": 1,
"IAQ": 127,
View iot_analytics_query.sql
SELECT to_iso8601(date_parse(wirelessmetadata.lorawan."timestamp",
'%Y-%m-%dT%H:%i:%SZ') AT TIME ZONE 'UTC') AS ts_utc,
to_iso8601(date_parse(wirelessmetadata.lorawan."timestamp",
'%Y-%m-%dT%H:%i:%SZ') AT TIME ZONE 'America/New_York') AS ts_local,
wirelessdeviceid,
payloaddata.battery,
round(((payloaddata.boardtemp * 1.8) + 32), 2) AS board_temp,
payloaddata.rh,
round(((payloaddata.envtemp * 1.8) + 32), 2) AS env_temp,
payloaddata.voc,
View lorawn_iot_rule.sql
SELECT WirelessDeviceId, WirelessMetadata, "tbhv110_915" as PayloadDecoderName,
aws_lambda("arn:aws:lambda:us-east-1:111222333444:function:lorawan-iot-core-TransformLoRaWANBinaryPayloadFunc-RQOC3C6CKGWZ",
{"PayloadDecoderName": "tbhv110_915",
"PayloadData": PayloadData,
"WirelessDeviceId": WirelessDeviceId,
"WirelessMetadata": WirelessMetadata}) as PayloadData
View sample_message.json
{
"data": {
"co": 0.0031827073092533685,
"humidity": 51.099998474121094,
"light": true,
"lpg": 0.005553622262501496,
"motion": false,
"smoke": 0.01449612738171321,
"temperature": 19.100000381469727
},
View add_continuous_aggregate_policy.sql
-- create policies that automatically refreshes continuous aggregates
SELECT add_continuous_aggregate_policy('air_quality_summary_minute',
start_offset => INTERVAL '1 week',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
SELECT add_continuous_aggregate_policy('light_summary_minute',
start_offset => INTERVAL '1 week',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');