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表結合空間索引方式，如果是複雜分析場景，建議考慮典型案例中的商業客流分析案例。

AnalyticDB for PostgreSQL 空間資料分析實戰

通用操作

典型案例

與開源方案對标

應用常見問題

繼續閱讀

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

Effective Java 8:通用程式設計

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method