#概要
IoTのデータストアに何を使うかは悩ましいところです。リレーショナルデータベースは、枯れた技術ですし、フロントエンドの可視化ツールとの接続性や、いろいろなデータ型を扱える機能的なところでも有力な選択枝となっていると思います。リレーショナルデータベースとしてSQLServerを使い、IoTデータの分析を行ってみたいと思います。一般的な、IoTデータの分析で特徴的なものが２つあります。
1.時系列データの処理
2.地理的データの処理
時系列のデータは時間軸に沿って、ヒストグラムや平均をとる等の操作が多発します。また、GPSと連携させることも多いことから、地理的なデータも同時に扱っていく必要が生じます。
SQLServerのLinux上への構成に関してはこちらを参考にしてください。使用するデータは、速度、加速度センサとGPSを合わせたデータになります。東海道新幹線ののぞみで直接測定したデータになります。
GitHubにデータやクエリをまとめて公開しています。
#データの準備
下記でテーブルを作成します。
CREATE TABLE expresslog
(
dataID int IDENTITY(1,1)
,geo geography
,ts datetime
,velocity float
,x int
,y int
,z int
,rx int
,ry int
,rz int
) ON GeoFG;
x,y,zはそれぞれ、加速度、rx,ry,rzはそれぞれ角加速度になります。
地理的なデータはSQLServerの場合、geography型とgeometory型があります。geographyは球面上の座標データが扱えますので、GPSの位置情報を直接格納する場合に使います。特に距離を計算したりする場合、geographyを使用していないと、不正確になります。ただ、状況により、geography型では難しい処理も発生しますので、平面座標系のgeometoryに適時変換して利用します。地理的なデータにはインデックスを作成することが多くの場合必要ですが、クラスタ化インデックスが必要になるため、作成します。
-最初にdataIDにクラスタ化インデックスを作成します
-次にgeoにインデックスを作成します。
ALTER TABLE expresslog ADD CONSTRAINT expresslog_pk PRIMARY KEY (dataID)
CREATE SPATIAL INDEX sixd ON expresslog(geo)
USING GEOGRAPHY_GRID
WITH (GRIDS = (LOW, LOW, MEDIUM, HIGH),
CELLS_PER_OBJECT = 20)
データとデータロードのためのpythonコードはこちらになります。
あとは分析を実行していきます。Windowsのクライアントが必要になりますが、ここからはSQLServer Management Studioを導入くださいまた、グラフはExcel2016を利用しています。ExcelにはPowerQueryというクエリツールがついています。こちらを使うとワークシートに直接データを持ってくることができます。
#実際の分析
##最大速度の場所
select velocity,geo.STAsText() from expresslog
where velocity= (select max(velocity) from expresslog)
結果：279.13 POINT (136.54385 35.35615)
場所は以下の地図で表示できます。岐阜県で、279.13km/hとなりました。
##速度変化
測定データは時系列の一般的な処理として一定の間隔で、平均をとる等の処理を行う必要があります。SQLを使うと簡単に記述できます。
select datepart(hour,ts) as h,datepart(minute,ts) as m,avg(velocity)
from expresslog
group by datepart(hour,ts),datepart(minute,ts)
order by h,m
同じように距離を一定間隔にして、平均をとります。
DECLARE @Location geography
set @Location=geography::STGeomFromText('POINT(135.757800 34.985460)',4326)
select avg(velocity),floor(@Location.STDistance(geo)/5000)*5000 as distance from expresslog
where @Location.STDistance(geo) < 1000000
group by floor(@Location.STDistance(geo)/5000)
order by distance
##揺れの分析
加速度センサーのデータも取得しているので、速度 vs ｚ軸方向の加速度を出してみます。これは単純にデータを取得してグラフ表示いただけです。200km/hを境に揺れが大きくなっているのがわかります。
select velocity,z from expresslog
##通過する市町村の表示
最後は地理的なデータの特徴的な扱いです。地理的データの集まりを『図形』として処理できるので、複雑な処理でもクエリ一つに集約することができます。新幹線の軌跡を【線】、市町村のエリアを【平面図形】として扱っています。GPS座標を線として表現するにはLINESTRINGを使っています。SQL Server Management Studioでは、クエリ結果を図形として表示できます。【線】と交わる【平面図形】をクエリで取り出しています。図形のデータはこちらからダウンロードして使用しています。国土交通省の標準のデータからでも大丈夫と思います。また、ダウンロードしたデータをogr2ogrツールでデータベースに書き込みました
$ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=tcp:localhost,1433;database=GeoTest;uid=sa;Pwd=xxxxxxx;Driver=ODBC Driver 13 for SQL Server" "japan_ver81.shp" -progress
declare @l geometry ='LINESTRING(135.75827 34.98474,135.75828 34.77309,138.10189 34.77412,138.11296 34.77671,138.12294 34.77963,138.12907 34.78136,138.12907 ......省略..... )'
select @l
select ogr_fid,ogr_geometry from japan_ver81 where ogr_geometry.STIntersects(@l)=1
