この記事は RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2019 11 日目のエントリとなります。
そして、以下の記事の続きでもあります。
- Alibaba Cloud の POLARDB を試してみる（１）MySQL 8.0 互換版起動編
- Alibaba Cloud の POLARDB を試してみる（２）MySQL 8.0 互換版のバッファプールまわりを中心に
- Alibaba Cloud の POLARDB を試してみる（３）MySQL 8.0 互換版でパラレルクエリを試す
- Alibaba Cloud の POLARDB を試してみる（４）PostgreSQL 11 互換版起動＋パラレルスキャン編
- Alibaba Cloud の POLARDB を試してみる（５）MySQL 8.0 互換版でパラレルクエリ（２）
この記事では、その４の続きとして、PostgreSQL 11 互換版の Ganos を試してみます。
Ganos とは
POLARDB PostgreSQL / Oracle 互換版で時空間データを扱う機能を提供するものです。
Ganos では、以下のモデルを扱うことができます。
- ジオメトリモデル（PostGIS 互換）
- ラスタモデル（PostGIS 互換とは別物？）
- 点群（ポイントクラウド）モデル
- パスモデル
- 軌道（軌跡）モデル
また、Ganos は「PostGIS より高速処理」をうたっていますが、説明等を見る限りでは PostGIS 互換の機能ではなく非互換の機能（↑の 2 番目以降）のことを指して「高速」と言っているのではないかと思います（あくまで推測）。
なお、「GPU が使える環境では GPU を使う」旨の説明もありますが、2019/12/10 現在、Alibaba Cloud が提供するインスタンスには該当するものがなさそうです。おそらく、オンプレミス向けのアプライアンス製品「POLARDB Box」についての説明でしょう。
試してみる
本当なら 2 番目以降の機能を試したいところですが、「時空間データナニモワカラナイ」状態なので、ここはおとなしく（？）PostGIS 互換機能を試すことにします。
国土交通省・国土数値情報 ダウンロードサービスから、行政区域のシェープファイルをダウンロードして Ganos に流し込み、市区町村の面積を合算して降順表示してみます。
データダウンロード・インポート準備
「行政区域」で「全国」を選び、平成 31 年のデータをダウンロードします。
PostGIS のクライアント（今回はバージョン 2.5 を使用）に含まれるshp2pgsqlを使ってテーブル定義とデータを変換・抽出します。
【参考】
- 国土交通省にあるGISデータをPostGISへインポート(2017年版)（end0tknr's kipple - 新web写経開発）
データ準備
[root@polartest ~]# unzip N03-190101_GML.zip
Archive: N03-190101_GML.zip
inflating: KS-META-N03-19_190101.xml
inflating: N03-19_190101.dbf
inflating: N03-19_190101.geojson
inflating: N03-19_190101.prj
inflating: N03-19_190101.shp
inflating: N03-19_190101.shx
inflating: N03-19_190101.xml
[root@polartest ~]# shp2pgsql -s 4612 -p N03-19_190101 gyosei_kuiki > create_gyosei_kuiki.sql
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
[root@polartest ~]# shp2pgsql -s 4612 -W cp932 -a N03-19_190101 gyosei_kuiki > i
nsert_gyosei_kuiki.sql
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
データベース準備
その４の記事の手順に従って、データベースクラスタを作成します。
※今回はデータベース名をganos_testとしました。
Ganos（GIS）有効化
データベースクラスタ作成後、Ganos の PostGIS 互換機能（基本部分）を有効にします。
Ganos有効化（psql）
[root@polartest ~]# psql -h polarprimary.pg.polardb.rds.aliyuncs.com -p 1921 -U polaradmin -d ganos_test
Password for user polaradmin:
Ganos有効化（続き）
ganos_test=> Create extension ganos_geometry cascade;
NOTICE: installing required extension "ganos_spatialref"
CREATE EXTENSION
ganos_test=> \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+----------+------------
public | geography_columns | view | polaradmin
public | geometry_columns | view | polaradmin
public | seq_spatial_ref_sys | sequence | polaradmin
public | spatial_ref_sys | table | polaradmin
(4 rows)
ganos_test=> \q
データ流し込み
続いて、テーブル定義とデータインポート用の SQL を流し込みます。
データ流し込み
[root@polartest ~]# psql -h polarprimary.pg.polardb.rds.aliyuncs.com -p 1921 -U polaradmin -d ganos_test < create_gyosei_kuiki.sql
Password for user polaradmin:
SET
SET
BEGIN
CREATE TABLE
ALTER TABLE
addgeometrycolumn
--------------------------------------------------------------
public.gyosei_kuiki.geom SRID:4612 TYPE:MULTIPOLYGON DIMS:2
(1 row)
COMMIT
ANALYZE
[root@polartest ~]# psql -h polarprimary.pg.polardb.rds.aliyuncs.com -p 1921 -U polaradmin -d ganos_test < insert_gyosei_kuiki.sql
Password for user polaradmin:
SET
SET
BEGIN
INSERT 0 1
（中略）
INSERT 0 1
COMMIT
ANALYZE
面積を計算してみる
psqlコマンドで POLARDB に接続し、データが入っていることを確認します。
psql
[root@polartest ~]# psql -h polarprimary.pg.polardb.rds.aliyuncs.com -p 1921 -U polaradmin -d ganos_test
Password for user polaradmin:
データ確認
ganos_test=> \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+------------
public | geography_columns | view | polaradmin
public | geometry_columns | view | polaradmin
public | gyosei_kuiki | table | polaradmin
public | gyosei_kuiki_gid_seq | sequence | polaradmin
public | seq_spatial_ref_sys | sequence | polaradmin
public | spatial_ref_sys | table | polaradmin
(6 rows)
ganos_test=> SELECT * FROM gyosei_kuiki LIMIT 1;
gid | n03_001 | n03_002 | n03_003 | n03_004 | n03_007 | geom
-----+---------+----------------------+---------+---------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | 北海道 | オホーツク総合振興局 | | 北見市 | 01208 | 010600002004120000010000000103000000010000000D00000054A4901E9B02624078B98FFA01104640F4ECBF0C9B02624030679ADE01104640785F5BE49A026240E89E8EE90110464068E33DD29A026240F8F02A22021046408098B9D19A026240B0A4D85A02104640282AD1DB9A0262407040AB080310464070A930FD9A02624098E0B66D05104640E8A315069B026240685FF49E06104640D4980E1B9B026240B81E0C510810464044D1A5519B026240300201BB0A104640C85BF7E49B02624070495D480A1046402017EE579B026240F08698C30410464054A4901E9B02624078B98FFA01104640
(1 row)
シェープファイルから面積を計算・合算し、市区町村別で上位から 20 位までを表示します。
面積上位20
ganos_test=> \timing
Timing is on.
ganos_test=> SELECT CONCAT(n03_001, n03_003, n03_004) AS city, SUM(ST_Area(Geography(geom))) AS area FROM gyosei_kuiki GROUP BY city ORDER BY area DESC LIMIT 20;
city | area
------------------------+------------------
岐阜県高山市 | 2177610833.71698
北海道択捉郡留別村 | 1477507333.34523
栃木県日光市 | 1449828803.67816
北海道北見市 | 1427405924.67816
北海道足寄郡足寄町 | 1408042799.885
北海道釧路市 | 1362896564.58215
北海道野付郡別海町 | 1344671002.37102
北海道紋別郡遠軽町 | 1331769686.09553
山形県鶴岡市 | 1314404093.35329
岩手県宮古市 | 1259138436.37317
岩手県一関市 | 1256423251.99734
広島県庄原市 | 1246494164.02729
富山県富山市 | 1241828724.60097
福島県いわき市 | 1232019119.12255
秋田県由利本荘市 | 1209592857.85402
新潟県村上市 | 1170623192.96715
秋田県北秋田市 | 1152760147.99449
北海道日高郡新ひだか町 | 1147551904.0626
北海道士別市 | 1119215025.47063
北海道枝幸郡枝幸町 | 1117469840.07533
(20 rows)
Time: 16003.727 ms
あくまでもシェープファイルのポリゴンデータから算出したものなので一般に統計情報として公開されているデータとの誤差がありますが、「それらしい値」が出ていると思います。
※政令指定都市は区別となっています。そのため、静岡県浜松市など本来上位にいるはずの市町村がランク外になっています。
次回は→今のところ未定です！
PostgreSQL 11 互換版で oss_fdw 機能を試す予定です。
マニュアルに従ってcreate extensions oss_fdw;を実行してみたのですが、
ERROR
fdw_test=> create extension oss_fdw;
ERROR: invalid extension name: "oss_fdw"
DETAIL: Extension is not supported.
ということで未サポートでした。
また、TimescaleDB plug-in を試したのですが、こちらも、
TimescaleDB初期化
[root@polartest ~]# psql -h polarprimary.pg.polardb.rds.aliyuncs.com -p 1921 -U polaradmin -d timescaledb_test
Password for user polaradmin:
psql (9.2.24, server 11.2)
WARNING: psql version 9.2, server version 11.0.
Some psql features might not work.
Type "help" for help.
timescaledb_test=> CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
FATAL: extension "timescaledb" must be preloaded
HINT: Please preload the timescaledb library via shared_preload_libraries.
This can be done by editing the config file at: /data/postgresql.conf
and adding 'timescaledb' to the list in the shared_preload_libraries config.
# Modify postgresql.conf:
shared_preload_libraries = 'timescaledb'
Another way to do this, if not preloading other libraries, is with the command:
echo "shared_preload_libraries = 'timescaledb'" >> /data/postgresql.conf
(Will require a database restart.)
If you REALLY know what you are doing and would like to load the library without preloading, you can disable this check with:
SET timescaledb.allow_install_without_preload = 'on';
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
の後、自動で DB 再起動が走るように見えて、どうやら上手く進まないようです（作成されるはずのストアドファンクションが作成されず…）。
というわけで、2019/12/11 時点で、POLARDB を試すシリーズはいったん終了することにしました。
ちょっと残念です。