AlmaLinux 8.10
AlmaLinux 8.10 セットアップメモ
Section 3.2 データベースサーバー構築編(PostgreSQL+PostGIS)
About
PostgreSQL に PostGIS をインストールと設定手順を残しています。
環境は、Section 3.2 が終わった状態からとなります。
必要なパッケージをインストールする
epelを入れて(手順通りならインストール済み) powertoolsを有効にします。
[root@localhost ~]# dnf install epel-release
(中略)
[root@localhost ~]# dnf -y config-manager --set-enabled powertools
[root@localhost ~]#
powertools 内に、PostGISで使うGDALなどのライブラリが含まれているので有効にしないと入らない感じです。
PostGIS の確認
PostGIS関係で何が入るか確認します。
[root@localhost ~]# dnf search postgis*
================================================= 名前 一致: postgis* ==================================================
postgis33_17.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis33_17-client.x86_64 : Client tools and their libraries of PostGIS
postgis33_17-devel.x86_64 : Development headers and libraries for PostGIS
postgis33_17-docs.x86_64 : Extra documentation for PostGIS
postgis33_17-gui.x86_64 : GUI for PostGIS
postgis33_17-llvmjit.x86_64 : Just-in-time compilation support for PostGIS 3.3
postgis33_17-utils.x86_64 : The utils for PostGIS
postgis34_17.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis34_17-client.x86_64 : Client tools and their libraries of PostGIS
postgis34_17-devel.x86_64 : Development headers and libraries for PostGIS
postgis34_17-docs.x86_64 : Extra documentation for PostGIS
postgis34_17-gui.x86_64 : GUI for PostGIS
postgis34_17-llvmjit.x86_64 : Just-in-time compilation support for PostGIS 3.4
postgis34_17-utils.x86_64 : The utils for PostGIS
postgis35_17.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis35_17-client.x86_64 : Client tools and their libraries of PostGIS
postgis35_17-devel.x86_64 : Development headers and libraries for PostGIS
postgis35_17-docs.x86_64 : Extra documentation for PostGIS
postgis35_17-gui.x86_64 : GUI for PostGIS
postgis35_17-llvmjit.x86_64 : Just-in-time compilation support for PostGIS 3.5
postgis35_17-utils.x86_64 : The utils for PostGIS
[root@localhost ~]#
PostGIS 3.3(postgis33)、3.4(postgis34)、3.5(postgis35)とあるのが確認できます。なお、_17 は、PostgreSQL 17 のものという意味。
インストール
リポジトリ上に存在していることが分かったのでバージョンを指定してインストールしていきましょう。
[root@localhost ~]# dnf -y install postgis35_17
依存関係が解決しました。
========================================================================================================================
パッケージ アーキテクチャー バージョン リポジトリー サイズ
========================================================================================================================
インストール:
postgis35_17 x86_64 3.5.2-1PGDG.rhel8 pgdg17 4.9 M
依存関係のインストール:
SFCGAL-libs x86_64 1.4.1-13.rhel8 pgdg-common 1.9 M
SuperLU x86_64 5.2.0-7.el8 powertools 191 k
armadillo x86_64 12.6.6-1.el8 epel 42 k
arpack x86_64 3.7.0-1.el8 epel 194 k
atlas x86_64 3.10.3-8.el8 baseos 6.4 M
blas x86_64 3.8.0-8.el8 appstream 428 k
boost-atomic x86_64 1.66.0-13.el8 appstream 13 k
boost-chrono x86_64 1.66.0-13.el8 appstream 22 k
boost-date-time x86_64 1.66.0-13.el8 appstream 29 k
boost-serialization x86_64 1.66.0-13.el8 appstream 121 k
boost-system x86_64 1.66.0-13.el8 appstream 17 k
boost-thread x86_64 1.66.0-13.el8 appstream 58 k
cfitsio x86_64 3.47-1.el8 epel 577 k
dejavu-fonts-common noarch 2.35-7.el8 baseos 73 k
dejavu-sans-fonts noarch 2.35-7.el8 baseos 1.5 M
fontconfig x86_64 2.13.1-4.el8 baseos 273 k
fontpackages-filesystem noarch 1.44-22.el8 baseos 16 k
freexl x86_64 1.0.6-4.el8 epel 34 k
gdal38-libs x86_64 3.8.5-7PGDG.rhel8 pgdg-common 9.3 M
geos313 x86_64 3.13.1-1PGDG.rhel8 pgdg-common 1.3 M
giflib x86_64 5.1.4-3.el8 appstream 51 k
gmp-c++ x86_64 1:6.1.2-11.el8 baseos 32 k
gpsbabel x86_64 1.6.0-3.el8 epel 964 k
hdf x86_64 4.2.14-5.el8 epel 651 k
hdf5 x86_64 1.10.5-4.el8 epel 2.1 M
jbigkit-libs x86_64 2.1-14.el8 appstream 54 k
lapack x86_64 3.8.0-8.el8 appstream 8.6 M
lcms2 x86_64 2.9-2.el8 appstream 164 k
libaec x86_64 1.0.2-3.el8 powertools 39 k
libarrow x86_64 8.0.1-3.el8 epel 4.7 M
libbsd x86_64 0.12.2-1.el8 epel 132 k
libdeflate x86_64 1.9-3.el8 epel 53 k
libgeotiff17 x86_64 1.7.3-2PGDG.rhel8 pgdg-common 106 k
libgeotiff17-devel x86_64 1.7.3-2PGDG.rhel8 pgdg-common 33 k
libgfortran x86_64 8.5.0-26.el8_10.alma.1 baseos 645 k
libgta x86_64 1.2.1-1.el8 epel 34 k
libjpeg-turbo x86_64 1.5.3-14.el8_10 appstream 156 k
libkml x86_64 1.3.0-24.el8 epel 379 k
libmd x86_64 1.1.0-1.el8 epel 45 k
libqhull_r x86_64 2015.2-5.el8 powertools 170 k
libquadmath x86_64 8.5.0-26.el8_10.alma.1 baseos 172 k
librttopo x86_64 1.1.0-2.rhel8 pgdg-common 178 k
libspatialite50 x86_64 5.1.0-6PGDG.rhel8 pgdg-common 3.3 M
libspatialite50-devel x86_64 5.1.0-6PGDG.rhel8 pgdg-common 102 k
libtiff x86_64 4.0.9-34.el8_10 appstream 189 k
libtiff-devel x86_64 4.0.9-34.el8_10 appstream 512 k
libtool-ltdl x86_64 2.4.6-25.el8 baseos 58 k
libusb x86_64 1:0.1.5-12.el8 baseos 42 k
libwebp x86_64 1.0.0-11.el8_10 appstream 273 k
libxslt x86_64 1.1.32-6.2.el8_10 baseos 249 k
mariadb-connector-c x86_64 3.1.11-2.el8_3 appstream 199 k
mariadb-connector-c-config noarch 3.1.11-2.el8_3 appstream 14 k
minizip x86_64 2.8.9-2.el8 epel 124 k
netcdf x86_64 4.7.0-3.el8 epel 645 k
nspr x86_64 4.35.0-1.el8_8 appstream 142 k
nss x86_64 3.101.0-11.el8_8 appstream 764 k
nss-softokn x86_64 3.101.0-11.el8_8 appstream 531 k
nss-softokn-freebl x86_64 3.101.0-11.el8_8 appstream 391 k
nss-sysinit x86_64 3.101.0-11.el8_8 appstream 76 k
nss-util x86_64 3.101.0-11.el8_8 appstream 142 k
ogdi41 x86_64 4.1.1-1PGDG.rhel8 pgdg-common 744 k
openblas x86_64 0.3.15-6.el8 appstream 5.0 M
openblas-openmp x86_64 0.3.15-6.el8 powertools 5.2 M
openblas-threads x86_64 0.3.15-6.el8 appstream 5.2 M
openblas-threads64_ x86_64 0.3.15-6.el8 powertools 5.1 M
openjpeg2 x86_64 2.4.0-5.el8 appstream 164 k
pgdg-poppler x86_64 20.11.0-3.rhel8 pgdg-common 1.0 M
pgdg-poppler-data noarch 0.4.9-7.rhel8 pgdg-common 2.1 M
postgresql17-contrib x86_64 17.5-3PGDG.rhel8 pgdg17 769 k
proj95 x86_64 9.5.1-1PGDG.rhel8 pgdg-common 2.9 M
protobuf-c x86_64 1.3.0-8.el8 appstream 36 k
qt5-qtbase x86_64 5.15.3-8.el8_10 appstream 3.6 M
qt5-qtbase-common noarch 5.15.3-8.el8_10 appstream 41 k
re2 x86_64 20190801-17.el8 epel 190 k
shapelib x86_64 1.5.0-12.el8 epel 81 k
unixODBC x86_64 2.3.7-1.el8 appstream 458 k
uriparser x86_64 0.9.8-2.el8 epel 67 k
xerces-c x86_64 3.2.5-1.el8 epel 985 k
トランザクションの概要
========================================================================================================================
インストール 79 パッケージ
(中略)
完了しました!
[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 (17.5)
"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 (17.5)
"help"でヘルプを表示します。
postgis_test_db=# CREATE EXTENSION postgis;
CREATE EXTENSION
postgis_test_db=# SELECT PostGIS_version();
postgis_version
---------------------------------------
3.5 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 | 測地系 | 座標系 |
|---|---|---|
| 4301 | Tokyo 地理座標系 | |
| 30161〜30179 | Tokyo | 平面直角座標系1系〜19系 |
| 102151〜102156 | Tokyo | UTMゾーン51〜55 |
| 4612 | JGD2000 | 地理座標系 |
| 2443〜2461 | JGD2000 | 平面直角座標系1系〜19系 |
| 3097〜3101 | JGD2000 | UTMゾーン51〜55 |
| 4326 | WGS84 | 地理座標系 |
| 3857 | WGS84 | メルカトル図法 |
| 900913 | WGS84 | メルカトル図法 |
標準的に使われている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.759640 | 35.698683 | 新橋の経度と秋葉原の緯度 |
| 右上 | 139.774219 | 35.698683 | 秋葉原の経度と緯度 |
| 右下 | 139.774219 | 35.665498 | 秋葉原の経度と新橋の緯度 |
| 左下 | 139.759640 | 35.665498 | 新橋の経度と緯度 |
| 左上 | 139.759640 | 35.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)以内にあるスポットをとか言う検索は簡単になるかと。