ラズパイ（Ubuntu 20.04 LTS Server）で空間データベース（東京編）

以前にSpatiaLiteを使いましたが今回はテキスト通りに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やQGISのインストールは

ラズパイ（Ubuntu 20.04 LTS Server）にpgAdmin4をインストール

や

ラズパイ 4 Model B （Ubuntu 20.04 LTS Server）にQGIS3をインストール

を参照。

Windowsのリモートデスクトップから開いておく場合はWorkspaceを別にしておくと便利です。

ちなみにリモートデスクトップ関連リンクはこれらのインストールページをご覧ください。

実習用databaseをダウンロードしておきます。

wget https://github.com/gis-oer/datasets/raw/master/database.zip

1	wget https://github.com/gis-oer/datasets/raw/master/database.zip

空間データベースの作成

pgAdmin4の起動

ラズパイ（Ubuntu 20.04 LTS Server）にpgAdmin4をインストール参照。

仮想環境から起動しておいて、WindowsのリモートデスクトップのFirefoxで

http://localhost:5050にアクセス。

testDBというServerが作られているものとします。

新規のデータベース（ここではTokyoとした）の作成を行います。

1.データベースの上にカーソルをあわせて右クリックし、作成 > データベースを選択する。

2.データベースをtokyo 所有者をpostgresとする。

3.定義から、エンコードがutf-8であることを確認。

4.保存をクリックするとTokyoのデータベースが作成される。

空間解析機能の追加

次のパートで空間データを読み込むため、pgAdmin4に空間データを扱うための拡張機能であるPostGISを追加します。

以下のように、拡張の上にカーソルをあわせて右クリックし、作成 > 拡張を選択します。

ウィンドウが表示されたら、名称をクリックし、postgisと入力し、保存をクリックします。

拡張にpostgisが追加されたことを確認します。

空間データのインポート

PostGISを用いてデータベースにシェープファイルを読み込みます。

WindowsではPostGIS x.x Shapefile and DBF Loader Exporterをエクスプローラーから起動することになっていますが、UbuntuのServer版ではこうなります。

Ubuntu にpostgis-guiパッケージがあるか確認します。

sudo apt list postgis-gui

1	sudo apt list postgis-gui

インストールします。

sudo apt install 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.世界測地系平面直角9系のSRIDを入力します。

3.Importをクリック。

以下のように、シェープファイルがTokyoデータベースに追加されます。インポートしたファイルは、スキーマ>public>テーブルに追加されます。テーブルを選択し、「データを表示」をクリックすると属性情報が確認できます。

Geometry Viewerのタブが見当たらないのでデータはQGISで確認してみます。

QGISでのデータベース接続

Windowsのリモートデスクトップの新しいWorkspaceでQGISを開きます。

メニュ－＞レイヤ－＞レイヤの追加－＞PostGISレイヤの追加…

あるいは、データソースマネージャーをクリックします。

新規を選んで接続用の情報を入力してOK。

ベーシック認証の情報は必ずしも必要というわけではないようです。

接続するとテーブル情報を読み込みます。

読み込んだら一旦閉じます。

もしくは、ブラウザー内のPostGISの上で右クリックして「接続の新規作成…」から操作すれば直接接続されます。

ブラウザ内でダブルクリックしてレイヤに表示

背景地図にOpenStreetMapを使ってみます。

よく使うSQL文

次のパートでは、SQLでデータベースの検索や空間分析を行います。下記は、よく使うSQL文についてまとめたものです。初学者は、次のパートを始める前に一読してください。

SELECT文・・・・・DBのテーブルからデータを検索し、取得する文
SLECT ・・・・・ 列名
FROM　・・・・・テーブル名
WHERE　・・・・条件の指定（表示するデータの選択)列＝値など

列 = 値　・・・　　列が値と一致するものを抽出
列 &gt;= 値　・・・　列が値（数値）より大きいものを抽出
列 &lt;= 値　・・・　列が値（数値）より小さいものを抽出

