App Inventor #3 “自己來寫APP 快篩何處買?” (How to process .csv files in App Inventor)

~~更新~~

有網友留言, 兩個Global變數 "search_city" & "search_area" 沒有在影片中定義~ 

剛剛把影片調出來看, 這兩個Global變數 "search_city" & "search_area" 的確沒有在影片中出現!  這兩周剪接這影片時, 可能被我不小心剪掉了, 請各位朋友自行加入這兩個變數. 謝謝darbertchang 提醒~ 

加入的方法可以參考這裡的作法喔: https://youtu.be/cEN9jAxf2VU

~~~~

App Inventor #3

大家好, 歡迎收看本週的Weekend project with Stonez56. 

 本週我要進行教授的課程是 App Inventor 第3集。  “App Inventor #3  “自己來寫APP 快篩何處買?” (How to process .csv files in App Inventor 2)”.  這個影片適合初學者來觀看學習, 想拿來這個單元來學習 App Inventor 2 也是很 Ok 的! 

這幾天最熱門的話題就是快篩試劑何處買了! 到處都大排長龍, 有找了許多點也找不到~ 本集的 App Inventor #3 我們一起來做一隻 App 以縣市, 鄉鎮區來查詢什麼地方才能買到.

這支App 主要是處理 .CSV 檔案來呈現 App 的資訊. 而這個資料是由政府資料開放平臺的- 配合辦理發放公費COVID-19家用快篩試劑社區定點診所名單 頁面下載的資料而來, 資料更新時間為 2022-04-26 20:16 算是很即時的資料~~ 不準不能怪我 Stonez56~~ (2022-5-6 17:09 又釋出新版資料, 格式不變, 一樣能用, 同學們記得下載新版來用)

我採用下載 .CSV 檔案後, 上傳到 App 裡, 再處理 .CSV. 我們也可以直接即時下載 .CSV 來得最新即時的資料, 但是我觀察了幾天, 資料變化不快, 不需要每執行一次, 就去下載一次 .CSV 快浪費時間, 也浪費頻寬~ 

一起動手來寫 APP 吧~ 

如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可!

影片時間軸：

00:00:00 開始

00:00:28 了解這次開發程式的功能

00:01:38 從政府開放資料平台獲取資料（.CSV）

00:02:18 使用 OpenStreetMap, 拾棄 Google Map

00:02:51 了解 .CSV 文件格式

00:04:25 設計 App 使用者界面

00:16:59 準備開始來寫 APP

00:36:34 介紹AI2 Companion App

00:40:39 如何建立並使用副程式(Procedure)

00:56:42 使用 ListUtils 擴展刪除重複項

01:09:29 如何分割/結合字串

01:25:40 使用副程式(Procedure)的好處

01:27:11 加入 OpenStreetMap 地圖畫面

01:28:09 設計 MapScreen 使用者介面

01:33:59 開始設計 MapScreen 程式

01:47:06 秘密挑戰~~ ;)

圖檔下載:

rapid-test-icon.png

rapid-test-big.png

AI2 程式碼 (Screen 1):

變數定義 (Define Global variables)

產生查詢結果到 list view (Generate display item list)

Screen1 Initialize & process after read .csv file

After picking of city & area / populate specific city & unique area

AI2 程式碼 (MapScreen):

顯示結果在 OpenStreetMap 上 (Show results on the map)

參考資料:

* 如何處理 .csv: https://www.youtube.com/watch?v=K2n98PYO90M 

* TextUtils extension: https://community.appinventor.mit.edu/t/all-rescued-extensions-from-appybuilder-community/27169/47?page=3 

 * 配合辦理發放公費COVID-19家用快篩試劑社區定點診所名稱、地址、座標

https://data.gov.tw/dataset/150692 

* Google Map 取得 Marker 要輸入信用卡資料了: 

https://developers.google.com/maps/documentation/maps-static/overview 

* APP 地圖使用: App Inventor 內建之 OpenStreet Map: https://www.openstreetmap.org/#map=8/23.611/120.768  

Android #34 用一塊ESP32來學IOT - Android手機也能通 (Learn IOT with an ESP32 / remote control w/ MQTTDash)

Android #34 用一塊ESP32來學IOT - Android手機也能通
Learn IOT with an ESP32 / remote control w/ MQTTDash
** ENGLISH closed captions is now available for this video **
Learn IOT with an ESP32 / remote control w/ MQTTDash APP

Video timeline:
00:58 Download MQTTDash App
01:29 Create an MQTT connection
03:20 Dashboard Design
04:17 Review subscriptions on MQTTLens
05:15 Enter subscription topics in MQTTDash
10:30 See the Demo on the phone
12:39 Modify ESP32 code accordingly


大家好, 歡迎收看本週的Weekend project with Stonez56。 本周是Android 第34集，"用一塊ESP32來學IOT - Android 手機也能通”。這個是一個專門為初學者來開發的一個系列。 
 
