M5 Stick C+を使用して可視化してみた 〜データ蓄積から可視化まで〜

# はじめに

[前回](https://qiita.com/f-mio/items/2e90ef7761fb77dff2b0)の続きの記事です。照度センサーを使用して、光が当たっている時間を測定するIoT機器を作成しようと思います。用途としては、部屋の電気がどのくらい使われているのかを実測したり、製造設備のシグナルタワー(積層信号灯)の点灯状態を取得したりする等を想定しております。

## 本記事の概要
[前回](https://qiita.com/f-mio/items/2e90ef7761fb77dff2b0)にインフラを構築したので、次はデータを貯めて、可視化してみましょう。
データの貯め方は下記の流れです。
1. センサーからデータをMQTT Brokerへ送信する。 (Publish)
1. MQTT Brokerからメッセージを読み取って(Subscribe)、DBへ書き込み

この後に、Power BIを使って可視化してみます。

## 想定読者
- IoTをやってみたいと考えている方

## システム構成
![照度測定システム.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/3ff56368-1f97-16a9-992a-eed964bfbd9f.png)


## 検証した環境
### データ取得まで
- Macbook Pro (Apple Silicon)
- Homebrew
- PostgreSQL 14
- Mosquitto
### 可視化
- Windows10 Professional
- Power BI Desktop バージョン: 2.119.986.0 64-bit (2023年7月)

## IoTの使用機器
- [M5 Stick C Plus](https://shop.m5stack.com/products/m5stickc-plus-esp32-pico-mini-iot-development-kit)
- [M5 Stack用Light Sensor](https://shop.m5stack.com/products/light-sensor-unit)

https://shop.m5stack.com/products/m5stickc-plus-esp32-pico-mini-iot-development-kit

https://shop.m5stack.com/products/light-sensor-unit

・・・書いている途中に気付いたのですが、[M5 Scick C Plus 2](https://shop.m5stack.com/products/m5stickc-plus2-esp32-mini-iot-development-kit)というものが12/13に出ていたみたいです。

## 前回記事について
前回の記事で2点修正が必要な箇所があります。
PostgreSQLデータベース設定で2箇所抜けている箇所があるのと、本記事でテーブル名をsensor_light_tableに変えている点の修正。これらは2023/12/24までに行おうと思います。


# データ取得からDB登録
センサーの値をデータベースに蓄積します。
1. センサーから値を取得してメッセージとして送信(MQTT)し、
1. メッセージを取得し、データベースに書き込む

という流れを行います。


## Arduino IDEの設定
Arduino IDEというエディタを使用します。インストールの概要は記述しますが、詳細は[公式ページ情報](https://docs.m5stack.com/ja/quick_start/m5stickc_plus/arduino)を確認してください。

<details>
<summary>インストールの概要</summary>
<h4>i. 追加設定用ファイルを設定</h4>

Macの画面の右上で、Arduino IDE > Preferenceを選択し、Additional Boards Manager URLsに下記のjsonのURLを入力し、OKを押して設定画面を閉じる。
![追加設定用ファイルを設定.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/8cb289d3-c4b3-2002-b764-f950fe8f1b53.png)
```
https://m5stack.oss-cn-shenzhen.aliyuncs.com/resource/arduino/package_m5stack_index.json
```
※.2023.12.16現在で公式ページに記載されていた情報です。今後変更される可能性があります。


<h4>ii. M5Stackのボードマネージャインストール</h4>

左端の上から二番目のアイコンをクリックし、【Boards Manager】を開く。入力フォームに【m5】と入力し、【M5Stack】をインストールする。 ![M5Stackのボードマネージャインストール.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/bf5f9048-5e78-c01d-f71f-17509d3930cc.png)


<h4>iii. M5StickCplusのライブラリインストール</h4>
左端の上から三番目のアイコンをクリックし、【library Manager】を開く。入力フォームに【m5stick】と入力し、【M5StickCPlus】をインストールする。

![M5Stickのライブラリインストール.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/06ff01b9-e1ce-91d6-64a9-466b59889164.png)
</details>


## センサーデータの送信
まずはM5 Stick C+と照度センサーを接続します。
次にType CのケーブルでPCとM5をつなぎ、PC上で[Arduino IDE](https://www.arduino.cc/en/Guide)を立ち上げます。

![M5stick_and_light_sensor.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/f3be149a-5403-ec28-9cbc-81e14831aae9.png)

エディターArduino IDE内でファイルを作成して、下記の内容を記載します。
なお、送信するjsonデータは下記のものです。

```python
{
    "status": "(ステータスメッセージ)",
    "payload": {
        "timestamp": "(日時の文字列)",
        "lightVal": (アナログの値)
    }
}
```


<details>
<summary>センサー値のMQTT送信プログラム</summary>


```c++
/*
*******************************************************************************
* Copyright (c) 2021 by M5Stack
*                  Equipped with M5StickC-Plus sample source code
*                          配套  M5StickC-Plus 示例源代码
* Visit for more information: https://docs.m5stack.com/en/core/m5stickc_plus
* 获取更多资料请访问: https://docs.m5stack.com/zh_CN/core/m5stickc_plus
*
* Describe: MQTT.
* Date: 2021/11/5
*******************************************************************************
*/
#include <M5StickCPlus.h>
#include <WiFi.h>
#include <PubSubClient.h>
#include <time.h>

WiFiClient espClient;
PubSubClient client(espClient);

// [Secret]
// WiFi config information.
const char* ssid        = "your ssid";
const char* password    = "your password";
const char* mqtt_server = "server host name or ip";

// For fetch date time from ntp server.
const char* ntpServer = "ntp.nict.jp";
const long gmtOffset_sec     = 9 * 3600;
const int daylightOffset_sec = 0;

// For mqtt publisher.
unsigned long lastMsg = 0;
#define MSG_BUFFER_SIZE (200)
char msg[MSG_BUFFER_SIZE];
int value = 0;


void setupWifi();
void callback(char* topic, byte* payload, unsigned int length);
void reConnect();


void setup() {
  M5.begin();
  M5.Lcd.setRotation(3);
  M5.Lcd.setTextColor(BLACK);
  M5.Lcd.setTextSize(2);
  M5.Lcd.setCursor(10, 2);
  M5.Lcd.printf("UNIT LIGHT \n");
  pinMode(32, INPUT);
  pinMode(36, INPUT_PULLUP);
  setupWifi();
  client.setServer(mqtt_server,
                    1883);  // Sets the server details.
  client.setCallback(
      callback);  // Sets the message callback function.

  // For fetch current time.
  configTime(gmtOffset_sec, daylightOffset_sec, ntpServer);

}

uint16_t analogRead_value = 0;
uint16_t digitalRead_value = 0;

void loop() {
  if (!client.connected()) {
    reConnect();
  }
  client.loop();  // This function is called periodically to allow clients to
                    // process incoming messages and maintain connections to the
                    // server.

  unsigned long now = millis();  // Obtain the host startup duration.
  // unsigned long now =
  //   millis();  // Obtain the host startup duration.
  if (now - lastMsg > 60000) {
    analogRead_value = analogRead(33);
    digitalRead_value = digitalRead(32);
    lastMsg = now;
    ++value;
    String json = createJson();
    char jsonStr[200];
    json.toCharArray(jsonStr,200);
    snprintf(msg, MSG_BUFFER_SIZE, jsonStr);  // Format to the specified string and store it in MSG.

    if (digitalRead_value == 0) {
      M5.Lcd.fillScreen(BLUE);
    }
    else {
      M5.Lcd.fillScreen(YELLOW);
    }
    M5.Lcd.setTextSize(2);
    M5.Lcd.setCursor(10, 2);
    M5.Lcd.printf("UNIT LIGHT \n");
    M5.Lcd.setCursor(10, 30);
    M5.Lcd.print("status: ");
    M5.Lcd.setTextColor(BLACK);
    M5.Lcd.setCursor(105, 30);
    M5.Lcd.printf("%d (%d)", digitalRead_value, analogRead_value);
    M5.Lcd.setCursor(10, 50);
    M5.Lcd.setTextSize(1);
    M5.Lcd.println(msg);
    client.publish("home", msg);  // Publishes a message to the specified
                                        // topic.
  }
}

void setupWifi() {
    delay(10);
    M5.Lcd.printf("Connecting to %s", ssid);
    WiFi.mode(
        WIFI_STA);  // Set the mode to WiFi station mode.
    WiFi.begin(ssid, password);  // Start Wifi connection.

    while (WiFi.status() != WL_CONNECTED) {
        delay(500);
        M5.Lcd.print(".");
    }
    M5.Lcd.printf("\nSuccess\n");
}

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

void reConnect() {
    while (!client.connected()) {
        M5.Lcd.print("Attempting MQTT connection...");
        // Create a random client ID.
        String clientId = "M5Stack-";
        clientId += String(random(0xffff), HEX);
        // Attempt to connect.
        if (client.connect(clientId.c_str())) {
            M5.Lcd.printf("\nSuccess\n");
            // Once connected, publish an announcement to the topic.
            client.publish("M5Stack", "hello world");
            // ... and resubscribe.
            client.subscribe("M5Stack");
        } else {
            M5.Lcd.print("failed, rc=");
            M5.Lcd.print(client.state());
            M5.Lcd.println("try again in 5 seconds");
            delay(5000);
        }
    }
}

String createJson() {
  String timestampStr = fetchTimestamp();
  String json = "{";
    json += "\"status\": \"success\",";
    json += "\"payload\": {";
    json +=   "\"timestamp\": \"";
    json +=   timestampStr;
    json +=   "\", ";
    json +=   "\"lightVal\": ";
    json +=   analogRead_value;
    json +=   "}";
    json += "}";
  return json;
}

String fetchTimestamp() {

  struct tm timeInfo;
  getLocalTime(&timeInfo);

  char timestamp[20];
  sprintf(timestamp, "%04d/%02d/%02d %02d:%02d:%02d",
    timeInfo.tm_year + 1900,
    timeInfo.tm_mon + 1,
    timeInfo.tm_mday,
    timeInfo.tm_hour,
    timeInfo.tm_min,
    timeInfo.tm_sec);

  return timestamp;
}
```

:::note info
WiFiの接続情報は各自の環境に合わせて下記の部分を編集してください。
```
const char* ssid        = "your_wifi_ssid <-- only 2.4GHz";
const char* password    = "your_wifi_password";
const char* mqtt_server = "server host name or ip";
```
:::
</details>


<details>
<summary>【雑談】M5プログラミングについてのおまけ</summary>

M5で使用されるブログラムは[Arduino言語](https://www.arduino.cc/reference/en/)というもので記述されています。Arduino言語はC++の派生した言語のようです。電気系,電子系の方以外は馴染みがないものと思います。
M5の便利なところはプログラムのテンプレートがエディター内で準備されているところです。ですので初心者が0からすべてをコーディングする必要がないです。また、このフォーマットを組み合わせることで知識がない方でもある程度自分がやりたいことを実現できます。(実は私も大学の講義で少しC++を触っただけだったので0から作れません...)
ちなみに[GitHub](https://github.com/m5stack/M5StickC-Plus/tree/master/examples)にもありますので、興味がある方はよろしければ見てください。
</details>


プログラムの記入が終わったら、M5Stickにデータを送信してください。こちらを行うことで、メッセージを受け取る入れ物(MQTT Broker)にメッセージを送信できるようになります。


## メッセージの受信とDB書き込み
メッセージを送信できるようになりましたので、メッセージを受信してデータベースに書き込むプログラムを作成しようと思います。

### データ受信＆DB書込プログラム
データの受信とデータベースへのデータ書き込みプログラムを作成します。基本的にどのプログラミング言語でも大丈夫なのですが、私は慣れているPythonという言語で記述します。


<details>
<summary>MQTTサブスクライブ＆DB書き込みプログラム</summary>

```python
import os
import json
import paho.mqtt.subscribe as subscribe
import psycopg2

# MQTT connection info
topics = ['M5Stack', 'home']
mqtt_host = "your_mqtt_host_name"
mqtt_port = "your_mqtt_host_port"

# PostgreSQL connection object
connection = psycopg2.connect(
    host = "your host name or ip address",
    dbname = "iot_db",
    port = "5432",
    user = "your db role",
    password = "your db password"
)


# mqtt subscribe program
while 1:
    message = subscribe.simple(topics, hostname=mqtt_host, port=mqtt_port, retained=False, msg_count=1)
    try:
        res = json.loads(message.payload)
        query = f"""
            INSERT INTO sensor_light_table
            (
                location,
                sensor_value,
                create_timestamp
            )
            VALUES
            (
                '{message.topic}',
                '{res['payload']['lightVal']}',
                '{res['payload']['timestamp']}'
            );
        """
        with connection.cursor() as cur:
            cur.execute(query)
            connection.commit()
        print(res)

    # ←　ほんとならここに、接続失敗のハンドラ入れて再接続メソッド入れた方がよい...(今回はスルー)
    except Exception as e:
        print(e)
```

:::note warn
私も間違えたのですが、mqtt_portは整数しか入れられないみたいです。
多くの方は1883ポートを使用していると思いますので下記のように記述して下さい。

```python
mqtt_port = 1883
```
:::


</details>


## データの確認
SQL開発ツールというデータベースの中身を確認できるツールがあります。
PostgreSQL用のpgAdmin4というソフトを使って溜めたデータを可視化してみました。結果は下記のものです。

![データベースへのセンサーデータ取り込み.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/61cfb7f3-4891-306e-f75d-fa31f52c4e37.png)

問題なくデータは入っていることが確認できました。
また、pgAdminの機能の一つにグラフ化があります。(ついさっき気付きました)
早速今回のデータでグラフを作ってみましょう。

![照度センサーデータ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/c3e05d06-487a-9b11-40c7-1d393ed4dac2.png)

グラフを見ると下記の内容がわかりました。
 - 照明がついている場合は、2500以下の数値
 - 照明が消えている場合は、4095 (12bit？)
この結果から、2500未満の場合に照明が点いていると考えてよいでしょう。


# 可視化 〜Power BI〜
可視化はPowerBI使います。

## なぜPowerBI？
ちょっと思想的な部分が入ります。
まずは、なぜWindowsのプログラムのPowerBIを使うかなのですが、一言でいうと<b>自分がメインで関わらないから</b>です。
IoTやSFA(Sales Force Automation)みたいなツールは可視化がキーになると考えています。グラフについては、はじめこそシステム開発者が組み立てることが多いですが、<b>基本は部署のノウハウを活かして、アジャイル的に部署内で合った形に作り変えていくことがあるべき姿</b>です。
そして、PowerBIを使った理由は、IoT関係でお話しする方にWindows使っている人が多く、無償で使えるので、いくつかの案件ではひとまずこれで進めることになりそうだからです。(このためだけに親にWindows PC借りました。苦笑)
たしかにシステム開発者からすれば、たとえばChart.jsを使ってシステムは作れますが、「簡単にデータを分析したい」、「過去使っていた指標を使わなくなったので新しい指標が見たい」みたいなニーズがあってもすぐに対応できないケースが多いです。なので<b>BIツールはユーザが自由に作り直せるものを準備したおいた方がいい</b>というのが私の考え。


## PowerBI設定
では最後に可視化を行っていこうと思います。この部分、わからないことが多いので良い使い方を教えていただけるとありがたいです。(もしくは、PowerBI以外の使い勝手がいいBIツール紹介して欲しい)

### PowerBIの立ち上げ
まずはPowerBIを立ち上げます。
初期画面のサイドバー中央に【データ取得】があるのでそれを選択します。
![PowerBI初期画面.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/c3230dcd-9e67-34ae-0e69-a839dde7d8ed.png)

### データソース種類の選択
数多くのリストの中からPostgreSQLデータベースを選択します。
![データ取得画面.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/b5b537e8-f350-f032-f51c-24c91c3b6709.png)

### データベース接続先とクエリの設定
PostgreSQLを選択し終えたら、データベースのサーバと名前を入力する画面に遷移します。
データベースサーバなのですが、IPアドレスとポート番号を入力する必要が有ります。ポート番号は忘れる可能性が高いので注意。
例えば、IPアドレスが【192.168.1.30】で、ポート番号が【5432】の場合、【192.168.1.30:5432】がサーバに入力する文字列です。

この2つを入力した後に、【詳細設定オプション】を押下して、下記のSQLを貼り付けます。

```sql
SELECT
  CASE
    WHEN sensor_value < 2500 THEN 1
	ELSE 0
  END AS "照明",
  create_timestamp AS "日時"
FROM
  public.sensor_light_table
WHERE
  create_timestamp
    BETWEEN '2023-12-19 00:00:00' AND '2023-12-20 00:00:00'
ORDER BY
  create_timestamp ASC
```


![db接続.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/30c0077b-d628-9352-1938-683cf339822a.png)

本編関係ないですが、SQLの貼り付けは、【はまりポイント】なんで一言いいます！

:::note alert
PowerBIのSQL最後に、【;】(セミコロン)いれてはいけないです。エラーが出ます。そして、素直にエラーメッセージに従って解決しようとすると沼ります、解決できません。私は3時間くらいこれではまりました...
:::

### ユーザ認証の入力
データベースにアクセスできるユーザを選択します。
![ユーザ認証.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/75794cda-c7d4-92c8-ccec-b198b36e276c.png)

接続に成功するとデータの一部が確認できます。
問題なさそうなら、【読み込み】を選択して次の画面に進みます。

![PowerBIでのテーブルデータ表示.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/5aa70ca7-46d0-b84f-0211-fbfc7f0f3775.png)


### データの選択
右側の【データ】バー内の【クエリ1】の中から、【照明】と【日時】のチェックマークを付けます。
次に、隣の【視覚化】バーの中央くらいにある、
　x軸に【日時】
　y軸に【照明】
を入れましょう。

### グラフ種類の選択
今回は棒グラフをx軸方向に時系列につなげていったものを作成したいので、右側の【視覚化】 > 【集合縦棒グラフ】を選択します。
![集合縦棒グラフを選択.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/3eaff815-6dff-ca6f-3035-d4edbb7af679.png)

### データ型の変換
日付まで入れた時系列グラフを作成したかったのですが、使い方が分からず出来ませんでした...
代案として、【日時】を【日付 + 時刻】型のデータではなく、【時刻】型のデータに変更し、時系列に表示できるようにしました。
![時刻型への変換.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/62004581-1355-bb97-c929-38be5ea455ef.png)


### 完成形
これまでの内容で、下記のようなグラフを得ることが出来ました。
![2023-12-19の測定結果.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/e8ba25c3-e1d6-54c0-c971-2719a81920ec.png)

こういったグラフに表示することで、分析につなげることが出来ます。
たとえば、ほぼ一日電気が付いていることから、この日が在宅仕事だと予想できるかもしれません。
また、12:00頃に不自然に電気が消えているのに、何か現場でしか分からない事情があるかもしれません。こういったものについては関係者にヒアリングしてみると面白い結果が得られるかもしれないですね。
![いらすとや_睡眠.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/1a9d5521-19d0-6778-68e2-cc6029cd6c5f.png)


# 最後に
意外と長かったです。自分で書いていて驚きました...
少し大変だと思いますが、一回やってみてイメージをつけると基礎がついてくると思います。
いろいろと自分でできるようになると楽しいと思いますのでよかったらチャレンジしてみてください。
ちなみに、私が一番難しいと感じたのはPowerBIの使い方でした。