PostGIS を使用した時空間分析、ユースケース - AnalyticDB

PostGIS は PostgreSQL の拡張機能であり、空間オブジェクト、空間インデックス、空間関数、空間演算子などの機能を提供します。このトピックでは、PostGIS を使用して時空間分析を実行する方法について説明します。

説明

PostGIS は Open Geospatial Consortium （OGC）標準に準拠しています。

一般的な操作

AnalyticDB for PostgreSQL インスタンスに接続します。
詳細については、「クライアント接続」をご参照ください。
初めて PostGIS 拡張機能をインストールします。
重要
- AnalyticDB for PostgreSQL V6.3.8.9 以降のインスタンスに PostGIS 拡張機能をインストールまたはアップグレードするには、コンソールで [拡張機能] ページに移動する（推奨）か、submit a ticket を行います。
- コンソールの [基本情報] ページで、インスタンスのマイナーバージョンを表示できます。
1. 次の文を実行して、PostGIS 拡張機能をインストールします。
```
CREATE extension postgis;
```
2. 次の文を実行して、インストールされている PostGIS のバージョンをクエリします。
```
SELECT postgis_version();
SELECT postgis_full_version();
```

テーブルを作成し、空間データをテーブルに挿入します。

次の文を実行して、ジオメトリフィールドを持つテーブルを作成します。

CREATE TABLE testg ( id int, geom geometry ) 
distributed BY (id);

上記の文は、挿入された空間データが、Point、MultiPoint、Linestring、MultiLineString、Polygon、MultiPolygon などのジオメトリタイプに依存しないことを示しています。必要なジオメトリタイプと空間参照識別子（SRID）を持つテーブルを作成するには、次の文を実行します。

CREATE TABLE test ( id int, geom geometry(point, 4326) ) 
distributed BY (id);

次の文は、SRID を使用する場合と使用しない場合のテーブルへのデータの挿入方法を示しています。

-- SRID なし
INSERT INTO testg values (1, ST_GeomFromText('point(116 39)'));

-- SRID あり
INSERT INTO test values (1, ST_GeomFromText('point(116 39)', 4326));

次のコードは、Java Database Connectivity（JDBC）API を使用してデータを挿入する方法を示しています。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class <PGJDBC> {
    public static void main(String args[]) {
        Connection conn = null;
        Statement stmt = null;
        try{
            Class.forName("org.postgresql.Driver");
            conn = DriverManager.getConnection("jdbc:postgresql://<host>:3432/<database>","<user>", "<password>"); // データベース接続情報
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            String sql = "INSERT INTO test VALUES (1001, ST_GeomFromText('point(116 39)', 4326) )"; // データ挿入SQL
            stmt.executeUpdate(sql);

            stmt.close();
            conn.commit();
            conn.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + " : " + e.getMessage());
            System.exit(0);
        }
        System.out.println("insert successfully"); // 挿入成功メッセージ
    }
}

空間インデックスを管理します。
- 空間インデックスを作成します。
```
CREATE index idx_test_geom on test using gist(geom);
```
  idx_test_geom はカスタムインデックス名で、test はテーブル名、geom はジオメトリ列名です。
- テーブル内のインデックスをクエリします。
```
SELECT * FROM pg_stat_user_indexes 
WHERE relname='test';
```
- インデックスのサイズをクエリします。
```
SELECT pg_indexes_size('idx_test_geom');
```
- インデックスを再構築します。
```
reindex index idx_test_geom;
```
- インデックスを削除します。
```
DROP index idx_test_geom;
```
文を使用して、一般的な空間クエリを実行します。
- 矩形範囲クエリを実行します。
```
-- SRID なし
SELECT st_astext(geom) FROM testg
WHERE ST_Contains(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), geom);

-- SRID あり
SELECT st_astext(geom) FROM test 
WHERE ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), 4326), geom);
```
  ST_MakeBox2D 関数は、矩形ポリゴンとも呼ばれるエンベロープを作成します。
- ジオメトリバッファに基づいて範囲クエリを実行します。
```
-- SRID なし
SELECT st_astext(geom) FROM testg
WHERE ST_DWithin(ST_GeomFromText('POINT(116 39)'), geom, 0.01);

-- SRID あり
SELECT st_astext(geom) FROM test 
WHERE ST_DWithin(ST_GeomFromText('POINT(116 39)', 4326), geom, 0.01);
```
  ST_DWithin の詳細については、「ST_DWithin」をご参照ください。
- 内部または境界上にあるポリゴンの交差を確認します。
```
-- srid なし
SELECT st_astext(geom) FROM testg
WHERE ST_Intersects(ST_GeomFromText('POLYGON((116 39, 116.1 39, 116.1 39.1, 116 39.1, 116 39))'), geom);

-- srid あり
SELECT st_astext(geom) FROM test 
WHERE ST_Intersects(ST_GeomFromText('POLYGON((116 39, 116.1 39, 116.1 39.1, 116 39.1, 116 39))', 4326), geom);
```
  ST_* 演算子は大文字と小文字を区別しません。詳細については、「PostGIS の概要」をご参照ください。
