LoginSignup
9
7

More than 5 years have passed since last update.

DelphiAdvent Calendar 2016Day 24
@kazinoue(Kazuhiro Inoue)

IoTデバイスの計測値やiBeaconを利用した位置測位結果をGoogle Spreadsheetでリアルタイムに分析するためにGoogle Formを使う

Last updated at Posted at 2016-12-23

これは Delphi Advent Calendar 2016 24日目の記事です。

そして前回の記事 固定Beaconを使って現在位置を測位するアプリをDelphi/C++BuilderとBeaconFenceで作る の続きでもあります。

まずは前回のフォローアップから

BeaconFence の位置補正精度を改善するために「パス」を設定する

前回の記事ではDelphiやC++Builderのオプション機能であるBeaconFenceを用いて位置測位アプリを作ることができました。しかし一連の記事ではビーコンを３つしか用いていないので測位精度が出づらいです。

そこで位置補正を行うために「パス」を設定します。パスはカーナビ地図での「道路」に相当すると考えていただくのがよいと思います。自動車用のカーナビでは、基本的に道路を走行していることを前提にナビゲーションを行えばよく、そのために計算で得られた位置情報を走行しているであろう道路に寄せるように補正するということができます。BeaconFenceでも「パス」を定義しておけば、パスに対して計算結果を寄せることができます。

パスの設定はビーコンの配置同様にマップエディタから行います。マップエディタのツールバーににパスのアイコンがありますので、これをクリックするとパスの描画モードに切り替わります。

Kobito.WOgd0g.png

ここでマップ上をマウスでクリックしていくと、クリックした箇所に薄茶色の四角が描画されます。さらにそこから次の場所をクリックすると、その場所にも薄茶色の四角が描画されて、なおかつそれらが線で繋がります。この操作を必要な分だけ行っていただくとパスが設定できます。必要なパスをすべて描画しおわったらESCで抜けます。

なお上記の例ではビーコン３つを頂点とする三角形の外側にもパスを描画していますが、パスを用いる場合はできるだけ三角形の内側でパスを描画するのがよいかもしれません。ビーコンを頂点とする三角形の外側の部分は位置測位の精度が下がります。三角形の外側は一部のビーコンからの距離が遠い場所なので、そのために距離計測精度が低下します。これをパスで補正するのは容易ではありません。逆の言い方をするならば「測位精度を改善したい場所はビーコンで囲む方がよい」と言えるかもしれません。

そして今回の話の前振り

2016年夏のデベロッパーキャンプでは、RAD Studio/Delphi/C++Builderの IoT デバイスサポートに関するデモを行った際に、計測データを Google Spreadsheet に送信した上でリアルタイムに可視化してみる、というデモを行いました。

その際の概要は community.embarcadero.coｍ のブログに掲載していたのですけど、あのブログはちょいと編集しづらくて、文字ばかりのブログになってしまっていました。

そこで本日の記事では、この手順をあらためて図入りでご説明したいと思います。Advent Calendar なのに新ネタでなくてすみません。

基本的な着想

ここで行う処理は「アプリで計測したデータを Google Form に送信し、Google Spreadsheet に流し込む」という内容です。

これは次のような流れで思いつきました。

  • IoTデバイスの計測データを収集したいけど、あくまで試験的な段階ではサーバを立てずにやりたい
  • 収集したデータを分析したりグラフ描きたいけど、そのためにデータをダウンロードしてExcelで開くのはメンドくさい
  • Google Spreadsheet でデータを扱えるとラクやなー
  • そういえば Google Drive には Google Form という機能でWebフォームを作れたなあー
  • そして Google Form の回答内容は Google Spreadsheet に出力することもできたっけ
  • アプリ側から Google Form を叩いてデータ送信できるなら、とりあえずプロトタイプ段階では十分に使えるかも

こういう流れで考えた内容を実際にやってみたらデータの投げ込みは案外すんなりと行けました。

さて、今回の記事では Delphi のアプリ側からは TNetHTTPClient コンポーネントを使用して Google Form にデータを投げ込むようにして、Google Form ⇨ Google Spreadsheet の連携をとりました。

