概要

WROOM-02で温度／湿度計測　その１では、WROOM-02とHDC-1000を組み合わせて、温度と湿度を定期的に測定しサーバーに送信するための仕組みについて掲載した。その後、たまに設置場所を変えてはいるが、ほぼ動かしたままで快調にデータの測定と送信を繰り返している。

今回はサーバー側の話になるが、残念なことに電子工作はなくて、プログラミングの話だけになる（題名に問題ありかもしれない）。

測定データの格納と測定データのクエリを行うプログラム（php）と、データをグラフ表示するhtml（javaScript）を掲載する。

サーバー側の構成

概要

今回は、数年前から安定して動作している httpd (apache 2.2.3)とphp 5.5.18、および、mysql (5.0.95)が導入されているLinux (CentOS 5.11) の仮想サーバーを用いた（当ブログのホストとは異なります）。

以下、上記の動作環境に対する操作として記載する。

データベース関係

準備のためのスクリプト

準備としては、mysqlの上に専用のデータベース（envdata）とデータベースユーザー（envuser）を作成し、所定のテーブル（envdata）を作成する。あとは、phpのプログラムを書くだけ。データベース関連の準備は以下のようなスクリプトで行った。

create database envdata default character set utf8 collate utf8_general_ci;

grant all privileges on envdata.* to envuser@localhost identified by 'password';

use envdata;