※ 他に良く使うもの　AND a=b and c&gt;=d aがbと一致していてcがd(数値)以上のもの
    OR a=b and c&gt;=d aがbと一致しているかcがd(数値)以上のもの
    LIKE a＝ LIKE '新％' a列に含まれる新○○を抽出する　あいまい検索
    (　チェック　　　GROUP BY　・・・・ グループ化する集約関数）

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文を実行する際は、緑枠の実行ボタンをクリックします。

属性情報の表示

まず、東京23区の行政区域の情報を表示します。SELECT name FROM tokyo;を実行し、tokyoテーブルのname列を表示します。ここで、SELECT name,male FROM tokyo;のようにすると複数の属性情報が表示できます。

属性情報の計算

属性情報には、区ごとの人口数が入力されているため、その合計を求めます。SELECT sum(population) FROM tokyo;を実行すると、23区内の人口が計算できます。

Sum()・・・合計を計算する関数

属性値に基づく抽出

ここでは、属性値に基づく抽出として東日本旅客鉄道に所属する駅名を抽出します。

SELECT name FROM station WHERE operator='東日本旅客鉄道';

1	SELECT name FROM station WHERE operator='東日本旅客鉄道';

name・・・stationテーブルの駅名が含まれる列名 operator・・・stationテーブルの鉄道会社名が含まれる列名

属性情報に基づく集計

鉄道会社ごとに駅の数を計算します。SELECT operator,count(name) FROM station GROUP BY operator;を実行します。

count()　データを数える関数

以下を実行すると、鉄道会社ごとのデータをテーブルnとして表示できます。

SELECT operator,count(name) AS “n” FROM station GROUP BY operator;

1	SELECT operator,count(name) AS “n” FROM station GROUP BY operator;

AS　別の列名として表示する

空間検索

以下では、ジオメトリーの情報を用いて、指定した領域や、複数の複数の空間データと重なる情報を検索する手法について解説します。

勝どき駅のジオメトリを表示する

勝どき駅のジオメトリを検索します。

SELECT name,geom FROM station WHERE name = '勝どき';

1	SELECT name,geom FROM station WHERE name = '勝どき';

勝どき駅のジオメトリを検索します。ジオメトリの上でCntr+Cでジオメトリをコピーします。

指定した領域に基づく検索

以下のように、勝どき駅から半径1000m以内の駅を表示します。

SELECT name,geom
FROM station
WHERE ST_DWithin(station.geom,'010100002093090000ECBC762D13DAB3C011138E4EA079E2C0',1000);

SELECT name,geom

FROM station

WHERE ST_DWithin(station.geom,'010100002093090000ECBC762D13DAB3C011138E4EA079E2C0',1000);

ST_DWithin(geometry.列名,値 ,○○ｍ) ジオメトリと範囲を指定して、それに該当するレコードを抽出する関数

データの領域による検索

以下のようにすると、tokyoテーブルのgeom(ジオメトリー)に含まれる勝どき駅を抽出できます。

SELECT
  station.name,tokyo.name
FROM tokyo
JOIN station
ON ST_Contains (tokyo.geom, station.geom)
WHERE station.name = '勝どき';

SELECT

station.name,tokyo.name

FROM tokyo

JOIN station

ON ST_Contains (tokyo.geom, station.geom)

WHERE station.name = '勝どき';

JOIN　テーブル名　ON　条件式テーブルを結合する式 ST_Contains(a,b) a（ジオメトリー）に含まれるb（ジオメトリーを）抽出する関数

空間結合

以下では、空間結合として、東京23区のテーブルに新規に区ごとの駅数を計算したテーブルを作成する手法について解説しています。

残念ながら、GIS教材のSQL構文は実行できませんでした。

実行可能な構文を準備中。

PostgreSQLのバージョンチェック

psql --version

1	psql --version

PostGISのバージョンチェック

sudo -u postgres -i psql

1	sudo -u postgres -i psql

どれかのデータベースに接続して、以下を実行。左端の数字がバージョンです。

SELECT postgis_version();

1	SELECT postgis_version();

FRONT

地図と画像のサイト

ラズパイ（Ubuntu 20.04 LTS Server）で空間データベース（東京編）

Be the first to comment

Leave a Reply コメントをキャンセル