繼上集之後, 有網友反應無法在手機上用 MQTTlens. 所以我們今天要把只能在電腦上使用的 MQTTLens 改成 MQTTDash 這支Android APP. 這樣子, 只要帶著手機, 我們就可真正隨時隨地的遠端控制IOT設備了~  那我們就一起來學習吧。  

Android MQTTDash App: https://play.google.com/store/apps/details?id=net.routix.mqttdash&hl=zh_TW&gl=US&showAllReviews=true 

尚未看過這系列的朋友, 建議從這裡依序學習:
* 用一塊ESP32來學 IOT  https://youtu.be/rjPU4YZG338 
* 溫溼度篇 https://youtu.be/x3k_dZXua1I  
* Android App 也能通 https://youtu.be/0IjQOxD2j48 

如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可! 
 
影片時間軸： 
00:00 開始 
00:58 下載 MQTTDash APP
01:29 建立 EMQX MQTT 連線
03:20 儀表板設計
04:17 查看之前 MQTTLens 上的訂閱/發佈主題
05:15 在 MQTTDash 中加入訂閱/發佈主題
10:30 看實際手機+ESP32 展示
12:39 如何修改 ESP32 相應代碼

Source code:

/*
 Basic ESP8266 MQTT example
 This sketch demonstrates the capabilities of the pubsub library in combination
 with the ESP8266 board/library.
 It connects to an MQTT server then:
  - publishes "hello world" to the topic "outTopic" every two seconds
  - subscribes to the topic "inTopic", printing out any messages
    it receives. NB - it assumes the received payloads are strings not binary
  - If the first character of the topic "inTopic" is an 1, switch ON the ESP Led,
    else switch it off
 It will reconnect to the server if the connection is lost using a blocking
 reconnect function. See the 'mqtt_reconnect_nonblocking' example for how to
 achieve the same result without blocking the main loop.
 To install the ESP8266 board, (using Arduino 1.6.4+):
  - Add the following 3rd party board manager under "File -> Preferences -> Additional Boards Manager URLs":
       http://arduino.esp8266.com/stable/package_esp8266com_index.json
  - Open the "Tools -> Board -> Board Manager" and click install for the ESP8266"
  - Select your ESP8266 in "Tools -> Board"
*/

#include <WiFi.h>
#include <PubSubClient.h>
#include <EasyButton.h>
#define LED 2        //built-in LED on ESP32

#include "DHT.h"
#define DHTPIN 23     // Digital pin connected to the DHT sensor
#define DHTTYPE DHT11 // DHT11
DHT dht(DHTPIN, DHTTYPE); // Initialize DHT sensor.
// Set up the temp / humidity update cycle
const int updateCycle = 5000;

// Update these with values suitable for your network.
const char *ssid = "Wi-Fi SSID";
const char *password = "Wi-Fi Password";

// Define your client ID on EMQX
String mqtt_ClientID = "stonez56_IOT_Station_";

// Define your topics to subscribe / publish
const char* sub_topic = "stonez56/esp32s";
const char* pub_led_topic = "stonez56/esp32s_led_state";
const char* pub_init_topic = "stonez56/esp32s_is_back";
const char* pub_temp_topic = "stonez56/esp32s_temp";
const char* pub_humd_topic = "stonez56/esp32s_humd";

// EMQX broker parameters
const char *mqtt_server = "broker.emqx.io";
const char *mqtt_userName = "emqx";
const char *mqtt_password = "public";

WiFiClient espClient;
PubSubClient client(espClient);
unsigned long lastMsg = 0;
#define MSG_BUFFER_SIZE (50)
char msg[MSG_BUFFER_SIZE];
char msg1[MSG_BUFFER_SIZE];

int value = 0;

void setup_wifi()
{
    delay(10);
    // We start by connecting to a WiFi network
    Serial.println();
    Serial.print("Connecting to ");
    Serial.println(ssid);

    WiFi.mode(WIFI_STA);
    WiFi.begin(ssid, password);

    while (WiFi.status() != WL_CONNECTED)
    {
        delay(500);
        Serial.print(".");
    }

    randomSeed(micros());

    Serial.println("");
    Serial.println("WiFi connected");
    Serial.println("IP address: ");
    Serial.println(WiFi.localIP());
}

