使用PostGIS進行時空分析-雲原生資料倉儲AnalyticDB-阿里雲

PostGIS是資料庫PostgreSQL的一個擴充，PostGIS提供空間對象、空間索引、空間操作函數和空間操作符等空間資訊服務功能。本文介紹如何使用PostGIS進行時空分析。

說明

PostGIS遵循Open Geospatial Consortium（OGC）規範。

通用操作

用戶端串連執行個體。
請參見用戶端串連。
初次裝載PostGIS擴充模組。
重要
- V6.3.8.9及以上版本，安裝或升級PostGIS外掛程式請在控制台外掛程式管理頁面操作（推薦）或提交工單聯絡支援人員處理。
- 您可以在控制台執行個體的基本資料頁查看核心小版本。
1. 建立擴充。
```
CREATE extension postgis;
```
2. 查看版本。
```
SELECT postgis_version();
SELECT postgis_full_version();
```

空間資料寫入資料庫表。

首先建立帶Geometry欄位的表，SQL參考。

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

該SQL表示插入的空間資料不區分幾何類型，幾何類型包括Point / MultiPoint / Linestring / MultiLinestring / Polygon / MultiPolygon等。如果在建立表時已知Geometry類型和SRID，SQL參考。

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

資料插入，SQL參考。

-- without srid
INSERT INTO testg values (1, ST_GeomFromText('point(116 39)'));

-- with srid
INSERT INTO test values (1, ST_GeomFromText('point(116 39)', 4326));

JDBC Java程式參考。

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) )";
            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為Geometry列名。

查看錶有哪些索引。

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;
```

典型空間查詢SQL。

矩形範圍查詢。

-- without srid
SELECT st_astext(geom) FROM testg
WHERE ST_Contains(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), geom);

-- with 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運算元產生一個Envelope。

幾何緩衝範圍查詢。

-- without srid
SELECT st_astext(geom) FROM testg
WHERE ST_DWithin(ST_GeomFromText('POINT(116 39)'), geom, 0.01);

-- with srid
SELECT st_astext(geom) FROM test 
WHERE ST_DWithin(ST_GeomFromText('POINT(116 39)', 4326), geom, 0.01);

ST_DWithin用法請參見ST_DWithin。

多邊形相交判定（在內部或在邊界上）。

-- without 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);

-- with 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 PostgreSQL 6.0版不完全相容PostGIS功能集，例如不支援 CREATE extension postgis_topology，不推薦用Geography類型建立表（如必須使用，SRID預設為0或4326）。

典型案例

案例一：電子圍欄情境

某客運監控服務電訊廠商，通過安裝在客車上的GPS定位終端收集定位元據，常見的業務有偏航警示、常去的服務區頻次、駛入特定地區提醒（例如易發事故地段、積水結冰地段）等，這類業務是比較典型的電子圍欄應用情境。以駛入特定地區提醒業務為例，特定地區不會頻繁變更且資料量偏少，可以一次採集定期更新，考慮地區表採用複製表，SQL參考。

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

插入Polygon / MultiPolygon類型的特定地區資料後，完成統計資訊收集（Analyze表名）並構建GIST索引。判定駛入地區，可以分為兩種情況：一種完全在地區內，一種是到達邊界就要提醒。兩種情況用到的空間運算元有所區別，SQL參考。

-- 完全在區劃內。
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)'));

SQL解釋：輸入變化的經緯度，查詢地區表Geom欄位包含或相交與輸入焦點的記錄，如果為0條記錄表示未駛入任何地區，如果為1條記錄表示駛入某個地區，如果大於1條記錄表示駛入多個地區（說明地區表有空間重疊的地區，需要從業務上驗證空間重疊的合理性）。

案例二：智慧交通情境

某智慧交通情境，資料庫包含線型軌跡表和其他業務表，一業務功能為尋找歷史軌跡表中曾經駛入過某一地區的軌跡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'),
 ......
);

軌跡按照天建立分區表，每天匯入資料後做統計資訊收集，並對分區表建立GIST空間索引。SQL參考：

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);

億級軌跡表做空間查詢的回應時間在80ms內。

案例三：商業客串流分析

某互連網生活服務電訊廠商，基於AnalyticDB PostgreSQL版做店鋪客流量分析，資料庫有兩張業務表：User簽到表和Shop店鋪地區表，表結構參考。

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

-- shop
CREATE TABLE user_shop (
  ghash7          int, 
  sid             int, 
  shop_poly       geometry) 
distributed by (ghash7);

業務表比較巧的設計是用Geohash或ZOrder編碼等方式將地理空間幾何降維作為分布鍵，而不用構建空間索引。客流統計的SQL參考。

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;