これをカンタンに図にしてみると、こんなカンジです。この図では、Delphi/C++Builder向けのオプション機能であるBeaconFence（固定ビーコンで三辺測量を行って位置測位を行う機能）で測位した位置情報を Google Form 経由で Google Spreadsheet に流し込んでいます。

image

さらにこのときに Spreadsheet 側では計算式と条件付き書式の組み合わせによって簡易ヒートマップを描いています。

Google Form とは何か？

さて、Google Spreadsheet については多くの方がご存知でしょうけれど、Google Form についてご存知でない方も多いかと思いますので、ここであらためて紹介しておきましょう。

Google Form はアンケートフォームをカンタンに作成するための機能として　Google Drive 上で提供されている機能です。アンケートの回答結果は以下の3通りの方法で処理できます。

  • Google Form 上で簡易集計する
  • 結果の生データを CSV 形式等でダウンロードする
  • 回答内容を Google Spreadsheet に出力する

Google Form の簡易集計機能でも通常のアンケート集計としては十分な集計結果がレポートされるのですが、今回考えている要件は通常のアンケート集計とは違います。このような場合に回答内容を Google Spreadsheet にリンクさせておけば、さまざまな切り口で処理できるわけです。

そしてさらに興味深いのは Google Spreadsheet には「複数人で同じシートを同時に編集できる」という大変おもしろい機能が備わっていることです。Google Spreadsheet の同時編集機能では、あるシートをだれかが更新すると、その結果は同じシートを開いている全ての方に対してリアルタイムに近いかたちで更新されるのです。

そしてこのときにセルに設定した計算式や条件付き書式、グラフも再計算、再描画が行われます。

このことは Google Form と Google Spreadsheet の連携においても同様に働きます。

つまり、Google Form 経由で投げ込んだデータに対してグラフや条件付き書式を設定しておくと、データが追記されるたびにこれらが自動的に更新されるのです。

さて基本の流れの説明はここまでにしておき、実際にこれらの連携をとってみることにしましょう。今回はアドベントカレンダー2016の私のターンの一連の締めですから、最終的には BeaconFenceの測位データの送信を行ってみます。

Google Form を作る。

Google アカウントをお持ちの場合は https://forms.google.com/ または https://docs.google.com/forms/ にアクセスすると Google Form の画面が開きます。

Kobito.TvH7Od.png

ここで右下の + を押すとフォームの新規作成画面となります。開いた直後の状態では、タイトルが「無題のフォーム」、１項目だけ表示されている質問は「無題の質問」という状態です。また初めてフォームを作ったときは、フォームの使い方のガイドが表示されますので、ガイドの内容は一通り確認しておくのがよいでしょう。
Kobito.3cxzKV.png

それでは、まずは１つめの質問文を "Estimated.X" に変更します。また回答方法を「記述式」に変更します。これで１つめの項目が完成しました。

Kobito.9SPViI.png

今回のフォームでは４種類の値（計算上のX座標とY座標、位置補正後のX座標とY座標）を取得したいと思いますが、「コピーを作成」のアイコンをクリックしてみましょう。

Kobito.dZiwzo.png
すると１つめの質問の設定のコピーが自動的に作成されますので、質問文を Estimated.Y に変えましょう。あと２つコピーを作り、Path.X と Path.Y としておきます。これでフォームの基本形が完成しました。
Kobito.mmfIFq.png

Google Form と Google Spreadsheet を紐づける。

これでフォームは完成しました。ですが、さらに２つの設定調整を行います。１つめは、このフォームからの回答内容を受け取るための Spreadsheet を紐付けることです。この作業は「回答」の設定調整項目です。「回答」のタブを押して切り替わった画面には、Google Spreadsheet のアイコンが表示されていますよね。
Kobito.mbbjlx.png

このアイコンをクリックすると、今回作成したフォームの回答先とする Spreadsheet を設定できます。通常は新規のシートを作成を選びますが、既存のシートに差し込むことも可能です。
Kobito.Wsvhk7.png