void callback(char *topic, byte *payload, unsigned int length)
{
    Serial.print("Message arrived [");
    Serial.print(topic);
    Serial.print("] ");
    for (int i = 0; i < length; i++)
    {
        Serial.print((char)payload[i]);
    }
    Serial.println();

    payload[length] = '\0';
    String message = (char *)payload;
    
    if (strcmp(topic, sub_topic) == 0)
    {
        if (message == "off")
        {
            digitalWrite(LED, LOW); //Turn off
            client.publish(pub_led_topic, "off");
        }
        if (message == "on")
        {
            digitalWrite(LED, HIGH); //Turn on
            client.publish(pub_led_topic, "on");
        }
    }

    /* Switch on the LED if an 1 was received as first character
    // if ((char)payload[0] == '1')
    // {
    //     digitalWrite(BUILTIN_LED, LOW); // Turn the LED on (Note that LOW is the voltage level
    //                                     // but actually the LED is on; this is because
    //                                     // it is active low on the ESP-01)
    // }
    // else
    // {
    //     digitalWrite(BUILTIN_LED, HIGH); // Turn the LED off by making the voltage HIGH
    // } */
}

void reconnect()
{
    // Loop until we're reconnected
    while (!client.connected())
    {

        Serial.println("Attempting EMQX MQTT connection...");
        // Create a random client ID
        mqtt_ClientID += String(random(0xffff), HEX);
        // Attempt to connect
        if (client.connect((mqtt_ClientID, mqtt_userName, mqtt_password)))
        {
            Serial.print(" connected with Client ID: ");
            Serial.println(mqtt_ClientID);
            // Once connected, publish an announcement...
            client.publish(pub_init_topic, "Hi, I'm online!");
            // ... and resubscribe
            client.subscribe(sub_topic);
        }
        else
        {
            Serial.print("failed, rc=");
            Serial.print(client.state());
            Serial.println(" try again in 5 seconds");
            // Wait 5 seconds before retrying
            delay(5000);
        }
    }
}

void setup()
{
    //Start DH11 sensor
    dht.begin();

    pinMode(LED, OUTPUT); // Initialize the _LED pin as an output
    digitalWrite(LED, LOW); //default ESP32 LOW is turn off

    Serial.begin(115200);
    setup_wifi();
    client.setServer(mqtt_server, 1883);
    client.setCallback(callback);
}

void loop()
{

    if (!client.connected())
    {
        reconnect();
    }
    client.loop();

    unsigned long now = millis();
    if (now - lastMsg > updateCycle)
    {
        lastMsg = now;
        float humidity = dht.readHumidity();
        float temperature = dht.readTemperature();
        // Check if any reads failed and exit early (to try again).
        if (isnan(humidity) || isnan(temperature))
        {
            Serial.println(F("Failed to read from DHT sensor!"));
            return;
        }else{ // publish the message
            snprintf(msg, MSG_BUFFER_SIZE, "%.1lf°", temperature);
            snprintf(msg1, MSG_BUFFER_SIZE,"%.0lf%%", humidity);
            Serial.print("Publish message: ");
            Serial.println(msg);
            client.publish(pub_temp_topic, msg);
            client.publish(pub_humd_topic, msg1);
            
        }

    }
}

;

Arduino #32 用一塊ESP32來學 IOT (#32 Learn IOT with MQTT + ESP32)

大家好, 今天和大家來分享, Arduino #32 用一塊ESP32來學 IOT (#32 Learn IOT with MQTT + ESP32) 這個影片很適合初學者來觀看學習. 影片內容主要是告訴大家, 只要手上有一塊 ESP32 和一台電腦, 就可以開始學習 IOT 了 ! 看過這個影片, 你會了解學習 IOT 一點都不難喔! 甚至可以延伸作法, 利用 MQTTlens 透過雲端EMQX來做遠端的控制!

參考資料:

• Pubsubclient library: https://github.com/knolleary/pubsubclient/blob/master/examples/mqtt_esp8266/mqtt_esp8266.ino
• Additional Boards Manager URL: https://arduino.esp8266.com/stable/package_esp8266com_index.json,https://dl.espressif.com/dl/package_esp32_index.json
• Source code: https://stonez56.blogspot.com/

如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可!

影片時間軸：

• 00:00 開始
• 00:29 什麼是 MQTT，它是如何運作的？
• 01:30 你的第一個IOT專案！
• 01:55 MQTT 程式：pubsubclient
• 02:40 Arduino IDE: 設定Additional Boards Manager URL
• 03:09 Arduino IDE 其它設定
• 03:48 從我的部落格中下載程式並貼在Arduino IDE
• 04:07 在程式中更改必要的設定
• 07:27 從 Chrome 瀏覽器啟動 MQTTlens
• 07:54 在 MQTTlens 新增 EMQX 連線
• 09:54 訂閱 ESP32上的兩個主題
• 11:19 從 MQTTlens 發布主題
• 12:45 本次主要內容摘要

程式碼:

