新型コロナ感染箇所マップを作ってみる【FastAPI/PostGIS/deck.gl(React)】（データ加工編）

新型コロナ…感染拡大が止まらないですね…

駆け出しGIS屋としては何かしらのデータを地図上に乗せて可視化したいなーとは思っていたんですが、公開されてるデータって大抵がPDFなんで収集するのめっちゃめんどいやん…と尻込みしていました。

が！！！！！！

非営利目的であればデータの複製・引用・転載自由で素晴らしいデータを提供してくれているサイト様（[https://gis.jag-japan.com/covid19jp/](https://gis.jag-japan.com/covid19jp/)）を見つけたのでこのデータを使って地図上に表現していきたいと思います！！xf

今回は勉強がてらに多少冗長な構成（docker-composeでアプリケーションサーバーとDBサーバーを立ち上げて、わざわざGeoJSONをAPIで配信するなど）になっています。

# データの取得

早速データを見てみましょう！

上述のサイトにアクセスし、左上の`csv`のリンクをクリックします。

![スクリーンショット 2020-03-30 7.40.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/203944/7d6bfbfd-7fd1-1e60-d223-194c749dbb9d.png)

すると以下のようなcsvがダウンロードできます。

```COVID-19.csv
通し,厚労省NO,無症状病原体保有者,国内,チャーター便,年代,性別,確定日,発症日,受診都道府県,居住都道府県,居住管内,居住市区町村,キー,発表,都道府県内症例番号,ステータス,備考,ソース,ソース2,ソース3,人数,累計,前日比,死者合計,退院数累計,退院数,発症数,PCR検査実施人数,PCR検査前日比,職業_正誤確認用,勤務先_正誤確認用,Hospital Pref,Residential Pref,Release,Gender,X,Y,確定日YYYYMMDD,受診都道府県コード,居住都道府県コード,更新日時,Field2,Field4,Field5,Field6,Field7,Field8,Field9,Field10
1 ,1 ,,A-1,,30 ,男性,1/15/2020,1/3/2020,神奈川県,神奈川県,,,神奈川県,神奈川県,1,退院,,https://www.mhlw.go.jp/stf/newpage_08906.html,https://www.pref.kanagawa.jp/docs/ga4/bukanshi/occurrence.html,,1 ,1 ,1 ,0 ,1 ,1 ,0 ,,,,,Kanagawa,Kanagawa,Kanagawa Prefecture,Male,139.642347,35.447504,2020/1/15,14,14,3/29/2020 18:50,,,,,,,,
2 ,2 ,,A-2,,40 ,男性,1/24/2020,1/14/2020,東京都,中華人民共和国,,,中華人民共和国,東京都,1,退院,,https://www.mhlw.go.jp/stf/newpage_09079.html,https://www.metro.tokyo.lg.jp/tosei/hodohappyo/press/2020/01/24/20.html,,1 ,2 ,1 ,0 ,,,2 ,,,,,Tokyo,China(Mainland),Tokyo Metropolitan Government,Male,116.409685,39.903832,2020/1/24,13,NA,,,,,,,,,

```

csv最高っすね。

感染者数＝レコード数になっていて、かなり細かいデータを提供してくれておりますが、僕じゃ扱いきれないので利用したいデータのみに絞って加工していきましょう！

# データの加工の前に仮想環境を作成

ひとまず作業用のディレクトリとデータ加工作業用のディレクトリを作成していきましょう！(ディレクトリ名（今回はcovid_sample）は任意です)

以下のコマンドでcovid_sampleディレクトリとその下のscriptディレクトリを一気に作成できます。

```shell-session
$mkdir -p covid_sample/script
```

今回はとりあえず地図上にデータを表示させたいだけなので`["通し", "年代", "性別", "確定日", "発症日", "受診都道府県", "居住都道府県", "X", "Y"]`あたりだけ取り出してみましょう！

本体マシンの環境を汚したくないので`pipenv`などを利用して仮想環境を作成し、pandasを使ってサクッと編集していきます。

pipenvの利用方法などは[こちら](https://qiita.com/y-tsutsu/items/54c10e0b2c6b565c887a)の記事なんかがとても参考になると思います！

pipenvがインストールされている方は`$cd covid_sample/script`でscriptディレクトリを移動し以下のようなPipfileを作成しましょう。

```ini:Pipfile
[[source]]
name = "pypi"
url = "https://pypi.org/simple"
verify_ssl = true

[dev-packages]

[packages]
pandas = "==0.24.2"
requests = "*"

[requires]
python_version = "3.8"
```

上記のファイルはどのように仮想環境を作成するのかを指定するファイルで、上記の例だとPythonのバージョンは3.8でpandasとrequestsをインストールします。

`$pipenv install`コマンドで仮想環境を作成し、`$pipenv shell`で仮想環境に入ります。

すると、shellの左端に(script)のような文字が出てくると思いますが、これが出ていれば仮想環境に入れています。

この状態で`$pip list`でインストールされているライブラリを確認すると、pandasとrequestsおよびその依存関係がインストールされているのが確認できます。

```shell-session
(script) hogehoge:script$pip list
Package         Version   
--------------- ----------
certifi         2019.11.28
chardet         3.0.4     
idna            2.9       
numpy           1.18.2    
pandas          0.24.2    
pip             20.0.2    
python-dateutil 2.8.1     
pytz            2019.3    
requests        2.23.0    
setuptools      46.1.3    
six             1.14.0    
urllib3         1.25.8    
wheel           0.34.2    

```

これで仮想環境作成完了です！

# データの加工

以下のようなPythonのスクリプトを作成しましょう！名前は仮に`format.py`にしました！

このスクリプトでcsvのダウンロード・加工を一気にやってしまいます！

```format.py
import pandas as pd
from datetime import datetime as dt
import requests

# csvのURL
csv_url = "https://dl.dropboxusercontent.com/s/6mztoeb6xf78g5w/COVID-19.csv"

try:
    # urlを指定し、GETメソッドでhttpリクエスト
    r = requests.get(csv_url)
    # COVID-19.csvという名前で保存
    with open("COVID-19.csv", 'wb') as f:
        f.write(r.content)
except requests.exceptions.RequestException as err:
    print(err)

# 利用するカラムの名前を指定
column_names = [
    "通し", "年代", "性別", "確定日", "発症日",
    "受診都道府県", "居住都道府県", "X", "Y",
]

# カラム名を変更
changed_column_name = {
    "通し": "id",
    "年代": "age",
    "性別": "gender",
    "確定日": "fixed_data",
    "発症日": "onset_data",
    "受診都道府県": "consultation",
    "居住都道府県": "prefectures",
    "X": "lng",
    "Y": "lat",
}

# usecolsで利用するカラム名を配列形式指定
df = pd.read_csv('COVID-19.csv', usecols=column_names)
# 変更するカラム名を辞書形式で指定
rename_df = df.rename(columns=changed_column_name)

# ageカラムから指定した文字列を削除してリストを作成
rename_df["age"] = [string.strip(' ') for string in list(rename_df["age"])]
# 時刻をdate型に変換
rename_df["fixed_data"] = [dt.strptime(data_string, "%m/%d/%Y").date() for data_string in list(rename_df["fixed_data"])]
# onset_dataのNaNを置換
rename_df.fillna({'onset_data': '1/1/0001'}, inplace=True)
rename_df["onset_data"] = [dt.strptime(data_string, "%m/%d/%Y").date() for data_string in list(rename_df["onset_data"])]

# 指定カラムの指定値を置換
# inplace=Trueで元のdfを変更
rename_df.replace(
    {
        "age": {"不明": "999", "0-10": "10"}
    }
    , inplace=True)

# csvに書き出し
rename_df.to_csv("../docker/postgis/custom_COVID-19.csv", index=False)
```

このコマンドを実行するとcsvをダウンロードしてスクリプトと同様のディレクトリに格納し、その上で必要なデータを抜き出すなどの加工をして指定したディレクトリに吐き出します。

細かい話はコードのコメントに記載しているのでそっちを見てもらえれば理解できると思います！

csvの吐き出し箇所（`covid_sample/fastAPI/docker/postgis/custom_COVID-19.csv`）は、事項で説明するdocker環境の構築で作成する新しいディレクトリを指定し、dockerの設定でも同様のディレクトリを指定しているのでスクリプトを実行する際にはご注意ください！

# docker-composeでの環境構築

csvを加工し終わったら一度、`control + D`を押して仮想環境から抜け、`cd ../`でルートディレクトリ（covid_sample）に戻ってください。

データをGeoJSONというwebで扱いやすい地理空間情報の形式に変換しましょう！

手法はなんでもいいのですが、将来的にデータはAPIとしてガチャガチャやりたいのでDBに登録し、バックエンドも使ってAPIサーバーを立ち上げていきましょう！

その際には個人でのサーバーの立ち上げに非常に便利なDockerを利用していきます！

インストールされていることを前提に話を進めていくので、macをお使いの方は[Docker Compose - インストール](https://qiita.com/zembutsu/items/dd2209a663cae37dfa81)などを参考にしてDockerとdocker-composeをインストールしてください。（他のOSの方でもググればすぐに出てきます！）

作成したら次のようにAPI用のディレクトリを作成していきます！

```shell-session
$mkdir -p docker/fastapi
$mkdir -p docker/postgis/init
$mkdir docker/postgis/sql
```

現在のディレクトリ構成はこんな感じになっているはずです！

```shell
covid_sample
├── docker
│   ├── fastapi
│   └── postgis
│       ├── init
│       └── sql
└── script
    ├── COVID-19.csv
    ├── Pipfile
    ├── Pipfile.lock
    └── format.py
```

その他、dockerの起動に必要なファイルが結構あるので以下に雑な感じで記載しているのでコピペして使ってくださいね！笑

- docker-compose用のメイン設定ファイル：立ち上げるコンテナやコンテナ間の接続設定・ホストマシンとのマウントディレクトリの設定を行う

```docker/docker-compose.yml
version: '3.7'
services:
    fastapi:
#        コンテナ名
        container_name: fastapi
#        ビルドするdockerファイルが格納されたディレクトリ
        build: fastapi
        volumes:
#            マウントするディレクトリ
            - ./fastapi:/usr/src/app/
        ports:
#            ホスト側のポート：コンテナ側のポート
            - 8000:8000
        env_file:
#            環境変数に設定するファイル
            - fastapi/.env
        depends_on:
#            接続するサービス
            - postgis

    postgis:
        container_name: postgis
        build: postgis
        volumes:
            - covid_postgis_data:/var/lib/postgresql/data
#            down -vなどでボリュームがない時などを含めた初回起動時に実行されるファイルを指定
            - ./postgis/init:/docker-entrypoint-initdb.d
#            マウントするディレクトリ
            - ./postgis:/home
        env_file: postgis/.env_db
        ports:
#            ホスト側のポートはローカルのpsqlとバッティングするので5432以外の方が良さそう
            - 5433:5432

volumes:
    covid_postgis_data:
```

- fastapiコンテナ用の環境変数設定ファイル

```docker/fastapi/.env
DATABASE_HOST=localhost
DATABASE_PORT=5433
```

- fastapiコンテナ起動用Dockerfile（docker/fastapi/Dockerfile）

```docker:docker/fastapi/Dockerfile
FROM python:3.8

RUN apt-get update -y --fix-missing \
    && apt-get install -y -q --no-install-recommends

# install
RUN pip install pipenv
ADD Pipfile Pipfile.lock /
RUN pipenv install --system

# add to application
ADD app.py /

CMD ["uvicorn", "app:app", "--host", "0.0.0.0", "--port", "8000"]
```

- fastapiコンテナでインストールするPythonモジュール（docker/fastapi/Pipfile）

```ini:docker/fastapi/Pipfile
[[source]]
name = "pypi"
url = "https://pypi.org/simple"
verify_ssl = true

[dev-packages]

[packages]
fastapi = "*"
uvicorn = "*"
psycopg2-binary = "*"

[requires]
python_version = "3.8.0"
```

- fastapi用のアプリケーションファイル

```docker/fastapi/app.py
from fastapi import FastAPI

app = FastAPI()

# `uvicorn app:app --reload`で起動
@app.get("/")
async def hello():
    return {"text": "hello"}
```

- postgisコンテナの環境変数設定ファイル

`docker/postgis/.env_db`のパスワードは任意のものを指定してください。

```docker/postgis/.env_db
#postgresコンテナではenvに書いておけば自動でDBが作成される
POSTGRES_USER=covid_user
POSTGRES_PASSWORD=hogehoge
POSTGRES_DB=covid_db
```

- postgisコンテナ起動用Dockerfile(docker/postgis/Dockerfile)

```docker:docker/postgis/Dockerfile
FROM mdillon/postgis:9.6

# locale settings.
RUN localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8
ENV LANG ja_JP.UT
```

- postgisコンテナ初回起動時に読み込まれるシェルスクリプト

```docker/postgis/init/init.sh
#!/bin/sh

psql -U covid_user -d covid_db -f /home/sql/init.sql

psql -U covid_user -d covid_db -c "COPY covid_data FROM '/home/custom_COVID-19.csv' WITH CSV HEADER DELIMITER ',';"

psql -U covid_user -d covid_db -f /home/sql/create_geom.sql
```

- 上記シェルスクリプトで実行されるsqlファイル

```sql:docker/postgis/sql/init.sql
CREATE EXTENSION postgis;

create table covid_data
(
    id           smallint not null,
    age          int,
    gender       text,
    fixed_data   date,
    onset_data   date,
    consultation text,
    prefectures  text,
    lng          float,
    lat          float
);
```

- 上記シェルスクリプトで実行されるsqlファイル

```sql:docker/postgis/sql/create_geom.sql
alter table covid_data
	add geom geometry(point, 4326);

UPDATE covid_data SET geom = ST_GeogFromText('SRID=4326;POINT(' || lng || ' ' || lat || ')')::GEOMETRY;
```

上記のファイルとスクリプトで加工したcsvが用意できたら`$docker-compose up -d --build`でビルドして起動しましょう！

今回はPython用のマイクロWebフレームワークの`FastAPI`とPostgreSQLの地理空間拡張である`PostGIS`のコンテナを立ち上げています。

コンテナ起動時にはcsvのデータがDBに登録され、init.shによりそのデータからgeometry型のgeomカラムが自動生成されデータが投入されます。

この段階で何かしらの手段（pgadminや`$psql -U covid_user -d covid_db -h localhost -p 5433`など）postgisコンテナ内のDBに接続するとデータが確認できるはずです。

また、ブラウザから`localhost:8000`にアクセスして`{"text": "hello"}`の表示が返ってくればdocker-compose環境構築の完了です！

# APIの作成

環境ができたらAPIを作成しましょう！

具体的には`docker/fastapi/app.py`を以下のように編集していきます！

```docker/fastapi/app.py
from fastapi import FastAPI
import psycopg2
from starlette.middleware.cors import CORSMiddleware

app = FastAPI()

# CORSの設定のために追加
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"]
)

# `uvicorn app:app --reload`で起動
@app.get("/")
async def hello():
    # postgreSQLに接続
    connection = psycopg2.connect(
        # host="localhost",
        host="postgis",
        user="covid_user",
        password="hogehoge",
        dbname="covid_db",
        port=5432
    )

    # クライアントプログラムのエンコードを設定（DBの文字コードから自動変換してくれる）
    connection.set_client_encoding('utf-8')

    # カーソルの取得
    cursor = connection.cursor()

    # geojsonを生成
    sql = """
    SELECT jsonb_build_object(
        'type',     'FeatureCollection',
        'features', jsonb_agg(features.feature)
    )
    FROM (
      SELECT jsonb_build_object(
        'type',       'Feature',
        'id',         id,
        'geometry',   ST_AsGeoJSON(geom)::jsonb,
        'properties', to_jsonb(inputs) - 'gid' - 'geom'
      ) AS feature
      FROM (SELECT * FROM covid_data) inputs) features;
    """

    # SQL実行
    cursor.execute(sql)

    # 取得結果を出力
    results = cursor.fetchall()[0][0]

    # カーソルをとじる
    cursor.close()

    # 切断
    connection.close()

    return results
```

このアプリケーションではまず、ReactからAjaxでAPIにアクセスしてデータを取得・表示させる必要があるのでCORSのための設定をしています（今回はお試しとして全てのホストやメソッドを許可していますが、危ないのでなるべくやめてください）。

設定しないとJavaScript側（地図表示用のアプリケーション）からAPIにアクセスできないので必ず設定していきましょう！（詳しくは`CORS`でググってみてね！）

`@app.get("/")`の部分ではGETメソッドで/へアクセスがあった場合の処理をその下に書いていく…という意味です。今回はここに処理を書きましょう。

その後、`psycopg2`というPythonからPostgreSQLに接続するためのモジュールを使ってDBにアクセスしていきます。

SQLの部分ではPostgreSQLのJSONB型の機能を利用してデータを無理やりGeoJSONに変換しています。

書き換え後に`$docker-compose down`でコンテナ群を停止、`$docker-compose up -d --build`で再起動していきましょう！

起動が完了したら再度`localhost:8000`に接続してみましょう。

うまくいっていれば以下のようなGeoJSONが返ってくるはずです！

```json
{
    "type": "FeatureCollection",
    "features": [
        {
            "id": 1,
            "type": "Feature",
            "geometry": {
                "type": "Point",
                "coordinates": [
                    139.642347,
                    35.447504
                ]
            },
            "properties": {
                "id": 1,
                "age": 30,
                "lat": 35.447504,
                "lng": 139.642347,
                "gender": "男性",
                "fixed_data": "2020-01-15",
                "onset_data": "2020-01-03",
                "prefectures": "神奈川県",
                "consultation": "神奈川県"
            }
        },
        {
            "id": 2,
            "type": "Feature",
            "geometry": {
                "type": "Point",
                "coordinates": [
                    116.409685,
                    39.903832
                ]
            },
            "properties": {
                "id": 2,
                "age": 40,
                "lat": 39.903832,
                "lng": 116.409685,
                "gender": "男性",
                "fixed_data": "2020-01-24",
                "onset_data": "2020-01-14",
                "prefectures": "中華人民共和国",
                "consultation": "東京都"
            }
        },
        ...
}
```

これでデータの準備が出来ましたね！！

次回はこのデータを地図上に表示させていきましょう！

→続きはこちら：[新型コロナ感染箇所マップを作ってみる【FastAPI/PostGIS/deck.gl(React)】（データ表示編）](https://qiita.com/nokonoko_1203/items/23ee1ffb6880f6acdda9)