AmazonTimestreamの位置情報データをGrafana　Geomapで表示する

# はじめに
SORACOMのGPSマルチユニットをユーザーガイドに従って操作するとLagoon上に簡単に地図上に表示できます

https://users.soracom.io/ja-jp/guides/iot-devices/gps-multiunit/

お手軽で良いのですがLagoonのベースとなっているGrafanaではより多くの機能があり
例えば航空写真をベースマップにしたいと思った時には本家のGrafanaを使いたくなります
そのためにはまずデータを蓄積する時系列データベースが必要です

そこで下記記事を見てsoracom Funnel を利用してAWS Timestreamへ送信する方法を知ったのでそこから先のGrafanaへの連携方法を書きたいと思います

# AWS Timestream にデータを蓄積

SORACOM GPSマルチユニットで取得した情報をAWS Timestreamへ送信させる

https://qiita.com/ckw-1227/items/82f3bc0d6c2e689411a5

この記事の通りにするとsoracom Funnel -> AWS IoT core -> AWS Timestream と送信できます

# Grafana で可視化
この記事を参考に進めていきます

Amazon TimestreamのデータをGrafanaで可視化してみた

https://dev.classmethod.jp/articles/timestream-grafana-visualize/


Grafana インストール

https://grafana.com/docs/grafana/latest/

Grafanaをインストール方法はいつくかあり
・Grafana Cloud で
・ローカルPCに（例えばDockerで）
・自宅ネットワーク内サーバーに（例えばラズパイに）
・クラウド上のサーバーに（例えばAWS　EC2に）
自宅PCだけ見るのか、外出先のスマホでも見たいのか使用用途にあったものを選びます。
今回は手軽なGrafana Cloud を使ってみます

アカウントを作り
はじめにAmazon Timestream用のプラグインを追加します

https://grafana.com/grafana/plugins/grafana-timestream-datasource/?tab=installation

ログイン後左下の歯車 **[Configuration]-[Data sources] Amazon Timestream** を検索して追加

![スクリーンショット 2023-01-31 125604.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/261581/3577aa3f-ee17-8668-242a-645dd7b91092.png)

![スクリーンショット 2023-01-31 125657.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/261581/36a4c41e-2bc1-14b9-b41b-7fcdc930aaec.png)


設定画面
![スクリーンショット 2023-01-31 154514.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/261581/8dbbc1b9-3060-de42-019b-6dbb7e8a7047.png)


Connection Details
・ **Authentication Provider**　Access & secret key

・ **Access Key ID**
・ **Secret Access Key**
ここを参考に　IAM　から　AccessKeyを入手し入力

https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html?icmpid=docs_iam_console#Using_CreateAccessKey


・ **Assume Role**
・ **External ID**
オプション。今回は省略

・ **Endpoint**　
・ **Default Region**

エンドポイントを作成します

https://docs.aws.amazon.com/timestream/latest/developerguide/VPCEndpoints.vpc-endpoint-create.html