/*
 Basic ESP8266 MQTT example
 This sketch demonstrates the capabilities of the pubsub library in combination
 with the ESP8266 board/library.
 It connects to an MQTT server then:
  - publishes "hello world" to the topic "outTopic" every two seconds
  - subscribes to the topic "inTopic", printing out any messages
    it receives. NB - it assumes the received payloads are strings not binary
  - If the first character of the topic "inTopic" is an 1, switch ON the ESP Led,
    else switch it off
 It will reconnect to the server if the connection is lost using a blocking
 reconnect function. See the 'mqtt_reconnect_nonblocking' example for how to
 achieve the same result without blocking the main loop.
 To install the ESP8266 board, (using Arduino 1.6.4+):
  - Add the following 3rd party board manager under "File -> Preferences -> Additional Boards Manager URLs":
       http://arduino.esp8266.com/stable/package_esp8266com_index.json
  - Open the "Tools -> Board -> Board Manager" and click install for the ESP8266"
  - Select your ESP8266 in "Tools -> Board"
*/

#include <WiFi.h>
#include <PubSubClient.h>
#include <EasyButton.h>
#define LED 2        //built-in LED on ESP32

// Update these with values suitable for your network.
const char *ssid = "WiFi-SSID";
const char *password = "WiFi-PASSWORD";

// Define your client ID on EMQX
String mqtt_ClientID = "stonez56_IOT_Station_";

// Define your topics to subscribe / publish
const char* sub_topic = "stonez56/esp32s";
const char* pub_led_topic = "stonez56/esp32s_led_state";
const char* pub_init_topic = "stonez56/esp32s_is_back";

// EMQX broker parameters
const char *mqtt_server = "broker.emqx.io";
const char *mqtt_userName = "emqx";
const char *mqtt_password = "public";

WiFiClient espClient;
PubSubClient client(espClient);
unsigned long lastMsg = 0;
#define MSG_BUFFER_SIZE (50)
char msg[MSG_BUFFER_SIZE];
int value = 0;

void setup_wifi()
{
    delay(10);
    // We start by connecting to a WiFi network
    Serial.println();
    Serial.print("Connecting to ");
    Serial.println(ssid);

    WiFi.mode(WIFI_STA);
    WiFi.begin(ssid, password);

    while (WiFi.status() != WL_CONNECTED)
    {
        delay(500);
        Serial.print(".");
    }

    randomSeed(micros());

    Serial.println("");
    Serial.println("WiFi connected");
    Serial.println("IP address: ");
    Serial.println(WiFi.localIP());
}

void callback(char *topic, byte *payload, unsigned int length)
{
    Serial.print("Message arrived [");
    Serial.print(topic);
    Serial.print("] ");
    for (int i = 0; i < length; i++)
    {
        Serial.print((char)payload[i]);
    }
    Serial.println();

    payload[length] = '\0';
    String message = (char *)payload;
    
    if (strcmp(topic, sub_topic) == 0)
    {
        if (message == "off")
        {
            digitalWrite(LED, LOW); //Turn off
            client.publish(pub_led_topic, "off");
        }
        if (message == "on")
        {
            digitalWrite(LED, HIGH); //Turn on
            client.publish(pub_led_topic, "on");
        }
    }

    /* Switch on the LED if an 1 was received as first character
    // if ((char)payload[0] == '1')
    // {
    //     digitalWrite(BUILTIN_LED, LOW); // Turn the LED on (Note that LOW is the voltage level
    //                                     // but actually the LED is on; this is because
    //                                     // it is active low on the ESP-01)
    // }
    // else
    // {
    //     digitalWrite(BUILTIN_LED, HIGH); // Turn the LED off by making the voltage HIGH
    // } */
}

void reconnect()
{
    // Loop until we're reconnected
    while (!client.connected())
    {

        Serial.println("Attempting EMQX MQTT connection...");
        // Create a random client ID
        mqtt_ClientID += String(random(0xffff), HEX);
        // Attempt to connect
        if (client.connect((mqtt_ClientID, mqtt_userName, mqtt_password)))
        {
            Serial.print(" connected with Client ID: ");
            Serial.println(mqtt_ClientID);
            // Once connected, publish an announcement...
            client.publish(pub_init_topic, "Hi, I'm online!");
            // ... and resubscribe
            client.subscribe(sub_topic);
        }
        else
        {
            Serial.print("failed, rc=");
            Serial.print(client.state());
            Serial.println(" try again in 5 seconds");
            // Wait 5 seconds before retrying
            delay(5000);
        }
    }
}

void setup()
{
    pinMode(LED, OUTPUT); // Initialize the _LED pin as an output
    digitalWrite(LED, LOW); //default ESP32 LOW is turn off

    Serial.begin(115200);
    setup_wifi();
    client.setServer(mqtt_server, 1883);
    client.setCallback(callback);
}

void loop()
{

    if (!client.connected())
    {
        reconnect();
    }
    client.loop();

    /* unsigned long now = millis();
    // if (now - lastMsg > 2000)
    // {
    //     lastMsg = now;
    //     ++value;
    //     snprintf(msg, MSG_BUFFER_SIZE, "hello world #%ld", value);
    //     Serial.print("Publish message: ");
    //     Serial.println(msg);
    //     client.publish("stonez56/esp32s_button_pushed", msg);
    // } */
}

 

