TOP > ラズパイ > できること > ラズパイとセンサで楽しむお手軽IoT【第4回】

できること

ラズパイ

うすだひさし

ラズパイとセンサで楽しむお手軽IoT【第4回】

RDBMSの情報をWebで可視化する

 

 

第1回：Webでデバイスを操作してみよう
第2回：Webサービスでデバイスを操作する
第3回：センサの値をRDBMSに記録する

 

loTという言葉が一般的なものとなって久しい昨今、「loT」関連の記事も多くインターネット上に流れていると思います。そんな「loT」を独自の切り口で楽しく紹介してくれる、うすだひさしさんの連載も今回が最終回。第3回ではセンサの値をRDBMSに記録する方法を紹介しましたが、今回はそのRDBMSの情報をWebで可視化する手順を見ていきたいと思います。

 

目次

  1. はじめに
  2. 準備
  3. RDBMSのデータをCSVで出力する
  4. RDBMSのデータをWebで取得する
  5. C3.jsでグラフ表示する
  6. まとめ

 

1. はじめに

「ラズパイとセンサで楽しむお手軽IoT」と称して、ラズパイにつないだデバイスを外部から操作したり、センサの情報を外部から読み取ったりする連載の最終回です。

第3回では、センサメダルから温度や湿度などのデータを読み取り、RDBMS（Relational DataBase Management System、リレーショナルデータベース管理システム）である「PostgreSQL」にデータを記録しました。
IoTでは、モノからデータを収集して蓄積することが重要ですが、そのデータを分析して活用することも重要です。データを活用することが本来の目的であり、そのためにデータを収集している、と言ってしまってもよいかもしれません。

そこで今回は、RDBMSからデータをCSV形式で取得できるようにして、さらにチャートのライブラリ「C3.js」を使って可視化する方法を紹介したいと思います。
今回の構成は下記の通りです。

easy-iot-with-raspberry-pi-and-sensor-04-01

 

2. 準備

今回必要となるものは、下記です。

