AnalyticDB for PostgreSQL 空間數據分析實戰

數字經濟時代，數據是其關鍵的生產資料，而空間信息作為一重要屬性集和模型特徵集在業界形成廣泛共識。政府層面，美國911之後，通信運營商為政府相關部門（如公安、交通、應急指揮等）提供手機定位信息受法律保護；社會部分行業，尤其涉及GIS、交通、物流、吃住行遊、自動駕駛等，無不與空間信息強相關。由此，空間數據的存儲、空間查詢與分析等特性成為數據庫的標配，比如NOSQL的Redis/MongoDB、RDBMS的MySQL/SQLServer/Oracle等都有相應模塊對其提供支持，PostgreSQL內核支持Geometric幾何類型，提供點、線、面、矩形、圓等幾何的存儲、幾何變換、空間關係判定（相交、包含、相等等）功能，模塊功能相對單一，缺失座標系轉換特性且用法不太優雅（不符合OGC規範），PostgreSQL開源界為彌補內核Geometric特性缺陷，衍生出PostGIS擴展模塊予以完善。

AnalyticDB PG版同樣支持空間數據存儲、簡單/複雜空間查詢、空間分析等功能。有所區別的是，公有云產品默認包含PostGIS擴展模塊包，但生產實例不默認裝載該擴展；專有云產品不包含PostGIS擴展模塊包，但為用戶提供PostGIS模塊整合到專有云AnalyticDB PG版的解決方案。下面介紹如何利用AnalyticDB PG版對空間數據進行管理和應用？

通用操作

1）客戶端連接實例

可參考連接實例。

2）初次裝載PostGIS擴展模塊

-- 創建擴展
create extension postgis;

-- 查看版本
select postgis_version();
select postgis_full_version();

3）空間數據寫入數據庫表

首先創建帶Geometry字段的表，SQL參考：

create table testg ( id int, geom geometry ) 
distributed by (id);

該SQL表示插入的空間數據不區分幾何類型，幾何類型包括Point / MultiPoint / Linestring / MultiLinestring / Polygon / MultiPolygon等。

如果在創建表時已知Geometry類型和SRID（有關SRID可參考 SRID），也可以參考如下SQL創建表：

create table test ( id int, geom geometry(point, 4326) ) 
distributed by (id);

Geometry類型指定Point類型，SRID為4326，SRID不指定默認為0。

寫入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");
    }
}

如果是OSM格式數據，不用提前創建表，可以藉助osm2pgsql工具導入，參考 openstreetmap數據導入。

如果是SHP格式數據，不用提前創建表，可以藉助shp2pgsql工具導入，參考 shp數據導入，也可以藉助一些GIS客戶端如ArcGIS Desktop等導入。

4）空間索引管理

創建空間索引SQL參考：

create index idx_test_geom on test using gist(geom);

idx_test_geom為自定義索引名，test為表名，geom為Geometry列名。

查看錶有哪些索引SQL參考：

select * from pg_stat_user_indexes 
where relname='test';

查看索引大小SQL參考：

select pg_indexes_size('idx_test_geom');

索引重建SQL參考：

reindex index idx_test_geom;

刪除索引SQL參考：

drop index idx_test_geom;

5）典型空間查詢SQL

• BBOX範圍查詢

-- 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 PG 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'),
 ......
);

軌跡按照天創建Partition表，每天導入數據後做統計數據收集，並對Partition表創建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);

億級軌跡表做空間查詢RT在80ms內，完全滿足業務對性能需求。

商業客流分析

某互聯網生活服務運營商，基於AnalyticDB PG版做店鋪客流量分析，數據庫有兩張業務表：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;

與開源方案對標

開源領域，比較典型的能夠支撐空間大數據管理與應用的方案有HBase+GeoMesa和Elasticsearch，我們簡單做一下對標介紹。

應用常見問題

1）對錶Geometry字段創建了空間索引，空間查詢為什麼不走空間索引？

具體問題具體分析。通過Explain查看SQL執行計劃，如果走的是SeqScan，可以嘗試：

set enable_seqscan = off;

--或者調低random_page_cost
set random_page_cost = 10;

2）表數據量很大，為什麼對錶Geometry字段創建空間索引會失敗？

這種情況是存在的，一方面是內存不夠觸發，另一方面創建索引需要足夠耐心。PSQL客戶端連接數據庫，檢查 maintenance_work_mem 參數配置項，根據實例規格可適當調整參數配置，SQL參考：

-- 參看參數配置
show maintenance_work_mem;

-- 修改參數配置
set maintenance_work_mem = '1GB';

另外如果是簡單查詢場景可以考慮Partition表結合空間索引方式，如果是複雜分析場景，建議考慮典型案例中的商業客流分析案例。

通用操作

典型案例

與開源方案對標

應用常見問題

Leave a Reply Cancel reply