The Sixwish project
公開日：2022/5/8 0:00:00

AlmaLinux 8

AlmaLinux 8.5 セットアップメモ

Section 3.2 データベースサーバー構築編(PostgreSQL+PostGIS)

About

PostgreSQL に PostGIS をインストールと設定手順を残しています。

環境は、Section 3.2 が終わった状態からとなります。

必要なパッケージをインストールする

epelを入れてpowertoolsを有効にします。

[root@localhost ~]# dnf install epel-release
(中略)
[root@localhost ~]# dnf config-manager --set-enabled powertools
(中略)

powertools 内に、PostGISで使うGDALなどのライブラリが含まれているので有効にしないと入らない感じです。

PostGIS の確認

PostGIS関係で何が入るか確認します。

[root@localhost ~]# dnf search postgis*
================================================= 名前 一致: postgis* ==================================================
postgis30_13.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis30_13-client.x86_64 : Client tools and their libraries of PostGIS
postgis30_13-devel.x86_64 : Development headers and libraries for PostGIS
postgis30_13-docs.x86_64 : Extra documentation for PostGIS
postgis30_13-gui.x86_64 : GUI for PostGIS
postgis30_13-utils.x86_64 : The utils for PostGIS
postgis31_13.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis31_13-client.x86_64 : Client tools and their libraries of PostGIS
postgis31_13-devel.x86_64 : Development headers and libraries for PostGIS
postgis31_13-docs.x86_64 : Extra documentation for PostGIS
postgis31_13-gui.x86_64 : GUI for PostGIS
postgis31_13-utils.x86_64 : The utils for PostGIS
postgis32_13.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis32_13-client.x86_64 : Client tools and their libraries of PostGIS
postgis32_13-devel.x86_64 : Development headers and libraries for PostGIS
postgis32_13-docs.x86_64 : Extra documentation for PostGIS
postgis32_13-gui.x86_64 : GUI for PostGIS
postgis32_13-utils.x86_64 : The utils for PostGIS
[root@localhost ~]#

PostGIS 3.0(postgis30)、3.1(postgis31)、3.2(postgis32)とあるのが確認できます。なお、_13 は、PostgreSQL 13 のものという意味。

インストール

対象バージョンが有効になったので普通に指定してインストールしていきましょう。

[root@localhost ~]# dnf install postgis32_13
依存関係が解決しました。
========================================================================================================================
 パッケージ                          Arch            バージョン                              リポジトリー         サイズ
========================================================================================================================
インストール:
 postgis32_13                        x86_64          3.2.1-1.rhel8                           pgdg13               5.1 M
依存関係のインストール:
(中略)
これでよろしいですか? [y/N]: y
パッケージのダウンロード:
(中略)
完了しました!
[root@localhost ~]#

関連ライブラリなども含めてインストールされます。

動作確認

Section 3.2 で、ロールを作ってあるので、新規にDBを作ってそこで確認してみます。

vagrant ユーザーで操作します。まずはデータベース作成。

[vagrant@localhost ~]$ createdb -O vagrant -E UTF-8 postgis_test_db
[vagrant@localhost ~]$

psql でDBに入って、postgisを初期化します。

[vagrant@localhost ~]$ psql postgis_test_db
psql (13.6)
"help"でヘルプを表示します。

postgis_test_db=> CREATE EXTENSION postgis;
ERROR:  機能拡張"postgis"を作成する権限がありません
HINT:  この機能拡張を生成するにはスーパユーザである必要があります。
postgis_test_db=>

想定外でした……このアカウントは対象外なので、postgresユーザーになってアクセスしてみます。

[vagrant@localhost ~]$ sudo su -
[root@localhost ~]# su - postgres
[postgres@localhost ~]$

ユーザーが変わったので同じように手続きをします。

[postgres@localhost ~]$ psql postgis_test_db
psql (13.6)
"help"でヘルプを表示します。

postgis_test_db=# CREATE EXTENSION postgis;
CREATE EXTENSION
postgis_test_db=# SELECT PostGIS_version();
            postgis_version
---------------------------------------
 3.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 行)
postgis_test_db=#

では、また、vagrant ユーザーでも同じようにバージョンが見れるかは確認してみてください。

使ってみる

地図上のお店や施設などの情報を管理するという名目でどのように使うか試していきます。

まず、テーブルを作成します。テーブルの作成は下記SQLを実行して作ります。

CREATE TABLE map_spot (
        ID bigserial,
        name text,
        location geography(POINT,4326),
        PRIMARY KEY(ID)
);

CREATE INDEX ON map_spot using gist (location);

