以前にSpatiaLiteを使いましたが今回はGIS教材のテキスト通りにPostgreSQL / PostGISでやってみます。
GIS教材の空間データベースではWindowsベースでしたが、ここではUbuntu 20.04.1 LTS Serverでやってみます。
データベースのインストールはラズパイ（Ubuntu 20.04 LTS Server）にOpenStreetMapのタイルサーバーを構築を参照
PostgreSQL / PostGISのバージョンは、PostgreSQL が12.4、PostGISが3.0です。
pgAdminというGUIを使ったやり方です。CUIのみで完結する場合はここを参照。
pgAdminやQGISのインストールは
ラズパイ（Ubuntu 20.04 LTS Server）にpgAdmin4をインストール
や
ラズパイ 4 Model B （Ubuntu 20.04 LTS Server）にQGIS3をインストール
を参照。
Windowsのリモートデスクトップから開いておく場合はWorkspaceを別にしておくと便利です。
ちなみにリモートデスクトップ関連リンクはこれらのインストールページをご覧ください。
データは国土数値情報 ダウンロードサービスを利用したものです。
大阪の行政区域（district）と避難施設（shelter）の２種類が同梱されています。
避難施設として「パナソニックスタジアム吹田」が追加されていますが、これについてはこのページを参照
実習用databaseをダウンロードしておきます。
オリジナルバージョンはこちら
ラズパイ（Ubuntu 20.04 LTS Server）で空間データベース（東京編）
空間データベースの作成
pgAdmin4の起動
ラズパイ（Ubuntu 20.04 LTS Server）にpgAdmin4をインストール参照。
仮想環境から起動しておいて、WindowsのリモートデスクトップのFirefoxで
http://localhost:5050にアクセス。
testDBというServerが作られているものとします。
新規のデータベース（ここではosakaとした）の作成を行います。
1.データベースの上にカーソルをあわせて右クリックし、作成 > データベースを選択する。
2.データベースをosaka 所有者をpostgresとする。
3.定義から、エンコードがUTF8であることを確認。
4.保存をクリックするとosakaのデータベースが作成される。
空間解析機能の追加
次のパートで空間データを読み込むため、pgAdmin4に空間データを扱うための拡張機能であるPostGISを追加します。
以下のように、拡張の上にカーソルをあわせて右クリックし、作成 > 拡張を選択します。
ウィンドウが表示されたら、名称をクリックし、postgisと入力し、保存をクリックします。
拡張にpostgisが追加されたことを確認します。
空間データのインポート
PostGISを用いてデータベースにシェープファイルを読み込みます。
WindowsではPostGIS x.x Shapefile and DBF Loader Exporterをエクスプローラーから起動することになっていますが、UbuntuのServer版ではこうなります。
Ubuntu にpostgis-guiパッケージがあるか確認します。
|
1 |
sudo apt list postgis-gui |
インストールします。
|
1 |
sudo apt install postgis-gui |
UbuntuのServer版にはGUIがないので、Windowsのリモートデスクトップから開きます。
上記でやったように新しいWorkspaceでApplications－＞「Run Program …」を開いて「shp2pgsql-gui」 で起動します（Applications－＞Otherからも開けます）。
View connection detailsをクリック。
接続するデータベースの情報を図のように設定します。
UsernameとPasswordはラズパイのOSインストール時に最初にログイン用に作ったユーザーとパスワードです。sudo adduser で新規に作成しても可。
データベースへの接続が完了したら、以下のようにインポートするシェープファイルを設定します。
Shapeファイルは上記で最初にダウンロードした実習用databaseの中にあります。
1.Add Fileから大坂の境界ポリゴンと避難施設のポイントを追加します（ファイルパス日本語を含まれるとエラーが生じる）。
2.測地系WGS84のSRIDを入力します。
3.Importをクリック。
以下のように、シェープファイルがosakaデータベースに追加されます。インポートしたファイルは、スキーマ>public>テーブルに追加されます。テーブルを選択し、「データを表示」をクリックすると属性情報が確認できます。
データ出力の右端をブラウズしてみると、View all geometries in this columnというボタンがあります。
クリックすれば、幾何データビューアで地図上にデータが表示されます。
QGISでのデータベース接続
Windowsのリモートデスクトップの新しいWorkspaceでQGISを開きます。
メニュ－＞レイヤ－＞レイヤの追加－＞PostGISレイヤの追加…
あるいは、データソースマネージャーをクリックします。
新規を選んで接続用の情報を入力してOK。
ベーシック認証の情報は必ずしも必要というわけではないようです。
接続するとテーブル情報を読み込みます。
読み込んだら一旦閉じます。
もしくは、ブラウザー内のPostGISの上で右クリックして「接続の新規作成…」から操作すれば直接接続されます。
ブラウザ内でダブルクリックしてレイヤに表示
背景地図にOpenStreetMapを使ってみます。
よく使うSQL文
次のパートでは、SQLでデータベースの検索や空間分析を行います。下記は、よく使うSQL文についてまとめたものです。初学者は、次のパートを始める前に一読してください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT文・・・・・DBのテーブルからデータを検索し、取得する文 SLECT ・・・・・ 列名 FROM ・・・・・テーブル名 WHERE ・・・・条件の指定（表示するデータの選択)列＝値など 列 = 値 ・・・ 列が値と一致するものを抽出 列 >= 値 ・・・ 列が値（数値）より大きいものを抽出 列 <= 値 ・・・ 列が値（数値）より小さいものを抽出 ※ 他に良く使うもの AND a=b and c>=d aがbと一致していてcがd(数値)以上のもの OR a=b and c>=d aがbと一致しているかcがd(数値)以上のもの LIKE a＝ LIKE '新％' a列に含まれる新○○を抽出する あいまい検索 ( チェック GROUP BY ・・・・ グループ化する集約関数） |
属性検索
以下では、属性検索の手法について解説しています。
以下のように、ツール> クエリツールを開き、SQLの入力ウィンドウを表示してください。
pgAdminで１つのServerに複数のデータベースがある場合は、それぞれでSQLウィンドウを開いておきます。
入力したSQL文を実行する際は、緑枠の実行ボタンをクリックします。
属性情報の表示
まず、大阪の行政区域の情報を表示します。
列名は以下を意味しています（国交省のページから抜粋）
n03_001－＞都道府県名
n03_002－＞支庁・振興局名
n03_003－＞郡・政令都市名
n03_004－＞市区町村名
n03_007－＞行政区域コード
select n03_004 from district;を実行し、districtテーブルのn03_004列を表示します。
ここで、select n03_001,n03_003,n03_004 from district;のようにすると複数の属性情報が表示できます。
以下ではshelter（避難施設）テーブルを使います。
避難施設の列名の意味です。
p20_001－＞行政区域
p20_002－＞名称
p20_003－＞住所
p20_004－＞施設の種類
p20_005－＞収容人数
p20_006－＞施設規模
p20_007－＞地震災害
p20_008－＞津波災害
p20_009－＞水害
p20_0010－＞火山災害
p20_0011－＞その他
p20_0012－＞指定なし
属性情報の計算
避難施設情報には、行政区域ごとの収容人数が入力されているため、その合計を求めてみます。
select sum(p20_005) from shelter;を実行すると、大阪の避難所使用人数上限が計算できます。
Sum()・・・合計を計算する関数
属性値に基づく抽出
ここでは、属性値に基づく抽出として施設の種類に「一時避難所」の属性を持つ避難施設の名称を抽出します。
|
1 |
select p20_002 FROM shelter where p20_004 like '%一時避難所%'; |
とし、実行します。
属性情報に基づく集計
施設の種類ごとに収容人数を計算します。select p20_004,count(p20_005) FROM shelter GROUP BY p20_004;を実行しまする。
count() データを数える関数
|
1 |
select p20_004,count(p20_005) AS “n” FROM shelter GROUP BY p20_004; |
を実行すると、施設の種類ごとのデータをテーブルnとして表示できます。
AS 別の列名として表示する
空間検索
以下では、ジオメトリーの情報を用いて、指定した領域や、複数の複数の空間データと重なる情報を検索する手法について解説します。
パナソニックスタジアム吹田のジオメトリを表示する
|
1 |
select p20_002,geom FROM shelter WHERE p20_002 = 'パナソニックスタジアム吹田'; |
パナソニックスタジアム吹田のジオメトリを検索します。
朱色で囲ったボタン（View all geometries in this column）で地図を表示
ジオメトリ値をコピーする場合は、ジオメトリ値を左クリックして以下のような表示が出たらCtrl + C
指定した領域に基づく検索
以下のように、パナソニックスタジアム吹田から半径1000m以内の避難施設を表示してみます。
上記でコピーしておいたジオメトリ値を使います。
クエリーはこんな感じ。
|
1 2 3 |
select p20_002,geom from shelter where ST_DWithin(shelter.geom,'0101000020E6100000C94C865938F16040140C6E47BF664140',1000,true); |
ST_DWithin関数では、第二引数にジオメトリ値を使います。
また第四引数に明示的にtrueを設定しておかないとまともな結果が返ってきませんでした、ちなみに第四引数はboolean use_spheroid。SRIDに4326を使っているため？
また、任意の地点から4km圏内で求めたい場合は、
|
1 2 3 |
select p20_002,geom from shelter where ST_DWithin(shelter.geom,ST_MakePoint(経度, 緯度),4000,true); |
データの領域による検索
以下のようなクエリーにすると、「パナソニックスタジアム吹田」のジオメトリを含むdistrict（行政区域）テーブルのgeom(ジオメトリー)を抽出できます。
|
1 2 3 4 5 6 |
SELECT shelter.p20_002,district.n03_004 FROM district JOIN shelter ON ST_Contains (district.geom, shelter.geom) WHERE shelter.p20_002 = 'パナソニックスタジアム吹田'; |
JOIN テーブル名 ON 条件式 テーブルを結合する式 ST_Contains(a,b) a（ジオメトリー）に含まれるb（ジオメトリー）を抽出する関数
空間結合
以下では、空間結合として、大阪の行政区のテーブルに新規に区ごとの避難施設数を計算したテーブルを作成する手法について解説しています。
残念ながら、GIS教材のSQL構文は実行できませんでした。
実行可能な構文を準備中。
PostgreSQLのバージョンチェック
|
1 |
psql --version |
PostGISのバージョンチェック
|
1 |
sudo -u postgres -i psql |
どれかのデータベースに接続して、以下を実行。左端の数字がバージョンです。
|
1 |
SELECT postgis_version(); |
CUIのみで空間データベース作成
Shapeファイルは上記で最初にダウンロードした実習用databaseの中にあるものを使います。
以下のようなディレクトリを作ってデータ一式をそこにコピー
mkdir /home/osaka-shelter
shelter.shp（避難施設）ファイルをSQLに変換します。
変換用のshp2pgsqlはPostgreSQLをインストールした時点でデフォルトで入っています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
cd /home/osaka-shelter shp2pgsql ＜Shapeファイル＞ ＜テーブル名＞ > ＜SQLファイル＞ shp2pgsql shelter.shp osaka_shelter > osaka_shelter.sql SQLファイルはディレクトリごとパーミッションを変更しておきます sudo chmod 777 -R /home/osaka-shelter osaka_facilitiesというデータベースを作ってそこにストアします。 sudo -u postgres -i createdb -E UTF8 -O ＜ユーザー名＞ osaka_facilities psql postgres=#\c osaka_facilities osaka_facilities=#CREATE EXTENSION postgis; \q psql osaka_facilities < /home/osaka-shelter/osaka_shelter.sql |
テーブルのオーナー名を変更する場合
ALTER TABLE ＜テーブル＞ OWNER TO ＜オーナー名＞;
国交省などのデータではカラム名などに独自（？）のコードを使っているので、民間人（？）には分かりづらいです。
テーブルのカラム名を変更する場合
ALTER TABLE ＜テーブル＞ RENAME COLUMN ＜旧カラム名＞ TO ＜新カラム名＞;
Leave a Reply