さて、もう一つは G Suite (Google Apps) を利用中の方だけが対象の作業です。G Suite の Google Form では、Google にログイン中の同じ組織のメンバーだけが Form から回答を送信できます。しかし今回の作業ではアプリの内部から Form 経由で Spreadsheet に投げ込むので、Google にログインしていない状態でも Form にアクセスできるほうが便利です。そこでユーザを限定する設定を無効にします。設定画面の中に以下のようにユーザを限定する設定があります。以下のスクリーンショットでは「IDERAのユーザに限定する」にチェックが入っている箇所がそれです。このチェックを外しておくわけです。
Kobito.24U5ZO.png

Google Form に対する投げ込み用のURLやクエリーパラメータを確認する。

ここまで出来上がったら、Form に対する投げ込み用の URL やクエリーパラメータを調べてみましょう。これは実際にフォームから回答を送信しつつ、その処理をブラウザのデベロッパーツールで調べてみるのが最もカンタンです。
Kobito.J2tX8o.png

編集中のフォーム右上にある「プレビュー（目玉のアイコン）」をクリックすると回答用のフォームが開きます。ここでブラウザのデベロッパーツールを有効にしつつ、適当な回答を送信してみると、このような結果が得られます。
Kobito.ZEosIN.png

なるほど、つまり、Google Form は以下のURL形式でデータを受け取っていることがわかります。
https://docs.google.com/a/embarcadero.com/forms/d/e/[formID]/formResponse

またパラメータは entry.[num]=[value] の組み合わせで受け取っていることもわかりますよね。ここらへんは実際に作成したフォームでこれらの値を確認してみてください。

なお、Google Form ではこれらを POST で送信していますが、実際には GET で送信してもOKのようです。ここまでわかれば、データの投げ込みのパラメータ込みのURLをブラウザから試験して動作を確認してください。

たとえば、今回作成した Form の場合は以下のようなURLでデータの投げ込みが行えます。（この Form は本記事公開時点では抹消していますので、参照すると「回答は締め切られました」という表示となります）

アプリの実装にデータ送信処理を組み込む

まずは試験用のアプリでお試し

送信用のURLやパラメータが分かったら、あとは BeaconFence の測位データを流し込めばよいのです。これは TBeaconMapFencing の OnPositionEstimated イベントの中で取得できますから、それを TNetHTTPClient でリクエスト送出するだけです。

しかし Beacon が手元にないためにこれを試せないケースも多いことでしょう。そこでまずは BeaconFence の代わりに TTimer でランダムな値を生成しつつ TNetHTTPClient でデータを投げ込む試験アプリを作ってみます。

試験アプリのIDEでのコンポーネント配置はこんな感じです。TTimerとTMemoを１つ置き、TMemo は Align := Client にしています。

Kobito.eBVc2S.png

そしてTTimerのタイマーイベントに以下のように実装します。この例は Form の４つのパラメータに対してランダムな値を投げ込んでいます。

Timer1のタイマーイベント
procedure TForm1.Timer1Timer(Sender: TObject);
var
  HTTPRequest: String;
  HTTPRequestBase: String;
  QueryParameter: String;
begin
  // QueryParameter を除くベースのURL
  HTTPRequestBase := 'https://docs.google.com/forms/d/e/1FAIpQLSdKhibEYTHA7rvDcXovUH3Dsml-VIpIdLMMFPXL8_ew9YehwQ/formResponse';


  // QueryParameter を作る
  QueryParameter := Format(
    'entry.1625787623=%f&' +
    'entry.2136790809=%f&' +
    'entry.1971050741=%f&' +
    'entry.1076782688=%f',
    [
        Random()*10,
        Random()*10,
        Random()*10,
        Random()*10
    ]
  );


  // QueryParameter だけを確認用として Memo1 に出力する
  Memo1.Lines.Insert(0, QueryParameter);


  // Google Form への投げ込みを行う
  HTTPRequest := HTTPRequestBase + '?' + QueryParameter;
  NetHTTPClient1.Get(HTTPRequest);
