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を起動赤下線青下線PostgreSQLPostGISのバージョンを取得しています。
コマンドラインにてexitを2回入力すれば、ホスト側(Dockerの外)に戻れます。

※もし、PostGISが導入されていない場合は、後述のpgAdminにて導入してください。

2.pgAdmin4の導入
これでサーバー側は構築できましたので、管理ツールのpgAdminを導入します。
pgAdminは、以下からダウンロードします。
https://www.pgadmin.org/download/pgadmin-4-windows/

一覧から現時点の最新版のインストーラ(画面例ではv4.19)を選択しダウンロードします。

2020.3.8時点のpgAdmin最新版のダウンロード画面

ダウンロード後は、インストーラを起動してください。デフォルトインストールで問題はないでしょう。Visual Studio 2015 C++ Runtime(C++の再頒布用ライブラリ)も導入されます。
pgAdmin4を初回起動すると英語表記となっていますが、[File]、[Miscellaneous]、[User Language]を選択し、”Japanese”を指定して”Save”ボタンを押下すると、以下のように日本語化されます。

日本語化されたpgAdmin4

また、画面左のツリー”拡張”に”postgis”が存在しない場合は、下図のように拡張を右クリック後に表示されるダイアログからPostGISを追加インストールしてください。
名称に”postgis”と入力し、”保存”ボタンを押下すれば導入されます。

pgAdmin4から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”を起動し下記画面が表示されれば導入完了です。

shp2pgsql-guiの起動画面

4.シェープファイルのインポート
ようやくここからが課題への対応です。
使用するデータとして、下記をダウンロードして解凍しておきます。
https://github.com/gis-oer/datasets/raw/master/tokyo.zip

次にshp2pgsql-guiを起動し、画面上部のボタン”View connection details…”を押下し、表示されたダイアログに接続情報を入力して”OK”ボタンを押下すれば接続できます。

PostGISへの接続情報入力画面

次にshp2pgsql-guiの画面中央にある”Add File”ボタンを押下して下記2ファイルを追加します。
・tokyo_23ku_jgd2011_9.shp(23区の面データ)
・station_9kei.shp(駅の点データ)
なお、”Import List”のSRIDは自動検出してくれませんので、クリックして”6677”を入力します。

PostGISにインポートするファイルを指定

準備ができたら”実行”ボタンを押下します。
インポートが成功すると、psqlでは下記のようにテーブルの存在を確認できます。

postgis-# \d
List of relations
Schema |   Name         | Type   | Owner
----+---------------+----- + -------
public | station_9kei          | table   | takamoto
public | tokyo_23ku_jgd2011_9     | table   | takamoto

また、下図のようにpgAdminでも確認できます。

インポートしたデータを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は少し違います(外部結合))

OpenJumpによるSQLの発行と色変え地図の表示

6.結果をQGISへ取り込み色替え地図を表示
QGISではSQLの直接発行ができないようなので、結果を取り込むためにはPostGIS上にビューの作成が必要です。
先ほどのSQLの先頭に以下を追加して発行すると、”kueki”という名称のビューが作成されますので、これをQGISへ取り込みます。
 ”create or replace view kueki as”

QGISへの接続は、下図のように画面左の”ブラウザ”からPostGISを右クリックして”接続の新規作成…”を指定します。

PostGISへの新規接続を指定

表示される”新しいPostGIS接続を作成する”ダイアログに接続情報を設定します。

PostGISへの接続情報を設定するダイアログ

接続が成功すると画面左にPostGIS上のテーブルやビューなどのオブジェクトが表示されますので、先ほど作成したビュー”kueki”を選択して表示します。
ラベルを付けて駅密度(ekidensity)でランキング表示した結果が下図となります。

PostGISで求めた駅密度をPostGISで表示した結果

駅密度は、千代田区などの中心部が300平方メートル程度ですが、江戸川区は4,900平方メートル程度となっており、密度の差は16倍程度となっています。

まとめ

ここまで、以下を学習しました。

  • PostgreSQLおよびPostGISの環境構築
  • gAdminの導入と使い方
  • shp2pgsql-guiの導入と使い方
  • PostGISの空間関数のリファレンスと使い方の確認
  • QGISへの取り込み方法

今回は、導入の部分で大きな作業となってしまいました。
PostgreSQLなどのサーバー類の端末へのインストールはちょっと。。という方にはDockerはお勧めです。
PostGISは、SQLだけで空間データを取り扱えるため大変便利ですね。
ただ、QGISでのPostGISデータの表示は、OpenJumpに比べてとても重い(遅い)のが気になりました。もしかしたら、シェープファイルとPostGISでQGIS内部のジオメトリの保有形式が違うのかなと思いました。