From f461190e22a97bde985b067ce01c4ed9c74f7e62 Mon Sep 17 00:00:00 2001 From: pks Date: Sat, 1 May 2021 06:17:55 +0200 Subject: v1 --- v1/DEVICES | 10 ++++++ v1/README | 11 ++++++ v1/create-db.rb | 51 ++++++++++++++++++++++++++++ v1/fill-db-from-log.rb | 90 ++++++++++++++++++++++++++++++++++++++++++++++++++ v1/fill-db.rb | 71 +++++++++++++++++++++++++++++++++++++++ v1/get-temps.rb | 21 ++++++++++++ v1/get-temps.rb.bak | 16 +++++++++ v1/query-db.rb | 19 +++++++++++ v1/receive-test.py | 15 +++++++++ v1/receive.py | 61 ++++++++++++++++++++++++++++++++++ v1/run | 5 +++ v1/test.rb | 27 +++++++++++++++ 12 files changed, 397 insertions(+) create mode 100644 v1/DEVICES create mode 100644 v1/README create mode 100755 v1/create-db.rb create mode 100755 v1/fill-db-from-log.rb create mode 100755 v1/fill-db.rb create mode 100755 v1/get-temps.rb create mode 100755 v1/get-temps.rb.bak create mode 100755 v1/query-db.rb create mode 100755 v1/receive-test.py create mode 100755 v1/receive.py create mode 100755 v1/run create mode 100644 v1/test.rb (limited to 'v1') diff --git a/v1/DEVICES b/v1/DEVICES new file mode 100644 index 0000000..295a76a --- /dev/null +++ b/v1/DEVICES @@ -0,0 +1,10 @@ +Berlin +====== +- Inside: THGR810, THGN800 +- Outside: BTHR918 + +HDH +=== +- Wind '04:00' +- Rain 'eb:00', '7d:00', '02:00' +- Temp '10:02', 'ec:01', '25:01', '62:02', 'd9:00', '2a:0e', '60:01' diff --git a/v1/README b/v1/README new file mode 100644 index 0000000..fb0448f --- /dev/null +++ b/v1/README @@ -0,0 +1,11 @@ +Dependencies: + Ruby + sqlite3 + Python + pyRFXtrx + +``` +./create-db.rb +`` + +Time is in UTC. diff --git a/v1/create-db.rb b/v1/create-db.rb new file mode 100755 index 0000000..2866b7b --- /dev/null +++ b/v1/create-db.rb @@ -0,0 +1,51 @@ +#!/usr/bin/env ruby + +require 'sqlite3' + +db = SQLite3::Database.new("weather.db") + +db.execute <<-SQL + create table rain( + id INTEGER PRIMARY KEY, + timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, + device_type TEXT, + device_id TEXT, + battery INTEGER, + rssi INTEGER, + rain_rate FLOAT, + rain_total FLOAT + ); + SQL +db.execute <<-SQL + create table wind( + id INTEGER PRIMARY KEY, + timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, + device_type TEXT, + device_id TEXT, + battery INTEGER, + rssi INTEGER, + chill FLOAT, + temperature FLOAT, + average_speed FLOAT, + direction INTEGER, + gust FLOAT + ); + SQL +db.execute <<-SQL + create table temp( + id INTEGER PRIMARY KEY, + timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, + device_type TEXT, + device_id TEXT, + battery INTEGER, + rssi INTEGER, + humidity FLOAT, + humidity_status TEXT, + humidity_status_numeric INTEGER, + temperature FLOAT, + forecast TEXT, + forecast_numeric INTEGER + ); + SQL + +db.close diff --git a/v1/fill-db-from-log.rb b/v1/fill-db-from-log.rb new file mode 100755 index 0000000..1fb2ab3 --- /dev/null +++ b/v1/fill-db-from-log.rb @@ -0,0 +1,90 @@ +#!/usr/bin/env ruby + +require 'sqlite3' + + +int = Proc.new do |x| + x.to_i +end +float = Proc.new do |x| + x.to_f +end +str = Proc.new do |x| + x.to_s +end +common_conv = { "db" => str, "timestamp"=>float, "device_type"=>str, "type"=>str, "device_id"=>str, "battery" => int, "battery_numeric" => int, "rssi" => int, "rssi_numeric" => int } +rain_conv = {"rain_rate"=>float, "rain_total"=>float}.merge(common_conv) +wind_conv = {"chill"=>float, "temperature"=>float,"average_speed"=>float,"direction"=>int,"gust"=>float}.merge(common_conv) +temp_conv = {"humidity"=>float, "humidity_status"=>str,"humidity_status_numeric"=>int,"temperature"=>float,"barometer"=>float,"forecast"=>str,"forecast_numeric"=>int}.merge(common_conv) +conv = { "rain" => rain_conv, "wind" => wind_conv, "temp" => temp_conv } + +$db = SQLite3::Database.new("weather.db") + + +def parse_to_hash s, conv, type + h = {} + begin + s.split("\t").map { |i| + i=i.split("::") + if not conv.keys.include? i[0] + STDERR.write "Unknown key '#{i[0]}' in data point of type '#{type}', ignoring!\n" + end + h[i[0]] = conv[i[0]].call(i[1]) + } + rescue + STDERR.write "Cannot parse '#{s.strip}', skipping data point!\n" + return false + end + return h +end + + +def db_execute s, d + begin + $db.execute(s, d) + rescue SQLite3::BusyException + sleep 3 + begin + $db.execute(s, d) + rescue SQLite3::BusyException + STDERR.write "DB busy, skipping data point '#{d.to_s}'\n" + end + end +end + + +def insert_rain h + db_execute("INSERT INTO rain(timestamp, device_type, device_id, battery, rssi, rain_rate, rain_total) VALUES(?,?,?,?,?,?,?)", [h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["rain_rate"], h["rain_total"]]) +end + + +def insert_wind h + db_execute("INSERT INTO wind(timestamp, device_type, device_id, battery, rssi, chill, temperature, average_speed, direction, gust) VALUES(?,?,?,?,?,?,?,?,?,?)", [h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["chill"], h["temperature"], h["average_speed"], h["direction"], h["gust"]]) +end + + +def insert_temp h + if h.keys.include? "forecast" + db_execute("INSERT INTO temp(timestamp, device_type, device_id, battery, rssi, humidity, humidity_status, humidity_status_numeric, temperature, forecast, forecast_numeric) VALUES(?,?,?,?,?,?,?,?,?,?,?)",[h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["humidity"], h["humidity_status"], h["humidity_status_numeric"], h["temperature"], h["forecast"], h["forecast_numeric"]]) + else + db_execute("INSERT INTO temp(timestamp, device_type, device_id, battery, rssi, humidity, humidity_status, humidity_status_numeric, temperature) VALUES(?,?,?,?,?,?,?,?,?)",[h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["humidity"], h["humidity_status"], h["humidity_status_numeric"], h["temperature"]]) + end +end + + +while line = STDIN.gets + break if line.strip=="stop" + type = line.split("\t").first.split("::")[1] + h = parse_to_hash(line, conv[type], type) + if h + if type == "rain" + insert_rain h + elsif type == "wind" + insert_wind h + elsif type == "temp" + insert_temp h + end + end +end + +$db.close diff --git a/v1/fill-db.rb b/v1/fill-db.rb new file mode 100755 index 0000000..1bfc0f2 --- /dev/null +++ b/v1/fill-db.rb @@ -0,0 +1,71 @@ +#!/usr/bin/env ruby + +require 'sqlite3' + +int = Proc.new do |x| + x.to_i +end +float = Proc.new do |x| + x.to_f +end +str = Proc.new do |x| + x.to_s +end +common_conv = { "db" => str, "timestamp"=>float, "device_type"=>str, "type"=>str, "device_id"=>str, "battery" => int, "battery_numeric" => int, "rssi" => int, "rssi_numeric" => int } +rain_conv = {"rain_rate"=>float, "rain_total"=>float}.merge(common_conv) +wind_conv = {"chill"=>float, "temperature"=>float,"average_speed"=>float,"direction"=>int,"gust"=>float}.merge(common_conv) +temp_conv = {"humidity"=>float, "humidity_status"=>str,"humidity_status_numeric"=>int,"temperature"=>float}.merge(common_conv) +conv = { "rain" => rain_conv, "wind" => wind_conv, "temp" => temp_conv } + +$db = SQLite3::Database.new("db/weather.db") + +def parse_to_hash s, conv + h = {} + s.split("\t").map { |i| i=i.split("::"); h[i[0]] = conv[i[0]].call(i[1]) } + return h +end + +def db_execute s, d + begin + $db.execute(s, d) + rescue SQLite3::BusyException + sleep 3 + begin + $db.execute(s, d) + rescue SQLite3::BusyException + STDERR.write "db busy, skipping data point '#{d.to_s}'\n" + end + end +end + +def insert_rain h + #db::rain type::PCR800 device_id::eb:00 battery::9 rain_rate::0.0 rain_total::0.9 rssi::6 + db_execute("INSERT INTO rain(timestamp, device_type, device_id, battery, rssi, rain_rate, rain_total) VALUES(?,?,?,?,?,?,?)", [h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["rain_rate"], h["rain_total"]]) +end + +def insert_wind h + # db::wind type::WGR800 device_id::04:00 battery::9 chill::0.0 rssi::5 temperature::0.0 average_speed::0.0 direction::270gust::0.0 + db_execute("INSERT INTO wind(timestamp, device_type, device_id, battery, rssi, chill, temperature, average_speed, direction, gust) VALUES(?,?,?,?,?,?,?,?,?,?)", [h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["chill"], h["temperature"], h["average_speed"], h["direction"], h["gust"]]) +end + +def insert_temp h + #db::temp type::THGR810, THGN800 device_id::10:02 battery::9 humidity::80 humidity_status::wet humidity_status_numeric::3 rssi::5 temperature::2.6 + #db::temp type::THGR810, THGN800 device_id::25:01 battery::0 humidity::94 humidity_status::wet humidity_status_numeric::3 rssi::6 temperature::1.1 + db_execute("INSERT INTO temp(timestamp, device_type, device_id, battery, rssi, humidity, humidity_status, humidity_status_numeric, temperature) VALUES(?,?,?,?,?,?,?,?,?)",[h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["humidity"], h["humidity_status"], h["humidity_status_numeric"], h["temperature"]]) +end + +while line = STDIN.gets + break if line.strip=="stop" + db = line.split("\t").first.split("::")[1] + h = parse_to_hash(line, conv[db]) + if db == "rain" + insert_rain h + elsif db == "wind" + insert_wind h + elsif db == "temp" + insert_temp h + end +end + +$db.close + diff --git a/v1/get-temps.rb b/v1/get-temps.rb new file mode 100755 index 0000000..f25540b --- /dev/null +++ b/v1/get-temps.rb @@ -0,0 +1,21 @@ +#!/usr/bin/env ruby + +$devices = { "f4:01" => "outside", "1f:01" => "inside" } + +while line = STDIN.gets + a = line.split + d = {} + a.each { |i| + name, val = i.split("::") + if name == "timestamp" + d["timestamp"] = val.to_f + end + if name == "temperature" + d["temperature"] = val.to_f + end + if name == "device_id" + d["dev"] = devices[val] + end + } + puts d.to_s +end diff --git a/v1/get-temps.rb.bak b/v1/get-temps.rb.bak new file mode 100755 index 0000000..ac24051 --- /dev/null +++ b/v1/get-temps.rb.bak @@ -0,0 +1,16 @@ +#!/usr/bin/env ruby + +while line = STDIN.gets + a = line.split + d = {} + a.each { |i| + name, val = i.split("::") + if name == "temperature" + d["temperature"] = val + end + if name == "device_id" + d["dev"] = val + end + } + puts d.to_s +end diff --git a/v1/query-db.rb b/v1/query-db.rb new file mode 100755 index 0000000..984a59d --- /dev/null +++ b/v1/query-db.rb @@ -0,0 +1,19 @@ +#!/usr/bin/env ruby + +require 'sqlite3' + +db = SQLite3::Database.new("weather.db") + +#db.execute("select * from rain") do |result| +# puts result.to_s +#end + +#db.execute("select * from wind") do |result| +# puts result.to_s +#end + +db.execute("select * from temp") do |result| + puts result.to_s +end + +db.close diff --git a/v1/receive-test.py b/v1/receive-test.py new file mode 100755 index 0000000..c1d9783 --- /dev/null +++ b/v1/receive-test.py @@ -0,0 +1,15 @@ +#!/usr/bin/env python3 + +from RFXtrx import PySerialTransport +import sys, datetime, os + + +if __name__ == "__main__": + transport = PySerialTransport('/dev/ttyUSB0') + transport.reset() + while True: + if os.path.isfile('./stop'): + break + timestamp = datetime.datetime.timestamp(datetime.datetime.now()) + recv = transport.receive_blocking() + print(str(recv)) diff --git a/v1/receive.py b/v1/receive.py new file mode 100755 index 0000000..71d0a67 --- /dev/null +++ b/v1/receive.py @@ -0,0 +1,61 @@ +#!/usr/bin/env python3 + +from RFXtrx import PySerialTransport +import sys, datetime, os + + +if __name__ == "__main__": + transport = PySerialTransport('/dev/ttyUSB0') + transport.reset() + + while True: + if os.path.isfile('/home/pks/weather/stop'): + break + + recv = transport.receive_blocking() + + if not recv: + continue + + timestamp = datetime.datetime.timestamp(datetime.datetime.now()) + + sys.stderr.write("Raw: %s\n"%str(recv)) + + try: + device_id = recv.device.id_string + device_type = recv.device.type_string + except Exception as e: + sys.stderr.write("Cannot find device id, exception %s." % str(e)) + continue + + data = {} + for k,v in recv.values.items(): + data[k.lower().replace(" ", "_")] = v + + data_type = None + if "rain_rate" in data: + data_type = "rain" + elif "wind_gust" in data: + data_type = "wind" + elif "temperature" in data: + data_type = "temp" + else: + sys.stderr.write("Unknown device '%s' with id '%s', ignoring.\n"%(device_type, device_id)) + continue + + + out = [ "type::%s" % data_type, + "timestamp::%f" % timestamp, + "device_type::%s" % device_type, + "device_id::%s" % device_id ] + + for key in sorted(data.keys()): + out.append("%s::%s" % (key, data[key])) + + print("\t".join(out)) + sys.stderr.flush() + sys.stdout.flush() + + +sys.stderr.write("stop\n") +sys.stdout.flush() diff --git a/v1/run b/v1/run new file mode 100755 index 0000000..c346553 --- /dev/null +++ b/v1/run @@ -0,0 +1,5 @@ +#!/usr/bin/zsh -x + +source env/bin/activate +python receive.py 2>received.err | tee received | ./fill-db.rb 2>db.err > db.out + diff --git a/v1/test.rb b/v1/test.rb new file mode 100644 index 0000000..16ab18a --- /dev/null +++ b/v1/test.rb @@ -0,0 +1,27 @@ +int = Proc.new do |x| + x.to_i +end +float = Proc.new do |x| + x.to_f +end +str = Proc.new do |x| + x.to_s +end + +def parse_to_hash s, conv + h = {} + puts s.split("\t").map { |i| i=i.split("::"); "#{i[0]} #{conv[i[0]].call}" } + return h +end + +common_conv = { "db" => str, "timestamp"=>float, "device_type"=>str, "type"=>str, "device_id"=>str, "battery" => int, "battery_numeric" => int, "rssi" => int, "rssi_numeric" => int } +rain_conv = {"rain_rate"=>float, "rain_total"=>float}.merge(common_conv) +wind_conv = {"chill"=>float, "temperature"=>float,"average_speed"=>float,"direction"=>int,"gust"=>float}.merge(common_conv) +temp_conv = {"humidity"=>float, "humidity_status"=>str,"humidity_status_numeric"=>int,"temperature"=>float}.merge(common_conv) +conv = { "rain" => rain_conv, "wind" => wind_conv, "temp" => temp_conv } + +while line = STDIN.gets + db = line.split("\t").first.split("::")[1] + parse_to_hash line, conv[db] +end + -- cgit v1.2.3