end;

ここまで出来上がったら実際に動かしてみてください。そして同時にブラウザでスプレッドシートを開いておくとよいでしょう。アプリからのデータ投げ込みに連動して値が追加されていく様子がよく分かるはずです。

Google Spreadsheet のシートを「データリンク用」と「参照用および分析用」に分ける

ここまでの作業で Google Spreadsheet までのデータがつながりました。しかしここで以下のことに気づくことでしょう。

  • Form からの回答が送信されるシートにグラフを表示するのは使いにくいかも。
  • 作業ミスで回答内容が毀損される恐れがあるのでは。

そうなのですよ。回答が記録されるシートを直接いじるのはいろんな意味で楽しくない作業です。できれば、回答が記録されるシートとは別のシートで作業を行いたいです。実は Google Spreadsheet には、別シートの内容を SQL ライクな構文で抽出する関数があるのです。

たとえば別のシートを増やしてA1セルに以下のように記述すると、Form の回答結果を直近60件だけ抽出できます。そのときに並び順を時刻の逆順で表示できます。

=QUERY('フォームの回答 1'!A:E,"select A,B,C,D,E order by A desc limit 60")

実際の動きは以下のスクリーンショットを参考にしていただければと。

licecap.gif

このように日付のデータの逆順でデータを抽出すると、直近のデータだけを対象にグラフ描画を行いたい場合に「先頭からn行目まで」という範囲指定だけで処理が行えるのです。たとえば「1秒に1件づつデータが常に末尾に増えていく場合で直近60秒のデータを分析する」という要件を考えてみてください。ワークシート関数だけでデータの末尾の60件をグラフ化するには、グラフの範囲指定自体を毎秒書き換えるという残念な作業を行わねばならないでしょう。でも、新しいデータが常に先頭行に差し込まれているならばグラフ描画の範囲指定は最初に作成したものが常に有効に使えます。時間経過に伴って行う作業は全く要らないのですよ。

参照用および分析用のシートにグラフを差し込んでリアルタイム分析！

さて、参照用および分析用のシートにデータ抽出が行えるようになりましたから、つぎに折れ線グラフやヒストグラムを入れてみました。グラフを描画すること自体はスプレッドシートの基本機能ですから、コードを書くことなしにグラフが描けますよね。

なのでグラフを描きつつ、データの流し込みも同時に行ってみたのがこちらのスクリーンショットです。

licecap.gif