説明
AnalyticDB for PostgreSQL V6.0 は、CREATE extension postgis_topology などの PostGIS の特定の機能と互換性がありません。 geography 列を含むテーブルを作成しないことをお勧めします。このタイプのテーブルを作成する必要がある場合は、SRID を 0 または 4326 に設定してください。

ユースケース

ケース 1：電子フェンス

旅客交通の監視サービスプロバイダーは、乗用車に搭載された GPS 端末を使用して位置データを収集します。一般的なサービスには、逸脱アラーム、人気のあるサービスエリアの頻度リマインダー、事故多発エリアや凍結道路などの特定のエリアの運転状況警告などがあります。これらのサービスには、電子フェンスを使用できます。特定のエリアの運転状況警告を例にとります。このようなエリアは変更される頻度が少なく、データ量が少なくなります。エリアデータは一度収集し、定期的に更新できます。エリアデータを記録するには、レプリケートテーブルを使用することをお勧めします。文の例：

CREATE TABLE ky_region (
  rid     serial,
  name    varchar(256),
  geom    geometry)
DISTRIBUTED REPLICATED;

Polygon または MultiPolygon タイプの特定のエリアデータが挿入されると、AnalyticDB for PostgreSQL は ANALYZE TABLE 文を使用して統計を収集し、Generalized Search Tree（GiST）インデックスを作成します。警告は、車がエリア内に完全に囲まれている場合にトリガーされる警告と、車が境界線に接触した場合にトリガーされる警告の 2 つのカテゴリに分類できます。各警告タイプは、異なる空間演算子を使用します。文の例：

-- エリア内に完全に囲まれている
SELECT rid, name FROM ky_region
WHERE ST_Contains(geom, ST_GeomFromText('POINT(116 39)'));

-- 境界線に接触している可能性がある
SELECT rid, name from ky_region
WHERE ST_Intersects(geom, ST_GeomFromText('POINT(116 39)'));

特定のポイントの緯度と経度が提供されると、文は geom フィールド内の指定されたポイントを含むまたは交差するレコードをクエリします。レコードが返されない場合、車はエリアに入りませんでした。1 つのレコードが返された場合、車はエリアに入りました。複数のレコードが返された場合、車は複数のエリアに入りました。テーブル内のエリアが重複している場合は、重複しているエリアが有効かどうかを確認します。

ケース 2：スマートトランスポーテーション

スマートトランスポーテーションデータベースには、運転軌跡テーブルとその他のビジネステーブルが含まれています。 AnalyticDB for PostgreSQL は、運転軌跡テーブルからエリアに入った運転軌跡の ID をクエリします。テーブルスキーマ：

CREATE TABLE vhc_trace_d (
 stat_date        text, 
 trace_id         text, 
 vhc_id           text, 
 rid_wkt          geometry) 
Distributed by (vhc_id) partition by LIST(stat_date)
(
 PARTITION p20191008 VALUES('20191008'),
 PARTITION p20191009 VALUES('20191009'),
 ......
);

AnalyticDB for PostgreSQL は、軌跡テーブルのパーティションを日単位で作成し、毎日インポートされるデータの統計を収集し、パーティションの GiST 空間インデックスを作成します。文の例：

SELECT trace_id FROM vhc_trace_d
WHERE ST_Intersects(
  ST_GeomFromText('Polygon((118.732461  29.207363,118.732366  29.207198,118.732511  29.205951,118.732296  29.205644,
                  118.73226  29.205469,118.732350  29.20470,118.731708  29.203399,118.731701  29.202401, 118.754689 29.213488,
                  118.750827 29.21316,118.750272 29.213337,118.749677 29.213257,118.748699 29.213388,118.747715 29.213206,
                  118.746580 29.213831,118.74639 29.213872,118.744989 29.213858,118.743442 29.213795,118.74174 29.213002,
                  118.735633 29.208167,118.734422 29.207699,118.733045 29.207450,118.732803 29.207342,118.732461  29.207363))'), rid_wkt);

数億行を含む軌跡テーブルに対する空間クエリは、80 ミリ秒未満で応答できます。

ケース 3：顧客トラフィック分析

インターネットサービスプロバイダーは、AnalyticDB for PostgreSQL を使用して顧客トラフィックを分析します。データベースには、ユーザーテーブルとショップテーブルの 2 つのビジネステーブルが含まれています。テーブルスキーマ：

-- ユーザー
CREATE TABLE user_label (
  ghash7          int, 
  uid             int, 
  workday_geo     geometry, 
  weekend_geo     geometry) 
distributed by (ghash7);

-- ショップ
CREATE TABLE user_shop (
  ghash7          int, 
  sid             int, 
  shop_poly       geometry) 
distributed by (ghash7);

ビジネステーブルは、空間インデックスを作成する代わりに、Geohash または Z オーダーを使用して、データ配布のための地理空間次元数を削減します。文の例：

SELECT COUNT(1)
FROM (
  SELECT DISTINCT T0.uid FROM user_label T0 JOIN user_shop T1 
  ON T1.ghash7 = T0.ghash7
  WHERE T1.sid IN (1,2,3) AND (ST_Intersects(T0.workday_geo, T1.shop_poly) 
                               OR ST_Intersects(T0.weekend_geo, T1.shop_poly))
) c;