国土交通省のGISデータをBigQueryにポリゴンとしてデータ投入する

# 概要
国土交通省の GIS データを BigQuery に投入する方法を記載する

# 用途
緯度経度に情報を付与したい場合に使う
(都道府県、市区町村、各種統計情報)

# 参考
参考にさせて頂いた記事を記載させて頂きます

- https://qiita.com/takahi/items/f141f3c596788d612fe8
- https://qiita.com/takahi/items/9d44966b10d6257d8b2d
- https://qiita.com/A-Kira/items/3339e902e7a8fca8fdf6

# おおまかな作業手順
1. 国土交通省から地理情報をダウンロード
2. QGISをインストール
3. pdAdminをインストール & postgres(Docker)を起動する
4. QGISを利用してシェイプファイルを読み込む & 読み込んだシェイプファイルをpostgresにインポート
5. postgresからBigQueryが読み込める形でポリゴンデータをエクスポート
6. BigQueryにポリゴンデータをインポート
7. BigQuery上でテーブルを整形する

# 作業手順
## 1. 国土交通省から地理情報をダウンロード
https://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N03-v2_3.html#prefecture00 より
全国の最新の地理情報をダウンロードする
※ 今現在だと「N03-190101_GML.zip」というファイル名がダウンロードされます

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/9bc995c0-5dc7-683a-8ab6-ea3c8273b590.png)

## 2. QGISをインストール
https://qgis.org/ja/site/forusers/download.html より
長期リリース版をダウンロード
※ 時間が掛かるのでその間に3の手順を進める
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/58609881-b050-8459-27bb-1174cbe5f461.png)

QGIS起動
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/795923bb-b8d2-2911-f225-c4cd456a320b.png)

## 3. pdAdminをインストール & postgres(Docker)を起動する
postgres に PostGIS というextension を入れる必要があるが、
ローカルでやると環境が汚れる & 環境構築ではまる可能性大なので docker 推奨

### docker-compose の設定 と pgAdmin のインストール
https://qiita.com/A-Kira/items/3339e902e7a8fca8fdf6 より
docker-compose の設定および pgAdmin をインストールする

こういったフォルダ構成になります
※ 「1.国土交通省から地理情報をダウンロード」で取得したファイルを解凍して配置する(N03-190101_GML)
※ japan_city_polygon.csv はBQ投入前のポリゴン情報(今回作成するものです)
※ japan_city_polygon.json はBQテーブル定義(記事後半にjson定義を記載しています)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/2d1a1bdd-201f-019b-aeb0-315e80e6b310.png)

### docker-compose up -d
```
$ docker-compose up -d
$ sh ./init-db.sh

NOTICE: extension "postgis" already exists, skipping
CREATE EXTENSION
DROP TABLE
CREATE TABLE
INSERT 0 1
```

### pdAdmin

docker-compose.yml に記載してある通りに入力する

> Host: 127.0.0.1
> Port: 5432
> database: postgre
> Username: docker
> Password: docker

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/4f51ddd3-e582-5afe-7a9e-183700ac76c9.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/537c67ad-220e-39c5-f340-6c1bd6a8eecf.png)

## 4. QGISを利用してシェイプファイルを読み込む & 読み込んだシェイプファイルをpostgresにインポート
### シェイプファイルの読み込み
「N03-19_190101.shp」をクリックすると日本地図が展開される

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/cf13c198-62ee-15ea-8660-e6e6b6a3af45.png)

### postgresとの接続
PostGISを選択し、接続の新規作成

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/07ec728b-cc46-0184-ea34-bd8eb09a5bb3.png)

docker-compose.yml に記載してある通りに入力する

> 名前: 任意
> Host: 127.0.0.1
> Port: 5432
> database: postgre
> Username: docker
> Password: docker

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/5c1de79b-34b8-b9e1-6056-a07d1d1bb12d.png)

### シェイプファイルをDBに投入する
このままDBに投入すると都道府県や市区町村が文字化けしてしまうのでエンコードを設定する
レイヤ部分のN03-19-190101を右クリックからプロパティを選択する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/27cb89bc-e9c6-e42b-92cb-c005eed6e96a.png)

エンコーディングをShift_JISにする

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/8a4fa8ae-c987-c245-68c6-382757f27c0e.png)

データベース > DBマネージャーから

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/2cb29537-155b-18c8-0e9d-4444dbbb11b1.png)

PostGIS > 設定したDB名 > public > レイヤ/ファイルのインポート より

> 主キーにチェック
> ジオメトリカラムにチェック
> 変換後SRIDにチェック

してOK(少し時間が掛かる)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/d31ac22f-0ff2-39d7-f9fa-d183ef43561a.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/0000b2a2-7001-fb21-a9b5-bb63b9dc4f54.png)

## 5. postgresからBigQueryが読み込める形でポリゴンデータをエクスポート
### pgAdminでテーブルを読み込んで見る(確認)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/a1b1e287-a9d7-54d1-065f-020421d321c9.png)

### geometory 型をWKTに変換するためにCREATE〜SELECTで新規テーブルを作成する

```
CREATE TABLE "N03-19_190101_transform" AS
SELECT
    　id     　-- 行ID
    , n03_001 -- 都道府県名
    , n03_002 -- 支庁・振興局名
    , n03_003 -- 郡・政令都市名
    , n03_004 -- 市区町村名
    , n03_007 -- 行政区域コード
    , ST_AsText(geom) AS wkt --WKTへ変換した空間情報
FROM public."N03-19_190101"
```

### 作成した N03-19_190101_transform テーブルをcsvエクスポートする


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/a0e68df6-21d3-77b9-2dd5-9d60a3c1893e.png)

「japan_city_polygon.csv」というファイル名でエクスポートする

![スクリーンショット 2020-09-02 18.47.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/cfc59a82-a22b-2143-7119-b587a3f78d75.png)

### csvの確認
※ 画像は2回目なのでファイル名が違います

```
$ less japan_city_polygon.csv
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/273970/ba4dadac-7b42-4426-493d-64e80651e836.png)


## 6. BigQueryにポリゴンデータをインポート
### テーブル定義(json)を作成
```japan_city_polygon.json
[
  {
    "mode": "NULLABLE",
    "name": "id",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "n03_001",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "n03_002",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "n03_003",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "n03_004",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "n03_007",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "wkt",
    "type": "STRING"
  }
]
```

### csvをBigQueryにロードする
```
bq load --source_format=CSV \
--skip_leading_rows=1 \
projectId:datasetId.japan_city_polygon_org \
japan_city_polygon.csv \
japan_city_polygon.json
```

## 7. BigQuery上で整形する
※ クエリ結果から `projectId:datasetId.japan_city_polygon` を作成
※ ST_GEOGFROMTEXT(wkt) でポリゴンに変換している

```
SELECT
  id,
  n03_001 AS prefecture,
  n03_002 AS branch_office,
  n03_003 AS designated_city,
  n03_004 AS city,
  n03_007 AS administrative_division,
  ST_GEOGFROMTEXT(wkt) AS polygon
FROM `jtx-dwh.dtm_geo.japan_city_polygon_org`
```