しつこいようですが、コードは1行も書いていませんよ！ (試験データを送信するクライアントアプリを除く）

なのにリアルタイムで受け取ったデータのグラフもリアルタイムに描画が更新されていく。

これって、Delphiでアプリを作るとビジュアル開発でサクサク行えるのと近いものを感じませんか？

こういう感じでデータ分析するのは、プロトタイプの時点ではとても便利に使えると思います。

COUNTIFSと条件付き書式を組み合わせてリアルタイムヒートマップ？

さて、もう一つの分析は、リアルタイムヒートマップ描画です。

まずはスクリーンショットからご覧下さい。データの更新に合わせて、スクリーンショット右側の10x10の領域の数値が更新されており、なおかつ各セルの色が数値に合わせて変わっていることがわかります。

licecap.gif

この処理は、送信された位置情報を計算上の位置を1メートル単位メッシュで集計し、その集計値の大きさに合わせて着色しています。

なお、途中で緑色が消えますが、これは条件付き書式のカラーマップ設定を1色に変更したためです。数値が0のセルが濃い緑のままだと見辛いなあ、と思いましたので、ちょいと設定を変えてみました。

この実装はさすがにコード無しでとはいいませんが、使っているのは単なるワークシート関数です。I3セル（メッシュの左上のセル）に以下の計算式が入っています。他のセルは、この計算式が入ったセル自体をコピーしたのち、残りの範囲に対してペーストしているだけです。

=COUNTIFS(
　　'シート2'!$B:$B,">=" & H$2,
　　'シート2'!$B:$B,"<"  & I$2,
　　'シート2'!$C:$C,">=" & $H2,
　　'シート2'!$C:$C,"<"  & $H3)

COUNTIFSは条件に合致したセルの個数を数える関数なのですが、上記の内容は「Estimated.Xの値が0〜1、Estimated.Yの値が0〜1のセルの数を数える」という処理を行うように条件付けしています。そして0〜1という範囲指定をハードコーディングしたくなかったので、H＄2, I＄2 や ＄H2, ＄H3 というセル参照で行っています。このときに ＄ が付いているのがポイントですね。ExcelやGoogleSpreadsheetでセル参照式が定義されたセルを別のセルに貼り付けると、参照指定が相対的に書き換えられた状態で貼り付けられます。しかし ＄ をつけていると、その指定は絶対参照となり、参照式をコピーして別セルに貼り付けても $ の箇所は書き変わらないのです。だから上記の計算式を記述したセルをコピーしてI3〜R12の範囲に貼り付けると、H＄2 - R＄2 や ＄H2 - ＄H12 に表記された縦横の目盛りを範囲指定の処理として汎用的に用いることができるわけです。

そして色の着色は条件付き書式のカラースケールを使っています。条件付き書式については難しいことは一つもないので細かい説明は省略します。実際に手を動かしていただくのが早い。

Kobito.Wm1vDB.png

BeaconFence の測位情報を実際に流し込む

BeaconFence では測位完了を onPositionEstimated で処理できます。

なので、このイベントに対して、Google Form への投げ込みの試験コードを流用すると、こんな感じのコードになります。

OnPositionEstimated
procedure TForm1.OnPositionEstimated(AEstimatedPoint, APointToPath: TPointF);
var
  HTTPRequest: String;
  HTTPRequestBase: String;
  QueryParameter: String;
begin
  // QueryParameter を除くベースのURL
  HTTPRequestBase := 'https://docs.google.com/forms/d/e/1FAIpQLSdKhibEYTHA7rvDcXovUH3Dsml-VIpIdLMMFPXL8_ew9YehwQ/formResponse';


  // QueryParameter を作る
  QueryParameter := Format(
    'entry.1625787623=%f&' +
    'entry.2136790809=%f&' +
    'entry.1971050741=%f&' +
    'entry.1076782688=%f',
    [
        AEstimatedPoint.X,
        AEstimatedPoint.Y,
        APointToPath.X,
        APointToPath.X
    ]
  );

  // QueryParameter だけを確認用として Memo1 に出力する
  Memo1.Lines.Insert(0, QueryParameter);

  // Google Form への投げ込みを行う
  HTTPRequest := HTTPRequestBase + '?' + QueryParameter;
  NetHTTPClient1.Get(HTTPRequest);
end;

なお、上記のコードでは Memo1 への書き出しも含んでいますので、これを実行すると前回の作業で配置した TMemo に確認用の情報が出力されます。

注意点

この方法は収集するレコード数が少ない場合には有効ですが、収集頻度が高かったり、あるいは収集レコード数が多くなると激重になるのであくまで内部実験に用いるのがよいようです。

またフォームがノーガードなので、だれでも値を投げ込むことができる点にも注意が必要です。このことが問題となるようでしたら、Google Apps の API を叩いて特定のシートの末尾行にデータを差し込むように実装すれば、今回説明した内容はそのまま使いまわせます。だけど、試作品レベルならばそこまでやることもないでしょう。

感想？

今回の Advent Calendar では「週刊DelphiでBeacon」的な感じで4週連続での続きネタを書きました。Beaconを使うアプリはハードウェアが必要なのでナカナカ作れないという印象があるかと思いますが、手持ちのデバイスをビーコンにしてしまえばよいので、実はその他のIoTデバイス利用に比べると開発のハードルは低いです。

ですのでDelphiやC++Builderをお持ちの方はぜひ一度試してみてくださいね。

なお、Delphi Advnet Calendar には初参加だったのに5つも投稿してしまったので、来年はもうちょっとセーブしつつ、もうちょっとDelphiやC++Builderの濃い話ができればよいなあと思います。

9
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
7