概要

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

用途

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

参考

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

おおまかな作業手順

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

作業手順

1. 国土交通省から地理情報をダウンロード

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

2. QGISをインストール

https://qgis.org/ja/site/forusers/download.html より
長期リリース版をダウンロード
※ 時間が掛かるのでその間に3の手順を進める

QGIS起動

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定義を記載しています)

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

4. QGISを利用してシェイプファイルを読み込む & 読み込んだシェイプファイルをpostgresにインポート

シェイプファイルの読み込み

「N03-19_190101.shp」をクリックすると日本地図が展開される

postgresとの接続

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

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

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

シェイプファイルをDBに投入する

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

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

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

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

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

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

5. postgresからBigQueryが読み込める形でポリゴンデータをエクスポート

pgAdminでテーブルを読み込んで見る(確認)

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エクスポートする

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

csvの確認

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

$ less japan_city_polygon.csv

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`