如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可!

#10.1 如何開機自動連接Google 雲端硬碟/ How to auto-mount Google Drive on boot

大家好, 今天是2021的最後一天, 祝大家新年快樂!!

在今年的最後天天, 來和大家分享 “一起來學樹莓派” 系列之 “如何開機自動連接Google 雲端硬碟/ How to auto-mount Google Drive on boot”

之前的影片做了一片”#10 重要資料, 直接備份到Google雲端硬碟的影片https://youtu.be/GmyiPJ1cxvk , 卻忘了一個重要的程序, 就是如何開機後, 如何自動連接Google 雲端硬碟, 少了這個重要的步驟, 那每次重新開機都要下 rclone 指令來掛載Google drive 也是很麻煩的!  請務必先看之前的影片 #10, 不然會不容易了解此片的內容!

 參考資料: 

• * Raspberry pi Mount error: setting does not match unit name
• https://unix.stackexchange.com/questions/283442/systemd-mount-fails-where-setting-doesnt-match-unit-name
• * Rclone + Raspberry pi parameters
• https://forum.rclone.org/t/raspberry-pi4b-4gb-config-help/11361/2
• * Rclone mount:
• https://rclone.org/commands/rclone_mount/

home-pi-mnt-gdrive.mount (千萬別照抄...要依你 Rclone 設定來修改)

[Unit]

After=network.target

[Mount]

Type=rclone

What=gdrive:rpi4

Where=/home/pi/mnt/gdrive

Options=rw,allow_other,args2env,vfs-cache-mode=writes,config=/home/pi/.config/rclone/rclone.conf,cache-dir=/tmp

[Install]

WantedBy = multi-user.target

•  gdrive:rpi4: Google drive 上要掛載的資料夾
• /home/pi/mnt/gdrive: 對應的樹莓派上的資料夾
• /home/pi/.config/rclone/rclone.conf: rclone 設定檔的位置!

如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可!

影片時間軸： 00:00 開始 00:52 先閱讀 Rclone 文件 01:30 使用 ssh 程式來下達命令 02:18 遵守嚴格的磁碟掛載命名規則 03:17 解釋掛載檔案內容 07:36 不要忘記掛載文件要以 .mount 結尾 09:17 重新啟動以驗證自動掛載是否成功 10:19 沒有自動掛載的原因 12:19 重新啟動以再次檢查自動掛載 13:12 教學內容總結

#7 動態查詢InfluxDB 資料庫 以及 用MQTT來遠端開/關繼電器

 大家好, 今天來和大家分享 “一起來學樹莓派”, 

本週的主題為: “#7 動態查詢InfluxDB 資料庫 以及 用MQTT來遠端開/關繼電器”.

上期的資料庫查詢的方式是固定時間軸的, 每次要查詢資料時, 可能都要重新下 SQL 指令.

本期要介紹的是使用 Slider 滑桿的方式來調整時間的區間, 並用 Interval 滑桿來調整時間的平均值, 使查詢資料的方法更便利. 

另外, 本次也介紹如何使用 MQTT out 來從遠端來開/關繼電器. 

(如果你要接上 110V or 220V 電器, 請自行小心高壓電的危險! 本人無法負責您的風險)

一起來學樹莓派吧!

這影片主要內容有:

• 如何用 Slider 建立動態查詢 InfluxDB 的界面

• 用 MQTT out 來遠端開/關繼電器 (可以接上電器開/關)

• 如何把 SQL 讀出的資料解析送到 Dashboard Chart

• 使用 Layout 工具來佈置 dashboard 排列位置

• 使用 CSS template 來改變 dashboard 外觀

如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可!

影片時間軸：