create table envdata(

serial_no int not null AUTO_INCREMENT

,point_id varchar (20) not null

,T float (7,2) null

,H float (7,2) null

,X1 float (7,2) null

,X2 float (7,2) null

,post_datetime timestamp default CURRENT_TIMESTAMP not null

,PRIMARY KEY (serial_no)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

カラムX1とX2は拡張用に用意した。気圧でもとれるとよいのだが。

リモートデータを受け取るためのデータベースユーザーを作ったときには、

mysql> select user,password, host from mysql.user;

を実行し、このデータベースユーザーが所定のホスト（この場合は localhost ）からしか接続できないことを確認した方がよいだろう。

測定データの格納とクエリ

センサーを接続したWROOM02 （以降、センサーと称する）は、このサーバーのtcp/80にhttp接続し、以下のような具合のGETリクエストで測定データを投げてくる。

/data/store_data.php?point_id=18:FE:34:xx:xx:xx&T=26.87&H=43.95

point_idがMACアドレス、Tが温度、Hが湿度である。リクエストから分かるように、store_data.php というphpプログラムで受けており、このプログラムがテーブル envdata にinsertを実行する。測定時刻としては、テーブルへのinsert時刻（サーバー時刻、 CURRENT_TIMESTAMP ）が post_datetime カラムに格納される。

前回のハードウェア編にあるように、WiFiルーターにつながらなかった場合や、センサーの電源を切っている場合には欠測となりデータは格納されないが、そのあたりには配慮していない。

今回掲載する3つのphpプログラム（envdata_db.php , store_data.php, readdata_json.php）は、いずれもhttpdのコンテンツディレクトリ（リクエストディレクトリの /data に対応する物理ディレクトリ）に格納しておく必要がある。どの物理ディレクトリにするのかは、httpd.confやconf.dディレクトリ内のconfファイルに書いたとおりになる。

データベース処理クラス envdata_db

データベースに接続するデータ格納とクエリ機能は、プログラムのブロック化を意識して一つのクラス (envdata_db.php) にまとめた。以下にソースコードを示す。

<?PHP

class envdata_db {

private $mysql; // コネクション

private $last_error;

private $last_sql;

public function __construct(){

$this->mysql= mysqli_connect("localhost", "envuser", "password", "envdata");

$this->last_error = mysqli_connect_error();

}

public function __destruct() {

if ($this->mysql)

mysqli_close($this->mysql);

$this->mysql = FALSE ;

}

public function isConnect() {

return ($this->mysql != FALSE);

}

public function getLast_error() {

return $this->last_error;

}

public function getLast_sql() {

return $this->last_sql;

}

public function insert_data($point_id, $t, $h, $x1, $x2) {

if (!$this->isConnect())

return FALSE;

$id = mysqli_real_escape_string($this->mysql, $point_id);

$sql = "insert into envdata (T, H, X1, X2, point_id) values ($t, $h, $x1, $x2, '$id')";

$this->last_sql = $sql;

if (!mysqli_query($this->mysql, $sql)) {

$this->last_error = mysqli_error($this->mysql);

return FALSE ;

}

return TRUE;

}

public function query_data_json($condition = "") {

if (!$this->isConnect())

return FALSE;

$today = TRUE;

$sql = "select post_datetime as datetime,T,H from (select * from envdata order by serial_no desc limit 120) as A";

$sql = $sql . " order by serial_no asc";

$this->last_sql = $sql;

if (($query_result = mysqli_query($this->mysql, $sql)) == FALSE) {

$this->last_error = mysqli_error($this->mysql);

return FALSE ;

}

$result_count = mysqli_num_rows($query_result);

$result = array();

while ($r = mysqli_fetch_assoc ($query_result))

$result[] = array('datetime' => str_replace("-", "/", $r['datetime']), 'T' => (float)$r['T'], 'H' => (float)$r['H']);

mysqli_free_result($query_result);

$json_result = json_encode(array('count' => $result_count, 'data' => $result), JSON_UNESCAPED_SLASHES);

return $json_result;

}

このクラスのインスタンスが作成された時にデータベース接続を確立し、insert_data()によってデータを格納、query_data_json()によりJSON形式でクエリ結果を返す。そしてデストラクトされるときに接続を閉じる。httpリクエストの都度ごとに、構築～クエリ～廃棄が速やかに行われることを想定している。

今回は測定結果をgoogle chartを使ってグラフ表示することだけを考えたので、クエリ用のメソッドとしては query_data_json() のみを用意した。簡単のためにクエリ条件はなく、以下のSQL文によって最新の120件（2分間ごとの測定ならば4時間分）をJSON形式の文字列として返す。

select post_datetime as datetime,T,H from
(select * from envdata order by serial_no desc limit 120) as A
order by serial_no asc

最新120件を得るために、サブクエリによって新規順120件の結果セットを作り、そこから古い順に取り出すというよく使われる手法。
SQLクエリが成功するとその結果セットの各行に対して mysqli_fetch_assoc() でカラム名をキーとする連想配列を得ている。そして、この連想配列内の日付の形式を変換したり、文字列を数値に直したりしてから、json_encode() を行っているが、これらは google chartでうまく表示するための小細工である。

データの受け取りと格納( store_data.php )

センサー側からのデータを受け取り、envdata_dbクラスを呼び出してデータベースに格納する。

<?PHP

require_once('envdata_db.php');

function check_store_data($key, $default) {

$v = isset($_GET[$key]) ? $_GET[$key] : $default;

if ($v == "")

$v = $default;

return $v;

}

$t = check_store_data('T', 0.0);

$h = check_store_data('H', 0.0);

$x1 = check_store_data('X1', 0.0);

$x2 = check_store_data('X2', 0.0);

$pid = check_store_data('point_id', 'unknown');

header('cotent-type: text/html');

$envdata = new envdata_db();

if (!$envdata->insert_data($pid, $t, $h, $x1, $x2)) {

echo "NG SQL ERROR: " . $envdata->getLast_error() . '<br/>';

echo $envdata->getLast_sql() . '<br/>';

} else {

echo "OK";

}

先のenvdata_dbクラスの内容から明らかなように、測定データ関連のリクエストパラメータにfloatと解釈できないデータが入っていると、insertは失敗する。とりあえず、check_data() という関数によって、パラメータ省略時や空白の場合は0.0という数値を入れることにした。

一応、insert_data()が失敗したときには、その内容をhtmlで返すような内容になっているが、センサー側で応答文字列を解釈したとしても、基本的に人間は介在しないわけなので、あまり意味がない。LEDを点滅させたところで誰も気づかないのである。あくまでも机上のテスト用というところ。

クエリ結果の出力( readdata_json.php )

このプログラムは、ブラウザで動くjavaScript内からjsonデータをリクエストするときに使うもので、人間が目でみるためのものではない。envdata_dbクラスquery_data_json()メソッドを呼び出すことで、JSON形式の結果を得た、httpリクエストの応答として返す。

<?PHP

require_once('envdata_db.php');

$envdata = new envdata_db();

$r = $envdata->query_data_json();

if (!$r) {

header('Content-Type: text/html');

echo $envdata->getLast_error() . "<br/>\n";

echo $envdata->getLast_sql() . "<br/>\n";

} else {

header('Content-Type: application/json; charset=utf-8');

echo $r;

}

クエリ成功時には、’application/json’というコンテントタイプを用いてデータを応答する。失敗時には人間用のエラーメッセージを返すが、これもテスト用でjavaScriptでエラーを判定することは考えていない。

ブラウザから直接 /data/readdata_json.php をリクエストしてみると、以下のような結果が得られる（見やすさのため改行とインデントを入れているが、実際には入らない）。結果セットの行数を count というキー名で、結果セットの各行の配列を data というキー名で表現している。そして、各結果セットは、datetime, T, Hというカラム名（またはその別名）がキーとなっている。

{"count":120,

"data":[

　{"datetime":"2016/09/01 08:44:14","T":29.58,"H":60.39},

　....,,

　{"datetime":"2016/09/01 12:55:18","T":27.79,"H":59.83}

　]

}

データ取得用のプログラムやクエリのためのメソッドに、必要に応じて検索条件を付加するなどの拡張は容易だろう。

休憩

脈絡もなく最近マクロレンズで写したアブラゼミの顔を。もう夏も終わりですね。

複眼の偽瞳孔がよく見える。

グラフ表示用html

Google Chartsの利用

googleさんが用意している、google.visualization.LineChart クラスを使って、ブラウザにグラフを表示する。このクラスを利用すれば、与えたデータを使って指定の<div>の中にSVGでグラフを描いてくれる。サーバー内でグラフを描画してpng形式のイメージを返すというやり方もあるが、データをビジュアルに表示したいときには、Google Charts を使うのが手軽で簡単。

LineChart1 — google.visualization.LineChart() による描画

温度と湿度の2系統のデータを表示すると、こんな具合になる。湿度に比べて温度の変化が少ない。

data_chart.html

このグラフを表示するためのhtml (data_chart.html) は以下のとおりで、ほとんどjavaScriptになっている。

<!DOCTYPE html>

<html>

<head>

<title>Thermal and Humidity</title>

google.charts.load("visualization", "1", {packages: ["corechart"], language:"ja"});

google.charts.setOnLoadCallback(drawChart);

function drawChart() {

var query_json = "readdata_json.php";

$.getJSON(query_json, function(json) {

var data = new google.visualization.DataTable();

data.addRows(json.data.length);

data.addColumn("datetime", "日時");

data.addColumn("number", "温度");

data.addColumn("number", "湿度");

var first = null;

var last = null;

$.each(json.data, function(i, v) {

last = new Date(v.datetime);

if (first == null)

first = last;

data.setValue(i, 0, new Date(last));

data.setValue(i, 1, v.T);

data.setValue(i, 2, v.H);

});

var options = {

title: "センサーデータ (" + json.count + "件) [" + first.toLocaleString() + " ～ " + last.toLocaleString() + "] ",

lineWidth: 1,

fontSize: 12,

chartArea: {left: '80px', top: '80px', width: "80%", height: "80%"}

};

var chart = new google.visualization.LineChart(document.getElementById("chart_div"));

chart.draw(data, options);

});

}

</script>

<style>

div#chart_div { height: 500px; width: 100%; border: 1px solid #000000; }

</style>

</head>

<body>

</body>

</html>

簡単に説明すると、

var query_json = “readdata_json.php”;

サーバーからjsonデータをGETするためのアドレスを指定する。今回は、このhtml (data_chart.html) と同じディレクトリに置いているから、ホスト名やパス名の指定はない。わざわざ変数にする必要はないのだが、データの取得条件を変更するためにリクエストパラメータを加工したりするときに、<form>から値を書き換えることを考慮した。

$.getJSON(query_json, function(json) { …. });

jQueryのgetJSON()によってquery_jsonで指定されるURLからjsonデータをGETしてくる。うまくとれたら、{ … } の部分の処理が走る。うまくとれたときにはgoogle.visualization.DataTable に取得してきたデータを展開しているが、そこでは、

DataTableオブジェクトに追加する行数を指定（addRows()）
DataTableオブジェクトの構成の指定（addColumn()）
開始／終了日時の取得（first, last）
文字列として送られてくる日付（v.datetime）をjavaScriptのDateに変換
系列データのセット（ data.setValue(…)）

を行っている。そして最後に、指定のdiv（chart_div）に google.visualization.LineChart.draw()　を実行している。

var optionsには、chart.draw() 時の各種オプションを指定しており、グラフのタイトル、線や文字の大きさ、描画領域（ chart_div ）に占めるチャート領域のサイズなどを指定する。オプションのchartAreaを指定しないと、余白の大きな間延びした表示になってしまう。

休憩２

日陰が好きなくせに明るい窓にきたヒカゲチョウを窓ガラス越しに撮影。

ローソクグラフも使ってみる

Google Chartsのギャラリーには、いろいろなパターンのグラフが用意されている。google.visualization.ComboChart を使って、株価などに使われるローソクグラフと折れ線グラフを組み合わせ、各測定日の最高気温と最低気温を縦棒の矩形で表現してみた。

中央に測定日と平均気温が表示されているが、これはマウスポインタを置いたときにでてくるツールチップ表示である。時刻（00:00:00）が余計なのだが、表示内容をカスタマイズすることもできるようだ（やってませんけど）。

本当はローソクの矩形の上に平均値の線を描きたいのだけど、そういうオプションが見つからない。ローソクグラフ（candleSticks chart）では、矩形を塗りつぶした場合の透明度（opacity）も指定できないようである。

データのクエリ

各測定日毎の最高、最低、平均気温を得るために、サーバー側のenvdata_db.phpにSQLの集計関数を用いたメソッドを追加した。

public function query_candledata_json() {

if (!$this->isConnect())

return FALSE;

$sql = "select min(T) as T1,max(T) as T2,avg(T) as TA, DATE(post_datetime) as datetime from envdata group by DATE(post_datetime) order by serial_no";

$this->last_sql = $sql;

if (($query_result = mysqli_query($this->mysql, $sql)) == FALSE) {

$this->last_error = mysqli_error($this->mysql);

return FALSE ;

}

$result_count = mysqli_num_rows($query_result);

$result = array();

while ($r = mysqli_fetch_assoc ($query_result))

$result[] = array('datetime' => str_replace("-", "/", $r['datetime']), 'T1' => (float)$r['T1'], 'T2' => (float)$r['T2'], 'TA' => (float)$r['TA']);

mysqli_free_result($query_result);

$json_result = json_encode(array('count' => $result_count, 'data' => $result), JSON_UNESCAPED_SLASHES);

return $json_result;

}

DATE(post_datetime)でGROUP BYした対象から、最高、最低、平均を得ており、平均値は’TA’というキーで返す。
javaScriptからのGETリクエストを受けて上記のメソッドを実行するプログラムは、前のものとほとんど同じなので省略。

data_candle.html

上に載せたグラフを表示するためのhtmlを以下に示す。折れ線グラフのものとあまり変わらない。

<!DOCTYPE html>

<html>

<head>

<title>Thermal and Humidity</title>

google.charts.load("visualization", "1", {packages: ["corechart"], language:"ja"});

google.charts.setOnLoadCallback(drawChart);

function drawChart() {

var query_json = "read_candledata_json.php";

$.getJSON(query_json, function(json) {

var data = new google.visualization.DataTable();

data.addColumn("datetime", "datetime");

data.addColumn("number", "T1");

data.addColumn("number", "T2");

data.addColumn("number", "平均");

data.addRows(json.data.length);

var first = null;

var last = null;

$.each(json.data, function(i, v) {

last = new Date(v.datetime);

if (first == null)

first = last;

data.setValue(i, 0, new Date(last));

data.setValue(i, 1, v.T1);

data.setValue(i, 2, v.T1);

data.setValue(i, 3, v.T2);

data.setValue(i, 4, v.T2);

data.setValue(i, 5, v.TA);

});

var options = {

title: "センサーデータ (" + json.count + "件) [" + first.toLocaleDateString() + " ～ " + last.toLocaleDateString() + "] ",

fontSize: 14,legend: 'none',

vAxis: {title: '温度'}, hAxis: {title: '日付'},

chartArea: {left: '80px', top: '80px', width: "82%", height: "80%"},

seriesType: 'candlesticks',

series : { 0: {color: 'green'}, 1: {type: 'line'}}

};

var chart = new google.visualization.ComboChart(document.getElementById("chart_div"));

chart.draw(data, options);

});

}

</script>

<style>

div#chart_div { height: 500px; width: 100%; border: 1px solid #000000; }

</style>

</head>

<body>

</body>

</html>

基本的な構造や流れの説明は省略。ComboChartでは複数種類のグラフを組み合わせることになるので、データ系列ごとのグラフの種別を指定してやる必要がある。

DataTableの構成

candlestick chartを使う場合、X軸の値1つに対して4つの値が必要になる（市場取引での、安値、始値、終値、高値に対応）。今回は最低気温、最高気温の2つしかないので、おのおの2回ずつ addColumn() してやり、最後に平均値を addColumn()し、2系列のデータとしている。DataTableのインスタンスに対する setValue()についても同様。

var options内のseriesType: ‘candlesticks’

グラフ全体の主たるタイプを、ローソクグラフに対応する ‘candlesticks’ としている。

series : { 0: {color: ‘green’}, 1: {type: ‘line’}}

最初のデータ系列（0番）は主たるタイプの candlesticks になるが、その色を指定している。データ系列1番のtypeを、‘line’ とすることで平均値を線グラフとして描画させる。

最後に、google.visualization.ComboChart.draw() を使って描画している。

系列間のインターバル表示

ローソクグラフを使った表示が今一つ満足できなかったので、isStacked: trueとしたColumnChart（積み重ね棒グラフ）とLineChartを組み合わせ、棒グラフ中の特定の系列表示色をtransparentにする、なんていうこともやってはみた。

画像では伝わりにくいが、いろいろと今一つなのである。
より良さそうなチャートを探していたら、重要なデータ系列を強調しつつ、それに付随する複数のデータ系列を同時に描画する Intervals という機能を見つけたので、さっそく使ってみた。

インターバル表示いろいろ

先ほどのローソクグラフと同じデータを用いて、とりあえず3種類のインターバル表示を行ってみた。htmlは共通で、ラジオボタンに対する操作で種類を変えて再表示させるようにした。

data_interval.html

これらのインターバル表示を行うためのhtmlを以下に示す。グラフ表示の変更は、google.visualization.LineChart.draw() に描画オプションとして与えるプロパティ値だけで変化するので、画面上のform要素に対する操作で表示が切り換わるようにした。

<!DOCTYPE html>

<html>

<head>

<title>Thermal and Humidity</title>

google.charts.load("visualization", "1", {packages: ["corechart"], language:"ja"});

google.charts.setOnLoadCallback(drawChart);

var interval_style = {'style' : 'line'};

function drawChart() {

var query_json = "read_candledata_json.php";

$.getJSON(query_json, function(json) {

var data = new google.visualization.DataTable();

data.addColumn("datetime", "datetime");

data.addColumn("number", "平均");

data.addColumn({id:'T1', type:'number', role:'interval'});

data.addColumn({id:'T2', type:'number', role:'interval'});

data.addRows(json.data.length);

var first = null;

var last = null;

$.each(json.data, function(i, v) {

last = new Date(v.datetime);

if (first == null)

first = last;

data.setValue(i, 0, new Date(last));

data.setValue(i, 1, v.TA);

data.setValue(i, 2, v.T1);

data.setValue(i, 3, v.T2);

});

var options = {

title: "センサーデータ (" + json.count + "件) [" + first.toLocaleDateString() + " ～ " + last.toLocaleDateString() + "] ",

fontSize: 14, vAxis: {title: '温度'}, hAxis: {title: '日付'}, legend: 'none',

chartArea: {left: '80px', top: '80px', width: "80%", height: "80%"},

curveType: 'function'

};

options.intervals = interval_style;

var chart = new google.visualization.LineChart(document.getElementById("chart_div"));

chart.draw(data, options);

});

}

function redrawChart(s) {

interval_style['style'] = s;

drawChart();

return true;

}

</script>

<style>

div#chart_div { height: 500px; width: 100%; border: 1px solid #000000; }

</style>

</head>

<body>

<input type="radio" name="types" value="boxes" onclick="return redrawChart(this.value);">boxes</input>

</form>

</body>

</html>

基本的にはLineChartなのだが、google.visualization.DataTable オブジェクトに対してカラム構成を指定するとき、role:’interval’　としてカラムに役割を与えている点が、単純なグラフと異なる点になる。詳しくはこちらを参照のこと。

表示するグラフの切換は、chart_div の下に作ったhtmlフォームにラジオボタンを配置し、options.intervals に与えるプロパティ値を作り、draw_chart() させることで実現している。

サンプルデータとファイル

このブログ内にも、data_interval.html を置いたので興味のある方はどうぞ。さすがに、センサーと直結するデータベースは置きたくなかったので、表示用のjsonデータ (data_sample1.json) もファイルとして配置し、$.getJSON() の読み込み先を変更した。気が向いたら、たまにデータファイルを入れ替えます。

まとめ

もともとは、WROOM02と温度／湿度センサーを使ったちょっとした工作のつもりだったが、結果としてデータの蓄積や表示に重きをおくことになってしまった。見直されないデータにはあまり意味がないから、今回Google Charts をいろいろ触ることができたのは良かった。
測定開始当時にセンサーを置いていた部屋にはエアコンが入っていたので、最低－最高温度にけっこうな差がでていた。人がいない間はエアコンが止まるので、平均温度が高めなのは納得できる。
途中から無人の部屋に設置したので、温度の変化が少なく、なおかつ高止まりしているが、これも季節の推移でだんだんと変わっていくだろう。
温度計測とロギングができるようになったので、次は同じブレッドボードに赤外線リモコン機能を載せ、エアコンその他自動オン／オフでもやってみる予定。また、サーバーにおいたコマンドデータを読み込み、それに応じてリモコンを動かすとかも考えられる。

東京お気楽カメラ

WROOM-02で温度／湿度計測　その２

概要

サーバー側の構成

概要

データベース関係

準備のためのスクリプト

測定データの格納とクエリ

データベース処理クラス envdata_db

データの受け取りと格納( store_data.php )

クエリ結果の出力( readdata_json.php )

休憩

グラフ表示用html

Google Chartsの利用

data_chart.html

var query_json = “readdata_json.php”;

$.getJSON(query_json, function(json) { …. });

休憩２

ローソクグラフも使ってみる

データのクエリ

data_candle.html

DataTableの構成

var options内のseriesType: ‘candlesticks’

series : { 0: {color: ‘green’}, 1: {type: ‘line’}}

系列間のインターバル表示

インターバル表示いろいろ

data_interval.html

サンプルデータとファイル

まとめ