第1回でおこなったApacheの設定(WSGIの有効化）と、第3回でおこなったPostgreSQLの設定（センサメダルのデータをPostgreSQLに記録）をおこなっておいてください。

 

3. RDBMSのデータをCSVで出力する

まず、PostgreSQLに記録されているデータを活用するには、活用できるフォーマットでファイルなどに出力する必要があります。
PostgreSQLの「COPY」コマンド（標準SQLのコマンドではありません）を使えば、CSV形式などのファイルに出力できます。

第3回ご紹介した「psql」コマンドを使って、例えば下記のように実行すると、「sensormedaldata」テーブルの全データを「/tmp/sensormedaldata.csv」というファイルに出力できます。
(下記の「$」はシェルのプロンプトです。以下同様。)

$ psql -c "COPY sensormedaldata TO '/tmp/sensormedaldata.csv' WITH CSV HEADER DELIMITER ','"

なお、COPYコマンドは「postgres」ユーザの権限で実行されるため、postgresユーザが書き込めるパスを指定する必要があります。例えば、/home/piに出力しようとしても、書き込み権限がないため、下記のようにエラーになります。

$ psql -c "COPY sensormedaldata TO '/home/pi/sensormedaldata.csv' WITH CSV HEADER DELIMITER ','"
ERROR: ファイル"/home/pi/sensormedaldata-20210403.csv"を書き込み用にオープンできませんでした: 許可がありません

sensormedaldataテーブルのデータすべてではなく、日時で範囲を限定したい場合は、標準SQLの「SELECT」コマンドを使います。例えば、2021年4月1日午後6時から4月2日午前6時までのデータを「/home/pi/sensormedaldata-part.csv」へ保存するには、下記のように実行します。

$ psql -A -F, -c "SELECT * FROM sensormedaldata WHERE date >= '2021-04-01T18:00' AND date <= '2021-04-02T06:00'" | grep -v '^(' > ~/sensormedaldata-part.csv

psqlコマンドの出力の最後の「(xx行)」を、grepコマンドで取り除いています。

ちなみに、ファイルの出力を（PostgreSQLのコマンドではなく）シェルのリダイレクトでおこなっているため、ここでは/home/piに出力できます。
出力したCSVファイルをもとに、みなさんご存じの通り、ExcelやCalc、Googleスプレッドシートなどの表計算ソフトを使って、集計や分析を行うことができます。

下記は、Calcでの使用例です。

easy-iot-with-raspberry-pi-and-sensor-04-02

 

4. RDBMSのデータをWebで取得する

上記をおこなうには、ラズパイにログインする必要があります。ラズパイで分析などの処理をおこなわない場合は、出力したファイルを実際に処理するマシンへコピーする必要がありますが、面倒くさいですよね。そこで、WebでCSVを取得できるようにしてみましょう。

semsormedaldataテーブルのデータをCSV形式で出力するWSGIアプリケーションを下記に示します。テキストエディタで下記を入力(あるいはコピペ)し、「/home/pi/wsgi/smpg2csv.py」というファイル名で保存してください。

import re
import psycopg2
from urllib.parse import parse_qs

def check_date(d):
  if re.match(r'^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}$', d):
    return True
  else:
    return False

def selectdb(f, t):
  hdrs = []
  rows = []
  try:
    conn = psycopg2.connect(host='localhost', dbname='pi', user='pi', password='パスワード')
  except Exception as e:
    print('Error: ', e)
    return rows
  sql = 'SELECT * FROM sensormedaldata WHERE date >= %s AND date <= %s'
  try:
    with conn.cursor() as cur:
      cur.execute(sql, (f, t))
      hdrs = [col.name for col in cur.description]
      rows = cur.fetchall()
  except Exception as e:
    print('Error: ', e)
  conn.close()
  return hdrs, rows

def application(env, start_response):
  start_response('200 OK', [('Content-Type', 'text/plain; charset=UTF-8')])
  form = parse_qs(env['QUERY_STRING'])
  if 'f' not in form or 't' not in form:
    return [b'Missing parameters']
  if not check_date(form['f'][0]) or not check_date(form['t'][0]):
    return [b'Invalid parameters']
  hdrs, rows = selectdb(form['f'][0], form['t'][0])
  ret = ','.join(hdrs) + '\n'
  for row in rows:
    dstr = row[6].strftime('%Y/%m/%d %H:%M:%S.%f')
    ret += '%s,%s,%s,%s,%s,%s,\'%s\'\n' % (row[0], row[1], row[2], row[3], row[4], row[5], dstr)
  return [ret.encode()]

smpg2csv.pyで使用するパラメータは「f」と「t」です。どちらも「YYYY-mm-ddTHH-MM」という形式で日時を指定します。fからtまでの間のデータをSELECTコマンドで取得し、CSV形式で出力します。

例えば、ラズパイのWebブラウザ「Chromium」で「http://localhost/wsgi/smpg2csv.py?f=2021-04-01T18:00&t=2021-04-02T06:00」にアクセスすると、2021年4月1日午後6時から4月2日午前6時までのデータが得られます（「localhost」をラズパイのIPアドレスに変更すれば、他のマシンのWebブラウザからアクセスできます。以下同様）。
ただ、WSGIアプリケーションを直接実行するのは面倒です。最低限ではありますが、日時を指定してCSVを取得するHTMLを作成してみました。

テキストエディタで下記を入力(あるいはコピペ)し、「/var/www/html/sm2csv.html」というファイル名で保存してください。

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <title>SensorMedal Data</title>
</head>
<body>
  <p><form method="get" action="/wsgi/smpg2csv.py">
    <input type="datetime-local" name="f" /> - <input type="datetime-local" name="t" /><br />
    <input type="submit" value="get" />
  </form></p>
</body>
</html>

そして、「http://localhost/sm2csv.html」にアクセスし、日時を指定してボタンをクリックすると、CSVファイルを取得できます。

easy-iot-with-raspberry-pi-and-sensor-04-03

 

5. C3.jsでグラフ表示する

データの範囲を指定して、Webでデータを取得できるようにしました。最後に、データをダウンロードせず、Webで直接可視化して確認できるようにしてみます。
C3.jsを使って、Webでグラフ表示を行います。C3.jsは、折れ線グラフや棒グラフ、円グラフなど様々なチャートを比較的簡単に描画できるJavaScriptのライブラリです。
まず、下記の手順でC3.jsのソースコードをダウンロードし、CSSとJavaScriptのファイルをドキュメントルート(/var/www/html)にコピーします。
（現時点での最新バージョン0.7.20を使用しています）

$ curl -LO https://github.com/c3js/c3/archive/refs/tags/v0.7.20.tar.gz
$ tar xvzf v0.7.20.tar.gz
$ sudo cp -p c3-0.7.20/c3.js c3-0.7.20/c3.min.js /var/www/html/

そして、日時の範囲を指定してグラフを表示するHTMLを作成します。テキストエディタで下記を入力(あるいはコピペ)し、「/var/www/html/smc3.html」というファイル名で保存してください。

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <title>SensorMedal Data</title>
  <link href="c3.min.css" rel="stylesheet" />
  <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20src%3D%22https%3A%2F%2Fd3js.org%2Fd3.v5.min.js%22%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
  <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20src%3D%22c3.min.js%22%20charset%3D%22utf-8%22%3E%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
</head>
<body>
  <p>
    <input type="datetime-local" name="f" /> - <input type="datetime-local" name="t" /><br />
    <input type="button" value="get" onclick="showchart()" />
  </p>
  <div id="chart"></div>

  <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cscript%20language%3D%22javascript%22%3E%0A%20%20function%20showchart()%20%7B%0A%20%20%20%20var%20f%20%3D%20document.getElementsByName('f')%5B0%5D.value%3B%0A%20%20%20%20var%20t%20%3D%20document.getElementsByName('t')%5B0%5D.value%3B%0A%20%20%20%20var%20chart%20%3D%20c3.generate(%7B%0A%20%20%20%20%20%20bindto%3A%20'%23chart'%2C%0A%20%20%20%20%20%20data%3A%20%7B%0A%20%20%20%20%20%20%20%20x%3A%20'date'%2C%0A%20%20%20%20%20%20%20%20xFormat%3A%20'%5C'%25Y%2F%25m%2F%25d%20%25H%3A%25M%3A%25S.%25f%5C''%2C%0A%20%20%20%20%20%20%20%20url%3A%20'%2Fwsgi%2Fsmpg2csv.py%3Ff%3D'%20%2B%20f%20%2B%20'%26t%3D'%20%2B%20t%2C%0A%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20axis%3A%20%7B%0A%20%20%20%20%20%20%20%20x%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20type%3A%20'timeseries'%2C%0A%20%20%20%20%20%20%20%20%20%20tick%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20format%3A%20'%25H%3A%25M'%2C%0A%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%7D)%3B%0A%20%20%7D%0A%20%20%3C%2Fscript%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="&lt;script&gt;" title="&lt;script&gt;" />
</body>
</html>

上記では、ボタンをクリックすると、「showchart」という関数を実行するようになっています。この関数では、fとtで指定された日時をもとに、前述のWSGIアプリケーションからCSVを取得し、それをそのままグラフのデータに指定しています。

X軸にはdateを指定し、そのフォーマットも(xFormatで)示しています。また、日時をすべて表示すると見づらいため、時間だけ表示するようにしています。
最後に、「http://localhost/smc3.html」にアクセスし、日時を指定してボタンをクリックします。すると、下記のようにグラフが表示されます。グラフにカーソルを合わせると、その箇所の値を確認できます。

easy-iot-with-raspberry-pi-and-sensor-04-04

 

すべてのデータを1つの折れ線グラフで表示しているため、見づらいかもしれません。そんなときは、凡例をクリックすることで、必要なデータだけ表示することができます。下記では、温度（temperature）だけを表示しています。

easy-iot-with-raspberry-pi-and-sensor-04-05

 

6. まとめ

以上、RDBMS(PostgreSQL)のデータをCSV形式で取得できるようにしたり、グラフで可視化したりする方法をご紹介しました。
C3.jsの説明は概ね省きましたが、サイトには例やリファレンスがありますので、不明点があれば覗いてみてください。
今回は、CSV形式で取得するHTMLと、グラフ表示するHTMLを別で作成しました。1つにまとめると、グラフで確認してからダウンロードできて便利かもしれません。簡単ですので、興味のある方はやってみてください。

これで本連載は終了となります。最後までお読み頂きありがとうございました！

 

 

今回の連載の流れ

第1回：Webでデバイスを操作してみよう
第2回：Webサービスでデバイスを操作する
第3回：センサの値をRDBMSに記録する
第4回：RDBMSの情報をWebで可視化する（今回）

アバター画像

LinuxやRTOSの移植・ドライバ開発、Unix系OSのサーバ構築・管理、自動運転(Autoware)関連の開発に関わった後、現在はなぜかテストエンジニアに。毎日栗の絵も描いています。

https://www.usupi.org/

NHK学生ロボコン2018

関連記事