新型コロナの感染状況(2020/4/18)

前回の記事では、新型コロナの感染状況データをPythonでスクレイピングしてOpenJUMP上の地図にグラフ表示しました。
今回は、データベース（PostGIS）を使うことで、もう一歩自動化を進めます。

今回実現する仕組み

前回、WHOの感染者数などのデータをPythonでスクレイピングし、OpenJUMPの属性結合を使ってグラフ表示しましたが、この方式は手作業が多いと感じていました。具体的には、地図データの読み込み→（今週であれば）3週間分のWHOデータを属性結合→グラフ表示ですので、いずれ限界となりそうです。
そこで今回は、スクレイピングしたデータをPostGISへ取り込みSQLで結合し手作業を減らしたいと思います。
今回実現する仕組みを以下に図示します。①のWHOサイトからのCSV出力は前回実現していますので、②、③が中心となります。

不要な属性の削除

今回使用する世界地図（naturalearth）は、属性のカラム数が多いので、使わないカラムを削除します。
この作業は、QGISにて行います。属性テーブルを開き、編集ボタン（下図左赤枠のアイコン）を押下して編集モードに遷移し、属性削除ボタン（下図右赤枠のアイコン）を押下します。

表示される”フィールドの削除”ダイアログから削除するカラムを指定して”OK”ボタンを押下します。問題なければ編集ボタンを再度押下し表示モードに遷移すると属性が削除された状態でシェープファイルが保存されます。

今回は、下図のように10項目としたシェープファイルを使って作業を進めます。

PostGISへのインポート

編集したシェープファイルを、”shp2pgsql-gui”ユーティリティを使ってPostGISへ取り込みます。
”shp2pgsql-gui”のインストールや取り込みの詳細は、以下の記事を参考にしてください。

[blogcard url=”https://takamoto.biz/gis/postgis1/”]

”shp2pgsql-gui”ユーティリティを起動し、PostGISへ接続後、先ほど編集したシェープファイルを指定して”Import”ボタンを押下すると、取り込みが行われます。

数分で取り込みが終了します。OpenJUMPでPostGISから地図表示すると、以下のようになります。

WHOデータをPostGISへインポート

PythonでスクレイピングしたWHOデータ（CSV）をPostGISへインポートします。
今回は、週ごとにテーブルを分ける方法としました。
Windowsへのpsqlの導入方法（pgadmin4のオマケとして導入）と、Pythonによるスクレイピングの詳細は以下の記事を参考にしてください。

[blogcard url=”https://takamoto.biz/gis/qgis17/”]

[blogcard url=”https://takamoto.biz/gis/openjump5/”]

WHOデータをインポートするテーブルの作成は、psqlなどから以下のSQLを発行します。

（4月18日のデータ収容テーブルの作成SQL）
create table who0418(
Country varchar(50),
TCases numeric,
NCases numeric,
TDeaths numeric,
NDeaths numeric,
TRecovered numeric,
ACases numeric,
Critical numeric,
TC1Mpop numeric,
D1Mpop numeric,
TTests numeric,
T1Mpop numeric,
Continent varchar(50)
);

作成したテーブルにCSVファイルのデータを取り込むには、psqlのcopyコマンドを使うとよいでしょう。コマンドのイメージは以下になります。

postgis=# \copy who0418 from ‘D:/data/who/stat418.csv’ with csv header
COPY 212

psqlでデータを確認すると、以下のようになります。うまく取り込めました。

同様にして、3週間分のWHOデータをPostGISへ読み込みました。

postgis=# \dt who*
List of relations
Schema | Name | Type | Owner
——–+———+——-+———-
public | who0404 | table | takamoto
public | who0411 | table | takamoto
public | who0418 | table | takamoto
(3 rows)

OpenJUMPを使ってグラフ表示

ここからが本題のグラフ表示です。
3週間分のグラフをOpenJUMPのDB Query（SQL）を使って表示します。
地図と発行SQLの両方を見ていきます。

１.感染者数（総数）
感染者数は、アメリカの伸びが際立っていることに変化はないようです。（棒グラフの赤が先々週まで、緑が先週まで、青が今週までの合計です。）

発行したSQLは、世界地図に3週間分のWHOデータ（3テーブル）を外部結合しています。

（発行SQL）
select na.name_en en_name,na.name_ja ja_name,w1.tcases w1,w2.tcases w2,w3.tcases w3,na.geom geom
from naturalearth na
left outer join who0404 w1 on (na.name_en=w1.country)
left outer join who0411 w2 on (na.name_en=w2.country)
left outer join who0418 w3 on (na.name_en=w3.country);

2.100万人あたりの感染者数
次に、100万人あたりの感染者数を確認すると、前回同様、ヨーロッパ中心部から周辺諸国へ広がっているように見えます。
特に中東、南米の北部が増加傾向にあるように見えます。

（発行SQL）
select na.name_en en_name,na.name_ja ja_name,w1.tc1mpop w1,w2.tc1mpop w2,w3.tc1mpop w3,na.geom geom
from naturalearth na
left outer join who0404 w1 on (na.name_en=w1.country)
left outer join who0411 w2 on (na.name_en=w2.country)
left outer join who0418 w3 on (na.name_en=w3.country);

3.100万人あたりの死亡者数
次に、100万人あたりの死亡者数を確認しましょう。
ヨーロッパ、北米を中心に見ると、アメリカとカナダの伸びが著しいようです。

ヨーロッパ中心部にフォーカスすると、ベルギーの伸びが圧倒的です。
メディアでは報道されていないように思いますが、現地では大変なことになっているのでしょう。

中東では、イランやトルコ、イスラエルが多くなっています。

（発行SQL）
select na.name_en en_name,na.name_ja ja_name,w1.d1mpop w1,w2.d1mpop w2,w3.d1mpop w3,na.geom geom
from naturalearth na
left outer join who0404 w1 on (na.name_en=w1.country)
left outer join who0411 w2 on (na.name_en=w2.country)
left outer join who0418 w3 on (na.name_en=w3.country);

4.100万人あたりの検査数
最後に我が国周辺の100万人あたりの検査数を確認しましょう。
SQL的には、where句にて”Asia”を条件に絞り込んでいます。
韓国は検査数を増やす戦略、台湾はおそらく水際で防ぐ戦略だったのだと思います。

（発行SQL）
select na.name_en en_name,na.name_ja ja_name,w1.t1mpop w1,w2.t1mpop w2,w3.t1mpop w3,na.geom geom
from naturalearth na
left outer join who0404 w1 on (na.name_en=w1.country)
left outer join who0411 w2 on (na.name_en=w2.country)
left outer join who0418 w3 on (na.name_en=w3.country)
where na.region_un=’Asia’;

まとめ

今回は、以下を試してみました。
1.世界地図とスクレイピングしたデータのPostGISへの取り込み
2.OpenJUMPによるグラフ表示（先週までとの比較）

グラフを見ていると、メディアで報道されていないことがたくさんあるように感じます。例えば、ベルギーの100万人あたりの死亡者が先週急増していることは、あまり報道されていないように思います。
ネットで調べると地方新聞に簡単な記事があった程度です。

[blogcard url=”https://www.kyoto-np.co.jp/articles/-/222671″]

また、北米と南米の北部、中東で感染者数や死亡者数が増えているということは、ヨーロッパから他の地域へ感染が広つつあることが想像できます。
ウィルスに国境はありませんので、特効薬やワクチンがない現状を踏まえると、少なくとも秋口までは人の動きが制限される状況が続きそうだと感じています。