AlmaLinux 10.1 セットアップメモ
Section 2.2 データベースサーバー構築編(PostgreSQL+PostGIS)
About
PostgreSQL に PostGIS をインストールと設定手順を残しています。
環境は、Section 2.1 が終わった状態からとなります。
PostGIS の確認
postgisの有無とバージョンを確認します。
[root@localhost ~]# dnf search postgis
============================================ Name Exactly Matched: postgis =============================================
postgis.x86_64 : Geographic Information Systems Extensions to PostgreSQL
=========================================== Name & Summary Matched: postgis ============================================
postgis-client.x86_64 : The CLI clients for PostGIS
postgis-docs.x86_64 : Extra documentation for PostGIS
postgis-upgrade.x86_64 : Support for upgrading Postgis
postgis-utils.x86_64 : The utils for PostGIS
=============================================== Summary Matched: postgis ===============================================
SFCGAL.x86_64 : C++ wrapper library around CGAL for PostGIS
[root@localhost ~]# dnf repoquery postgis
postgis-0:3.5.3-3.el10.x86_64
[root@localhost ~]#PostGIS 3.5.3 が提供されていることが確認できたのでこれをインストールします。
インストール
[root@localhost ~]# dnf -y install postgis
Dependencies resolved.
========================================================================================================================
Package Architecture Version Repository Size
========================================================================================================================
Installing:
postgis x86_64 3.5.3-3.el10 appstream 2.0 M
Installing dependencies:
gdal-libs x86_64 3.10.3-3.el10 appstream 3.6 M
geos x86_64 3.13.1-1.el10 appstream 1.2 M
jbigkit-libs x86_64 2.1-31.el10 appstream 53 k
libjpeg-turbo x86_64 3.0.2-4.el10 appstream 253 k
liblerc x86_64 4.0.0-8.el10 appstream 215 k
libtiff x86_64 4.6.0-6.el10_1.1 appstream 212 k
libtool-ltdl x86_64 2.4.7-13.el10 appstream 36 k
libwebp x86_64 1.3.2-8.el10 appstream 288 k
proj x86_64 9.6.0-2.el10 appstream 1.6 M
proj-data noarch 9.6.0-2.el10 appstream 1.3 M
protobuf-c x86_64 1.5.0-6.el10 baseos 32 k
unixODBC x86_64 2.3.12-6.el10 appstream 488 k
Transaction Summary
========================================================================================================================
Install 13 Packages
(略)
Complete!
[root@localhost ~]#PostgreSQLの公式サイト経由で入れるものと異なって少し少ないように見えますが、機能的に足りなければ公式サイト経由でPostgreSQLからインストールするようにしてください。
動作確認
Section 2.1 で、ロールを作ってあるので、新規に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 (16.11)
"help"でヘルプを表示します。
postgis_test_db=> CREATE EXTENSION postgis;
ERROR: permission denied to create extension "postgis"
HINT: Must be superuser to create this extension.
postgis_test_db=>postgis を作成する権限がありません、root で実行してください。 ということなので、postgresユーザーになってアクセスしてみます。
[vagrant@localhost ~]$ sudo su -
[root@localhost ~]# su - postgres
[postgres@localhost ~]$ ユーザーが変わったので同じように手続きをします。
[postgres@localhost ~]$ psql postgis_test_db
psql (16.11)
"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)');データを確認します。
SELECT * FROM map_spot; id | name | location
----+----------+----------------------------------------------------
1 | 東京 | 0101000020E6100000A5BF97C283786140A20A7F8637D74140
2 | 有楽町 | 0101000020E6100000E083D72E6D7861408FC536A968D64140
3 | 新橋 | 0101000020E610000078978BF84E786140D6C6D8092FD54140
(中略)
27 | 御徒町 | 0101000020E6100000EC4D0CC9C97861404DF910548DDA4140
28 | 秋葉原 | 0101000020E6100000239EEC66C6786140B6A2CD716ED94140
29 | 神田 | 0101000020E61000005B41D312AB786140317C444C89D84140
(29 行)はい。よくわからない形式になっているので経度、緯度になるようにSelectしてみます。
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型にキャストしてから関数を呼ぶことでできます。
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)以内にあるスポットをとか言う検索は簡単になるかと。