型には、geographyを使います。説明では、距離計算とか球体をベースにして行われるもので、もう一つのgeometry型は平面で考えるものになります。今回は、緯度経度を扱うのでgeographyで管理します。

また、**(POINT,4326)**というのは、一点であることと、SRID(空間参照系識別子)を指定しています。

SRID測地系座標系
4301Tokyo 地理座標系
30161〜30179Tokyo平面直角座標系1系〜19系
102151〜102156TokyoUTMゾーン51〜55
4612JGD2000地理座標系
2443〜2461JGD2000平面直角座標系1系〜19系
3097〜3101JGD2000UTMゾーン51〜55
4326WGS84地理座標系
3857WGS84メルカトル図法
900913WGS84メルカトル図法

標準的に使われているWGS84を使っておけば問題も少ないだろうという事でそうしています。なお、PostGISのST_系の関数で変換もできるみたいです。

次にデータを入れてみましょう。

データの管理方法が(x, y)に準じるので、経度(x=lon)、緯度(y=lat)という順番でデータを入れることになります。緯度経度っていう事が多いので違和感がありますがそういうルールなのでinsertやselect時に注意してください。

という事でinsertしましょう。データはQiita 山手線停車駅の座標一覧を参考に作成しました。

INSERT INTO map_spot (name, location) VALUES ('東京','SRID=4326;POINT(139.766084 35.681382)');
INSERT INTO map_spot (name, location) VALUES ('有楽町','SRID=4326;POINT(139.763328 35.675069)');
INSERT INTO map_spot (name, location) VALUES ('新橋','SRID=4326;POINT(139.759640 35.665498)');
INSERT INTO map_spot (name, location) VALUES ('浜松町','SRID=4326;POINT(139.756749 35.655646)');
INSERT INTO map_spot (name, location) VALUES ('田町','SRID=4326;POINT(139.747575 35.645736)');
INSERT INTO map_spot (name, location) VALUES ('品川','SRID=4326;POINT(139.740440 35.630152)');
INSERT INTO map_spot (name, location) VALUES ('大崎','SRID=4326;POINT(139.728553 35.619700)');
INSERT INTO map_spot (name, location) VALUES ('五反田','SRID=4326;POINT(139.723444 35.626446)');
INSERT INTO map_spot (name, location) VALUES ('目黒','SRID=4326;POINT(139.715828 35.633998)');
INSERT INTO map_spot (name, location) VALUES ('恵比寿','SRID=4326;POINT(139.710106 35.646690)');
INSERT INTO map_spot (name, location) VALUES ('渋谷','SRID=4326;POINT(139.701334 35.658517)');
INSERT INTO map_spot (name, location) VALUES ('原宿','SRID=4326;POINT(139.702687 35.670168)');
INSERT INTO map_spot (name, location) VALUES ('代々木','SRID=4326;POINT(139.702042 35.683061)');
INSERT INTO map_spot (name, location) VALUES ('新宿','SRID=4326;POINT(139.700258 35.690921)');
INSERT INTO map_spot (name, location) VALUES ('新大久保','SRID=4326;POINT(139.700044 35.701306)');
INSERT INTO map_spot (name, location) VALUES ('高田馬場','SRID=4326;POINT(139.703782 35.712285)');
INSERT INTO map_spot (name, location) VALUES ('目白','SRID=4326;POINT(139.706587 35.721204)');
INSERT INTO map_spot (name, location) VALUES ('池袋','SRID=4326;POINT(139.710380 35.728926)');
INSERT INTO map_spot (name, location) VALUES ('大塚','SRID=4326;POINT(139.728662 35.731401)');
INSERT INTO map_spot (name, location) VALUES ('巣鴨','SRID=4326;POINT(139.739345 35.733492)');
INSERT INTO map_spot (name, location) VALUES ('駒込','SRID=4326;POINT(139.746875 35.736489)');
INSERT INTO map_spot (name, location) VALUES ('田端','SRID=4326;POINT(139.760860 35.738062)');
INSERT INTO map_spot (name, location) VALUES ('西日暮里','SRID=4326;POINT(139.766787 35.732135)');
INSERT INTO map_spot (name, location) VALUES ('日暮里','SRID=4326;POINT(139.770987 35.727772)');
INSERT INTO map_spot (name, location) VALUES ('鶯谷','SRID=4326;POINT(139.778837 35.720495)');
INSERT INTO map_spot (name, location) VALUES ('上野','SRID=4326;POINT(139.777254 35.713768)');
INSERT INTO map_spot (name, location) VALUES ('御徒町','SRID=4326;POINT(139.774632 35.707438)');
INSERT INTO map_spot (name, location) VALUES ('秋葉原','SRID=4326;POINT(139.774219 35.698683)');
INSERT INTO map_spot (name, location) VALUES ('神田','SRID=4326;POINT(139.770883 35.691690)');

