GIS実習オープン教材の学習（その17）
今回は、第10回空間データベースを行います。
参考URLは、以下になります。
https://gis-oer.github.io/gitbook/book/materials/tasks/t_09.html
課題の内容
今回の課題を以下に示します。
空間データベースを作成し、東京23区ごとの駅密度を計算してください。計算結果の出力が完了したら、配色を調整した地図をレイアウトしてください。
第10回・空間データの分析課題· GIS実習オープン教材
今回の課題は、PostGISをインストールし、東京23区の行政界と駅の点データを読み込んで駅密度を計算するというものです。
ここまでの学習では、QGISにシェープファイルやCSVファイルを読み込んで解析していましたが、今回はリレーショナルデータベース（RDBMS）に読み込んで解析を行います。
RDBMS上での空間データの取り扱い
PostGISとは、RDBMSであるPostgreSQLにて空間データを取り扱うための拡張コンポーネントです。具体的には、ポリゴンなどのベクトルデータを特殊カラム（GEOMETRY型）に収容し、座標系の付与や幾何計算などを実現しSQLで利用可能とします。
このようなRDBMS上で空間データを取り扱う機能は、オラクル社がOracle7の拡張として提供したSpatial Data Optionが最初だったと思います。（1996年頃）
ISOのSQL99に合わせて仕様化されたSQL/MMにて空間情報が定義されてから各社のRDBMS上で実装が本格化し、現在ではほとんどのRDBMS製品で空間情報がサポートされています。PostGISも1.0版としてのリリースは2005年4月です。
課題の実施
それでは、課題に取り組みましょう。
今回は、以下の前提で作業します。
・PostgreSQL+PostGISはDockerイメージにて導入
・pgAdmin4とshp2pgsql-gui.はクライアントのみ単独で導入
手順は以下となります。
1.PostGISのDockerイメージの導入と起動
2.pgAdmin4の導入
3.shp2pgsql-guiの導入
4.シェープファイルのインポート
5.駅密度を求めるSQLの実行
6.結果をQGISへ取り込み色替え地図を表示
1.PostgreSQLのDockerイメージの導入と起動
今回は、Linuxサーバー（CentOS7）のDocker（正確にはdocker-compose）を使ってPostGIS環境を構築します。
PostgreSQLのホームとなるディレクトリを作成し、下記ファイル（docker-compose.yml）を作成します。
version: ‘3’
services:
db:
container_name: postgis
image: mdillon/postgis
environment:
POSTGRES_DB: postgis
POSTGRES_USER: takamoto
POSTGRES_PASSWORD: hogepwd
volumes:
- ./docker/db/data:/var/lib/postgresql/data
ports:
- XXXX:5432
ファイルの内容は、赤下線はイメージの取得先、黄下線はデータファイルを配置するディレクトリ指定（相対パス）、青下線はホスト側のポート番号（デフォルトから変更したい場合のみ設定）を指定しています。
ファイルを作成したら、下記コマンドを実行します。
$ docker-compose up -d
Pulling db (mdillon/postgis:)…
latest: Pulling from mdillon/postgis
f7e2b70d04ae: Pull complete
027ad848ac9c: Pull complete
7c040ef66643: Pull complete
b891079ad2eb: Pull complete
cb64a97e42d9: Pull complete
1b88625f7d89: Pull complete
a6ac0b663e77: Pull complete
594497f0a694: Pull complete
ca7201b6a21f: Pull complete
48cdfad3f2fd: Pull complete
912fb62e7390: Pull complete
1e6365c64609: Pull complete
eda829b73ec7: Pull complete
1dafb86732d6: Pull complete
ad1854653222: Pull complete
410147bb9559: Pull complete
ee8d11af0d8f: Pull complete
Digest: sha256:ac5dd18c43b0ee89320764684aa5c2da5c3c2ce54cf01a6caab21f905302d1b5
Status: Downloaded newer image for mdillon/postgis:latest
Creating postgis … done
このコマンドは、dockerコンテナをバックグラウンドで起動せよという意味ですが、イメージやコンテナが存在しない場合は取得してくれます。
ネットワークが速ければ数分でイメージ取得、コンテナ作成、コンテナ起動まで完了するはずです。
コンテナの起動状態は、下記コマンドで確認します。StateがUpであれば正常起動しています。
$ docker-compose ps
Name Command State Ports
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
postgis docker-entrypoint.sh postgres Up 0.0.0.0:XXXX->5432/tcp
念のためコンテナ内でpsqlを起動しバージョンを確認しましょう。
$ docker exec -i -t postgis bash
root@524c9c79d148:/# psql -d postgis -U takamoto
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type “help” for help.
postgis=# select version();
version
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
postgis=# select postgis_version();
postgis_version
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
緑下線にてコンテナ内でbashを起動し、黄下線にてpsqlを起動、赤下線と青下線でPostgreSQLとPostGISのバージョンを取得しています。
コマンドラインにてexitを2回入力すれば、ホスト側（Dockerの外）に戻れます。
※もし、PostGISが導入されていない場合は、後述のpgAdminにて導入してください。
2.pgAdmin4の導入
これでサーバー側は構築できましたので、管理ツールのpgAdminを導入します。
pgAdminは、以下からダウンロードします。
https://www.pgadmin.org/download/pgadmin-4-windows/
一覧から現時点の最新版のインストーラ（画面例ではv4.19）を選択しダウンロードします。
ダウンロード後は、インストーラを起動してください。デフォルトインストールで問題はないでしょう。Visual Studio 2015 C++ Runtime（C++の再頒布用ライブラリ）も導入されます。
pgAdmin4を初回起動すると英語表記となっていますが、[File]、[Miscellaneous]、[User Language]を選択し、”Japanese”を指定して”Save”ボタンを押下すると、以下のように日本語化されます。
また、画面左のツリー”拡張”に”postgis”が存在しない場合は、下図のように拡張を右クリック後に表示されるダイアログからPostGISを追加インストールしてください。
名称に”postgis”と入力し、”保存”ボタンを押下すれば導入されます。
管理対象のPostGISへの接続は、画面左の”Servers”の右クリックメニューから[作成]、[サーバ]を選択し、表示される”作成・サーバ”ダイアログに接続情報を設定し”保存”ボタン押下により接続されます。
※Windows版psqlの動作確認
Windows版のpgadmin4を導入すると、以下にpsqlの実行モジュールも導入されますので便利です。
（pgadmin導入ディレクトリ）\pgadmin4\v4\runtime\psql.exe
パスを通すなどして、上記実行モジュールをコマンドラインから以下のように起動するとPsotgreSQLへ接続されます。
>psql -h hogeSVR -p hogePORT -d hogeDB -U hogeUSER
3.shp2pgsql-guiの導入
これが最後の事前準備です。シェープファイルをPostGISへ導入するGUIツールshp2pgsqlを導入しましょう。
媒体は、以下のサイトからダウンロードします。（32ビット版はバージョンが古いですが特に問題なく動作しました。）
64ビット版 http://download.osgeo.org/postgis/windows/pg11/
32ビット版 http://download.osgeo.org/postgis/windows/pg96/
媒体は、zipファイルをダウンロードしてください。
ダウンロードしたzipファイルを開き“bin\postgissui”フォルダだけを任意の場所に解凍します。
解凍後、フォルダ内の”shp2pgsql-gui.exe”を起動し下記画面が表示されれば導入完了です。
4.シェープファイルのインポート
ようやくここからが課題への対応です。
使用するデータとして、下記をダウンロードして解凍しておきます。
https://github.com/gis-oer/datasets/raw/master/tokyo.zip
次にshp2pgsql-guiを起動し、画面上部のボタン”View connection details…”を押下し、表示されたダイアログに接続情報を入力して”OK”ボタンを押下すれば接続できます。
次にshp2pgsql-guiの画面中央にある”Add File”ボタンを押下して下記2ファイルを追加します。
・tokyo_23ku_jgd2011_9.shp（23区の面データ）
・station_9kei.shp（駅の点データ）
なお、”Import List”のSRIDは自動検出してくれませんので、クリックして”6677”を入力します。
準備ができたら”実行”ボタンを押下します。
インポートが成功すると、psqlでは下記のようにテーブルの存在を確認できます。
postgis-# \d
List of relations
Schema | Name | Type | Owner
－－－－+－－－－－－－－－－－－－－－+－－－－－ + －－－－－－－
public | station_9kei | table | takamoto
public | tokyo_23ku_jgd2011_9 | table | takamoto
また、下図のようにpgAdminでも確認できます。
5.駅密度を求めるSQLの実行
それでは、課題の核心である23区ごとの駅密度を求めるSQLを考えてみましょう。
駅密度ですから、以下の計算になりそうです。
1.23区それぞれの駅数を求め
2.区の面積を求めた駅数で割る
3.結果は1店舗あたりの面積となり狭い方が密度が高い
SQLの核心は、23区内の駅数をSQLで求めるところのようです。
まずは、テーブルの情報を整理しましょう。
・駅データ ：station_9kei、座標カラム geom、件数 1,188件
・23区データ：tokyo_23ku_jgd2011_9、区名カラム name、座標カラム geom、件数 23件
では、PostGISのマニュアルをあたってみましょう。今回導入したPostGIS2.5のマニュアルは下記URLになります。
https://www.finds.jp/docs/pgisman/2.5.0/index.html
マニュアルの8.9章に空間関係関数があります。
https://www.finds.jp/docs/pgisman/2.5.0/reference.html#Spatial_Relationships_Measurements
使えそうな関数は、以下の2つだと思われます。
・面積計算：ST_Area
・包含関係：ST_Contains
早速、SQLを組み立ててみましょう。以下のようになります。
select sum.kuname,sum.kuarea/sum.ekicount/1000/1000 ekidensity ,outku.geom
from (
select ku.name kuname,ST_Area(ku.geom) kuarea,count(*) ekicount
from tokyo_23ku_jgd2011_9 ku ,station_9kei eki
where ST_Contains(ku.geom,eki.geom)
group by kuname ,kuarea
) sum ,tokyo_23ku_jgd2011_9 outku
where sum.kuname=outku.name ;3～6行目で面積を求めて区内の駅を集計しています。（面積もグループ化して問題ないのは値が同じだから）
ジオメトリカラムはグループ化できないため、前述の結果に区のジオメトリカラムを結合しています。
以前ご紹介したOpenJumpでは、SQLをそのまま発行できるのでランキング地図を簡単に表示できます。（キャプチャのSQLは少し違います（外部結合））
6.結果をQGISへ取り込み色替え地図を表示
QGISではSQLの直接発行ができないようなので、結果を取り込むためにはPostGIS上にビューの作成が必要です。
先ほどのSQLの先頭に以下を追加して発行すると、”kueki”という名称のビューが作成されますので、これをQGISへ取り込みます。
”create or replace view kueki as”
QGISへの接続は、下図のように画面左の”ブラウザ”からPostGISを右クリックして”接続の新規作成…”を指定します。
表示される”新しいPostGIS接続を作成する”ダイアログに接続情報を設定します。
接続が成功すると画面左にPostGIS上のテーブルやビューなどのオブジェクトが表示されますので、先ほど作成したビュー”kueki”を選択して表示します。
ラベルを付けて駅密度（ekidensity）でランキング表示した結果が下図となります。
駅密度は、千代田区などの中心部が300平方メートル程度ですが、江戸川区は4,900平方メートル程度となっており、密度の差は16倍程度となっています。
まとめ
ここまで、以下を学習しました。
- PostgreSQLおよびPostGISの環境構築
- gAdminの導入と使い方
- shp2pgsql-guiの導入と使い方
- PostGISの空間関数のリファレンスと使い方の確認
- QGISへの取り込み方法
今回は、導入の部分で大きな作業となってしまいました。
PostgreSQLなどのサーバー類の端末へのインストールはちょっと。。という方にはDockerはお勧めです。
PostGISは、SQLだけで空間データを取り扱えるため大変便利ですね。
ただ、QGISでのPostGISデータの表示は、OpenJumpに比べてとても重い（遅い）のが気になりました。もしかしたら、シェープファイルとPostGISでQGIS内部のジオメトリの保有形式が違うのかなと思いました。