00:00 開始 00:34 課程開始 02:29 在 NodeRED 中開始寫程式 03:48 建立Dashboard (後來不小心被我刪除.. :( 05:27 將時間/平均時間 Sliders 加到立Dashboard 08:20 將變數儲存到 context.flow 10:45 解釋 SQL 命令的內容 11:50 從Flow 變數中取得 hours 變數內容 12:58 點擊Deploy儲存Nodes 14:29 使用 Influx client 在 terminal 上查看SQL的正確性 15:20 使用 Link in / Link out nodes 讓畫面更清楚 18:25 查看資料庫讀取後資料結構 20:33 使用 .toFixed(2) 設定小數點位數為 2 位 25:45 使用 Layout 工具來佈置 dashboard 排列位置 28:05 複製自其他 flow 的 Nodes 31:39 加入 Switch / MQTT out Nodes 34:39 解釋 ESP8266 + 繼電器程式碼 37:25 根據收到的 Message 來打開/關閉 relay 38:21 實機展示：用 MQTT來遠端打開/關閉繼電器 38:53 危險！ 注意高壓電可能會致命或使人受到傷害！！ 39:14 使用 Template (CSS) 來自行定義Dashboard 的外觀

Create query 程式碼(A):

msg.query = "select mean(temp) as temp, mean(humd) as humd from studioTemp where location='studio' and time > now() - ";
msg.query += flow.get("hours") + "h GROUP by time(" + flow.get("interval") + "m) FILL(0)";

return msg;

Format DB data 程式碼(B):

var My_series = ["Temperature °C", "Humidity %"];
var My_labels = ["temp", "Humd"];
var data = msg.payload;

// var data = msg.payload;
// Not sure the same query produced differnet results...?

temp = data.map(point=> {return {x:point.time , y:point.temp.toFixed(2)}});
humd = data.map(point=> {return {x:point.time , y:point.humd.toFixed(2)}});
var My_data = [temp, humd];

msg.payload = [{ series: My_series, data: My_data, labels: My_labels}]

return msg;

NodeRED source code is available here: Download it here.

Stonez56 一起來學樹莓派系列影片: 

• #1 無鍵盤 無滑鼠 快速上手
  https://youtu.be/BCkdSQNtVB0 
• #2 改用 USB3/SSD 來開機, 畫面好流暢!
  https://youtu.be/uI01GFmfH1k
• #3 一行指令安裝 Node RED & 外加密碼保護       
  https://youtu.be/Mk8-xn_fHOA 
• #4 安裝 Mosquitto broker (MQTT) & 並在Node-RED & Google MQTTLens 進行測試 https://youtu.be/cK_LJPgR4mQ
• #5 溫溼度感測器DHT11由 ESP32 經 MQTT 送到 Node-RED
  https://youtu.be/Vv1JRNYIZKc 
• #6 監測資料寫入InfluxDB資料庫, 長期保存並在NodeRED資訊看板裡顯示 https://youtu.be/UOoDolPVge0 
• #7 2021 一起來學樹莓派 - 動態查詢InfluxDB 資料庫 & MQTT來遠端開/關繼電器  https://youtu.be/EpY_jl4wXtM

參考資料:

• Function notd to format the data for “Influxdb Out” node URL: https://github.com/node-red/node-red-dashboard/blob/master/Charts.md 
• ESPMQTTClient source code https://github.com/plapointe6/EspMQTTClient/blob/master/examples/SimpleMQTTClient/SimpleMQTTClient.ino 
• Customize dashboard CSS https://discourse.nodered.org/t/customize-dashboard-css/23139

#6 監測資料寫入InfluxDB資料庫, 長期保存並在NodeRED資訊看板裡顯示

大家好, 今天來和大家分享 “一起來學樹莓派, 寫入InfluxDB 資料庫”. 上集分享的是資料送到 MQTT 顯示到資訊看板後, 時間後後資料就消失了. 本集是把所有的資料收集起來之後, 可以用來以後做為資料分析的依據. 以前你可能要把資料寫到 Thinkspeak server上, 現在自己家裡的樹莓派上就可以做到了! 一起來學樹莓派吧!

這影片內容有:

• 安裝 InfluxDB 資料庫
• 比較 InfluxDB 與關聯式資庫的不同
• 如何把ESP32送到 MQTT 資料寫入InfluxDB 資料庫
• 讀取InfluxDB 資料庫, 再把這些資料在資訊看板上顯示出來

Format code for writing InfluxDB

// Create the object key to match DB filed name from topic name(studio/temp1)
msg.payload["temp"] = msg.payload["studio/temp1"];
// Delete the unwanted studio/temp1
delete msg.payload["studio/temp1"];
// Update temp from string to float
msg.payload["temp"] = parseFloat(msg.payload["temp"]);

msg.payload["humd"] = msg.payload["studio/humd1"];
delete msg.payload["studio/humd1"];
msg.payload["humd"] = parseFloat(msg.payload["humd"]);

//remove unused topic
delete msg.topic;

//Create a temporary array
var data = [];
data.push(msg.payload);


//Add object to DB Tag(index field)
data.push({"location":"studio"});
//msg.payload.push({"location":"studio"});

//Copy temporary array to msg.payload
msg.payload = data;
return msg;

Format code for reading historical InfluxDB data

//Source from: https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665
var series = ["Temperature °C", "Humdity %"];
var labels = ["temp", "Humdity"];

var data0 = "[";
var thetime0;

for (var i=0; i < msg.payload.results[0].series[0].values.length; i++) {
thetime0 = (msg.payload.results[0].series[0].values[i][0]); // Some manipulation of the time may be required
thetime0 = Date.parse(thetime0);
data0 += '{ "x":' + thetime0 + ', "y":' + (msg.payload.results[0].series[0].values[i][1]) + '}';
if (i < (msg.payload.results[0].series[0].values.length - 1)) {
data0 += ","
} else {
data0 += "]"
}
}



var data1 = "[";
var thetime1;

for (var j=0; j < msg.payload.results[0].series[0].values.length; j++) {
thetime1 = (msg.payload.results[0].series[0].values[j][0]); // Some manipulation of the time may be required
thetime1 = Date.parse(thetime1);
data1 += '{ "x":' + thetime1 + ', "y":' + (msg.payload.results[0].series[0].values[j][2]) + '}';
if (j < (msg.payload.results[0].series[0].values.length - 1)) {
data1 += ","
} else {
data1 += "]"
}
}
var data = [data0, data1];
var jsondata = [JSON.parse(data0), JSON.parse(data1)];
msg.payload = [{"series": series, "data": jsondata, "labels": labels}];
msg.dataPoints = data;


return msg;

請在此下載本篇教學的 NodeRead 程式： #6 監測資料寫入InfluxDB資料庫, 長期保存並在NodeRED資訊看板裡顯示

更新：

影片內容不小心略過這個 InfluxDB Out Node (Write to studioTempDB)的內容：

InfluxDB out node 內容有兩項資料要輸入：

(1) 定義 InfluxDB Server, 按下鉛筆符號(見下圖）

(2) 寫入的 Measurement 資料表名稱，我們的範例裡，Measurement = studioTemp

定義 InfluxDB Server：

(1) InfluxDB - 在 Flow 裡看到的名稱

(2) InfluxDB 版本 - 教學裡案裝的是 v1.64

(3) Host: InfluxDB 的主機： 由於我們裝在本機，所以用 : raspberrypi.local, Port: 8086

(4) 資料庫名稱： 範例裡使用 studioTemp

(5) 資料庫帳號及密碼： 如果未設定可略過

Stonez56 一起來學樹莓派系列影片: 

• #1 無鍵盤 無滑鼠 快速上手
  https://youtu.be/BCkdSQNtVB0 
• #2 改用 USB3/SSD 來開機, 畫面好流暢!
  https://youtu.be/uI01GFmfH1k
• #3 一行指令安裝 Node RED & 外加密碼保護       
  https://youtu.be/Mk8-xn_fHOA 
• #4 安裝 Mosquitto broker (MQTT) & 並在Node-RED & Google MQTTLens 進行測試 https://youtu.be/cK_LJPgR4mQ
• #5 溫溼度感測器DHT11由 ESP32 經 MQTT 送到 Node-RED
  https://youtu.be/Vv1JRNYIZKc 
• #6 監測資料寫入InfluxDB資料庫, 長期保存並在NodeRED資訊看板裡顯示 https://youtu.be/UOoDolPVge0 
• #7 2021 一起來學樹莓派 - 動態查詢InfluxDB 資料庫 & MQTT來遠端開/關繼電器  https://youtu.be/EpY_jl4wXtM

#5 2021 一起學 Raspberry Pi 4 - 溫溼度感測器DHT11由 ESP32 經 MQTT 送到 Node-RED

大家好, 今天來和大家分享如何把 DHT11溫溼度感測器上的資料由ESP32 讀取, 並用MQTT Client 發佈到 Mosquitto Server(MQTT), 再送到 Node-RED上, 再把這些資料在資訊看板上顯示出來. 

本篇使用到的程式碼:

//---- Setup update frequency -----//
const long TEMP_updateInterval = 10000; // How long to change temp and update, 10000 = 10 sec
unsigned long TEMP_currentMillis = 0;
unsigned long TEMP_previousMillis = 0; // store last time temp update

#include "DHT.h"
#define DHTPIN 23  //ESP32 pin 23
#define DHTTYPE DHT11
DHT dht(DHTPIN, DHTTYPE); // Initialize DHT sensor

/*
  SimpleMQTTClient.ino
  The purpose of this exemple is to illustrate a simple handling of MQTT and Wifi connection.
  Once it connects successfully to a Wifi network and a MQTT broker, it subscribe to a topic and send a message to it.
  It will also send a message delayed 5 seconds later.
*/

#include "EspMQTTClient.h"

EspMQTTClient client(
    "Your_WiFi_AP",     // Wi-Fi AP name
    "Wi-FI_Password", // Wi-Fi Password
    "192.168.0.119", // MQTT Broker server ip
    "",                  // Broker user name; Can be omitted if not needed
    "",                  // Broker password; Can be omitted if not needed
    "ESP32_Client",      // Client name that uniquely identify your device
    1883                 // The MQTT port, default to 1883. this line can be omitted
);

void setup()
{
    Serial.begin(115200);
    //Start DH11 sensor
    dht.begin();
    
    // Optionnal functionnalities of EspMQTTClient :
    client.enableDebuggingMessages(); // Enable debugging messages sent to serial output
    //client.enableHTTPWebUpdater();  // Enable the web updater. User and password default to values of MQTTUsername and MQTTPassword. These can be overrited with enableHTTPWebUpdater("user", "password").
    //client.enableLastWillMessage("TestClient/lastwill", "I am going offline"); // You can activate the retain flag by setting the third parameter to true
}

// This function is called once everything is connected (Wifi and MQTT)
// WARNING : YOU MUST IMPLEMENT IT IF YOU USE EspMQTTClient
void onConnectionEstablished()
{
    // Subscribe to "mytopic/test" and display received message to Serial
    client.subscribe("message/hello", [](const String &payload)
                     { Serial.println(payload); });

    // Subscribe to "mytopic/wildcardtest/#" and display received message to Serial
    //client.subscribe("mytopic/wildcardtest/#", [](const String &topic, const String &payload)
    //                 { Serial.println("(From wildcard) topic: " + topic + ", payload: " + payload); });

    // Publish a message to "mytopic/test"
    client.publish("studio/temp1", "Let's go!"); // You can activate the retain flag by setting the third parameter to true

    // Execute delayed instructions
    //client.executeDelayed(5 * 1000, []()
    //                      { client.publish("mytopic/wildcardtest/test123", "This is a message sent 5 seconds later"); });
}



void loop()
{

    TEMP_currentMillis = millis();
    if(TEMP_currentMillis - TEMP_previousMillis >= TEMP_updateInterval){
        TEMP_previousMillis = TEMP_currentMillis;

        // Reading temperature or humidity takes about 250 milliseconds!
        // Sensor readings may also be up to 2 seconds 'old' (its a very slow sensor)
        float humidity = dht.readHumidity();
        float temperature = dht.readTemperature();
        // Read temperature as Fahrenheit (isFahrenheit = true)
        //float f = dht.readTemperature(true);

        // Check if any reads failed and exit early (to try again).
        if (isnan(humidity) || isnan(temperature))
        {
            Serial.println(F("Failed to read from DHT sensor!"));
            return;
        }

        String tmp = "Current Temperature: ";
        String hud = "Current Humidity: ";
        Serial.print(tmp);
        Serial.println(String(temperature));
        Serial.print(hud);
        Serial.println(String(humidity));
        //Publish
        client.publish("studio/temp1", String(temperature));
        client.publish("studio/humd1", String(humidity));

        // Here is how to subscribe
        //client.subscribe("message/hello", [](const String &payload) { Serial.println(payload); });
    }

    client.loop();
}

*** 影片中忘了說明, 在 VS Code 裡, 按下 F1 到 Arduino Library Manager 裡, 搜尋 EspMQTTClient V.11.1版本, 並按下安裝 , 程式才能正確執行喔~~ ***

如果時間不夠的朋友, 也可以從以下的時間軸挑選想看的部份即可!

影片時間軸:

00:29 課程開始 00:52 如何將 DHT11 連接到 ESP32 (接線圖) 00:59 在 ESP32 利用 EspMQTTClient 來寫程式 01:22 安裝 DHT11 程式庫 02:16 設定 EspMQTTClient 02:45 由 Raspberry Pi Terminal 找出 Mosquitto(MQTT) IP 地址 04:51 不要用 delay() 在程式 Loop 裡！ 06:50 上傳並檢查發佈/訂閱的 messages 07:54 安裝 Node-RED 資訊看板（在 Node-RED 中） 08:57 按主題來訂閱消息 09:58 設置 MQTT 服務器的位置 11:34 建立資訊看板選項 Tabs Links & Groups 17:00 建立歷史資料資訊看板 20:32 查看資訊看板 20:50 資訊看板中的選項 Tabs Links & Groups在哪裡

下一集將介紹如何把歷史資料存儲到樹莓派的資料庫(influxDBvor MySQL)中, 以取得長時間的記錄資料. 

Stonez56 一起來學樹莓派系列影片: 

• #1 無鍵盤 無滑鼠 快速上手
  https://youtu.be/BCkdSQNtVB0 
• #2 改用 USB3/SSD 來開機, 畫面好流暢!
  https://youtu.be/uI01GFmfH1k
• #3 一行指令安裝 Node RED & 外加密碼保護       
  https://youtu.be/Mk8-xn_fHOA 
• #4 安裝 Mosquitto broker (MQTT) & 並在Node-RED & Google MQTTLens 進行測試 https://youtu.be/cK_LJPgR4mQ
• #5 溫溼度感測器DHT11由 ESP32 經 MQTT 送到 Node-RED
  https://youtu.be/Vv1JRNYIZKc 
• #6 監測資料寫入InfluxDB資料庫, 長期保存並在NodeRED資訊看板裡顯示 https://youtu.be/UOoDolPVge0 
• #7 2021 一起來學樹莓派 - 動態查詢InfluxDB 資料庫 & MQTT來遠端開/關繼電器  https://youtu.be/EpY_jl4wXtM

參考資料:

EspMQTTClient: https://github.com/plapointe6/EspMQTTClient