リージョンは東京（ap-northeast-1）の場合
AWS-CLIで以下を入力します
ローカルでAWS-CLIの環境がない場合はAWS CloudShellがお手軽です
``` bash
aws timestream-write describe-endpoints --region ap-northeast-1
```
返ってきた
```bash
{
    "Endpoints": [
        {
            "Address": "ingest-cell1.timestream.ap-northeast-1.amazonaws.com",
            "CachePeriodInMinutes": 1440
        }
    ]
}
```
ここのcell1 のところをメモして(数字は変わります）

```bash
https://query-{cell}.timestream.{region}.amazonaws.com
```
に埋め込みます。この例では
```bash
https://query-cell1.timestream.ap-northeast-1.amazonaws.com 
```
がエンドポイントになります

**Save&Test** を押して *Connection success* が出れば成功
試しに **Explore** を押して中身を見てみます
- Database　- Table　を選択して
上位10のクエリを実行。　\\$__database　 \\$__table はマクロでデフォルトの名前が入ります
```SQL
SELECT * FROM $__database.$__table LIMIT 10

```
| simId  | timestamp  |  measure_name | time  | measure_value::double  |
|:-:|:-:|:-:|:-:|:-:|
|8981100001234567890|1675076917047|humi|2023-01-30 20:08:37|56.2|
|8981100001234567890|1675076917047|lat|2023-01-30 20:08:37|35.0|
|8981100001234567890|1675076917047|lon|2023-01-30 20:08:37|137|
|8981100001234567890|1675076917047|temp|2023-01-30 20:08:37|22|

これを位置情報として利用するために下のようなテーブルに変換します

|timestamp|simId|latitude|longitude|temp|humi|
|:-:|:-:|:-:|:-:|:-:|:-:|
|1675140696976|8981100001234567890|35.0|137|14.2|31.7|
|1675140072037|8981100001234567890|35.0|137|15.8|30.2|
|1675139471888|8981100001234567890|35.0|137|16.9|31.9|


内容は省略しますが　~~拾ってきてコピペしただけなので~~　 結論から言うと以下のクエリを実行します

https://stackoverflow.com/questions/72228109/how-to-group-data-with-sql

```SQL
SELECT 
timestamp, simId,
MAX(CASE WHEN measure_name = 'lat' THEN measure_value::double END) AS latitude ,
MAX(CASE WHEN measure_name = 'lon' THEN measure_value::double END) AS longitude,
MAX(CASE WHEN measure_name = 'temp' THEN measure_value::double END) AS temp,
MAX(CASE WHEN measure_name = 'humi' THEN measure_value::double END) AS humi
FROM $__database.$__table
GROUP BY timestamp,simId
ORDER BY timestamp DESC
LIMIT 10
```

# Geomapで表示
では早速ダッシュボードを作ります
- Add panel


[Query]
**Data source**　でAmazon　timestream　を選んで先程のクエリを入力

ここでtimestampを日時表示に変え、また一部のデータが欠損してることがあるので除外します
[Transform]
![スクリーンショット 2023-01-31 160319.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/261581/b36ae4c1-ed8e-566f-54ac-fe3c78652ca5.png)

- Convert field type
Field　timestamp　as　Numeric
Field　timestamp　as　Time


- Filter data by values
Filter type　= Include
Conditions　= Match all
Field latitude Match Is not null Value
Field longitude Match Is not null Value

右上のGeomapを選択
![スクリーンショット 2023-01-31 160409.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/261581/40c3ef2f-ce61-56da-18e4-bf90d3f7ff46.png)


### Basemap layer
![スクリーンショット 2023-01-31 160507.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/261581/5daddfdf-0606-ed87-2f26-ace3c2409dcf.png)

-XYZ　Tile　Layout
--URL template
```
https://cyberjapandata.gsi.go.jp/xyz/seamlessphoto/{z}/{x}/{y}.jpg
```

国土地理院がXYZタイルを提供しており出典を表示すれば無償で商用でも利用できます

https://maps.gsi.go.jp/development/ichiran.html

### Map layers
![スクリーンショット 2023-01-31 160540.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/261581/4248bf2d-6327-5969-e87c-c4f7318095ef.png)

**Layer type** *Markers*
**Data** *Query: A*
**Location Mode** *Auto*
今回はカラム名を latitude, longitude　にしてあるので自動で合わせてくれます
**Symbol-Color**
 Temp を選択
**Text label**
Source= Field
Field = temp
X offset 20

**Standard options**
Color scheme = Blue-Yellow-Red(bu Value)

その他タイトル名や単位等を修飾するとこのような地図が出来上がります
（GPSの誤差が結構あるな・・・）
![スクリーンショット 2023-01-31 161410.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/261581/a8779014-bf8f-c672-c605-d6781fd80ad0.png)

以上です。なおこれはSORACOMとAWSの有料サービスを利用しているのでテストが終わったら片付けておきましょう。



### 独自のgeojsonファイルを表示する　（2023/3/8追記）
　自分で作成したgeojsonファイルのポリゴンをマップレイヤーに追加するにはlinuxにインストールしたgrafanaの場合以下のディレクトリにファイルを置くことで

```
/usr/share/grafana/public/maps/ 
```
Layer Type > geojson
Geojson URL > ドロップダウンリストに表示されます

![スクリーンショット 2023-03-08 174856.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/261581/8e95e646-affa-a051-2f32-e2dd14f34447.png)

なおgrafanaCloudではこの方法ではできません

Filesystem access for grafana cloud
https://community.grafana.com/t/filesystem-access-for-grafana-cloud/82430/2

### soracom garafana v3 でGeomapが使えるようになりました（2023.4.21追記）

https://users.soracom.io/ja-jp/docs/lagoon-v3/versions/#panel

なのでAmazon　Timestream を使わなくても地理院地図を背景にすることができるようになりました