データを確認します。

postgis_test_db=> SELECT * FROM map_spot;
 id |   name   |                      location
----+----------+----------------------------------------------------
  1 | 東京     | 0101000020E6100000A5BF97C283786140A20A7F8637D74140
  2 | 有楽町   | 0101000020E6100000E083D72E6D7861408FC536A968D64140
  3 | 新橋     | 0101000020E610000078978BF84E786140D6C6D8092FD54140
(中略)
 27 | 御徒町   | 0101000020E6100000EC4D0CC9C97861404DF910548DDA4140
 28 | 秋葉原   | 0101000020E6100000239EEC66C6786140B6A2CD716ED94140
 29 | 神田     | 0101000020E61000005B41D312AB786140317C444C89D84140
(29 行)

はい。よくわからない形式になっているので経度、緯度になるようにSelectしてみます。

postgis_test_db=> SELECT id, name, ST_Astext(location) as location FROM map_spot;
 id |   name   |          location
----+----------+-----------------------------
  1 | 東京     | POINT(139.766084 35.681382)
  2 | 有楽町   | POINT(139.763328 35.675069)
  3 | 新橋     | POINT(139.75964 35.665498)
(中略)
 27 | 御徒町   | POINT(139.774632 35.707438)
 28 | 秋葉原   | POINT(139.774219 35.698683)
 29 | 神田     | POINT(139.770883 35.69169)
(29 行)

緯度経度それぞれ別のフィールドにするには、locationをgeometry型にキャストしてから関数を呼ぶことでできます。

postgis_test_db=> SELECT id, name, ST_Y(location::geometry) as lat, ST_X(location::geometry) as lon FROM map_spot;
 id |   name   |    lat    |    lon
----+----------+-----------+------------
  1 | 東京     | 35.681382 | 139.766084
  2 | 有楽町   | 35.675069 | 139.763328
  3 | 新橋     | 35.665498 |  139.75964
(中略)
 27 | 御徒町   | 35.707438 | 139.774632
 28 | 秋葉原   | 35.698683 | 139.774219
 29 | 神田     |  35.69169 | 139.770883
(29 行)

次によく使いそうな、とあるポイントから何km以内にあるものというのがあるので、それを試してみます。東京駅から2km以内にあるものという条件で検索します。

SELECT id, name, ST_Y(location::geometry) as lat, ST_X(location::geometry) as lon FROM map_spot
 WHERE ST_DWithin(location, ST_GeomFromText('POINT(139.766084 35.681382)', 4326), 2000, true);

 id |  name  |    lat    |    lon
----+--------+-----------+------------
  1 | 東京   | 35.681382 | 139.766084
  2 | 有楽町 | 35.675069 | 139.763328
  3 | 新橋   | 35.665498 |  139.75964
 29 | 神田   |  35.69169 | 139.770883
(4 行)

検証はしていないけれどたぶん正しいのでしょう。

因みに矩形の場合は下記のような感じに。POLYGONで4か所+最初の点を指定する。今回は、新橋と秋葉原の座標を使って矩形にする。

位置経度緯度メモ
左上139.75964035.698683新橋の経度と秋葉原の緯度
右上139.77421935.698683秋葉原の経度と緯度
右下139.77421935.665498秋葉原の経度と新橋の緯度
左下139.75964035.665498新橋の経度と緯度
左上139.75964035.698683最初のコピー

とSQLは下記のようになります。

SELECT id, name, ST_Y(location::geometry) as lat, ST_X(location::geometry) as lon FROM map_spot
 WHERE ST_CoveredBy (location, 
        ST_GeomFromText('POLYGON(
                (139.759640 35.698683,
                 139.774219 35.698683,
                 139.774219 35.665498,
                 139.759640 35.665498,
                 139.75964 35.698683))', 4326));

地図も見ればわかりますが、結果は下記のようになります。

 id |  name  |    lat    |    lon
----+--------+-----------+------------
  1 | 東京   | 35.681382 | 139.766084
  2 | 有楽町 | 35.675069 | 139.763328
  3 | 新橋   | 35.665498 |  139.75964
 28 | 秋葉原 | 35.698683 | 139.774219
 29 | 神田   |  35.69169 | 139.770883
(5 行)

まとめ

SQLを叩いた結果だけ見ると、PHPやPythonで使う時にどうなるんだろう？と思わなくもないですが、半径R(m)以内にあるスポットをとか言う検索は簡単になるかと。