この記事は RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2019 11 日目のエントリとなります。

そして、以下の記事の続きでもあります。

この記事では、その４の続きとして、PostgreSQL 11 互換版の Ganos を試してみます。

Ganos とは

POLARDB PostgreSQL / Oracle 互換版で時空間データを扱う機能を提供するものです。

Introduction - POLARDB Spatio-temporal Database

Ganos では、以下のモデルを扱うことができます。

また、Ganos は「PostGIS より高速処理」をうたっていますが、説明等を見る限りでは PostGIS 互換の機能ではなく非互換の機能（↑の 2 番目以降）のことを指して「高速」と言っているのではないかと思います（あくまで推測）。

なお、「GPU が使える環境では GPU を使う」旨の説明もありますが、2019/12/10 現在、Alibaba Cloud が提供するインスタンスには該当するものがなさそうです。おそらく、オンプレミス向けのアプライアンス製品「POLARDB Box」についての説明でしょう。

Enable GPU-accelerated computing

試してみる

本当なら 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 を試すシリーズはいったん終了することにしました。

ちょっと残念です。

Alibaba Cloud の POLARDB を試してみる（６）PostgreSQL 11 互換版で Ganos を試す

Ganos とは

試してみる

データダウンロード・インポート準備

データベース準備

Ganos（GIS）有効化

データ流し込み

面積を計算してみる

次回は→今のところ未定です！