使用Postgres，MobilityDB和Citus大規模(百億級)實時分析GPS軌跡-有解無憂

背景

https://github.com/MobilityDB/MobilityDB

https://www.citusdata.com/download/

https://www.postgresql.org/

https://www.citusdata.com/blog/2020/11/09/analyzing-gps-trajectories-at-scale-with-postgres-mobilitydb/

GPS已成為我們日常生活的一部分，GPS在用于導航的汽車中，在智能手機中可以幫助我們找到位置，最近，GPS一直在幫助我們避免被COVID-19感染，管理和分析流動性軌跡是我作業的核心，我在布魯塞爾自由大學的團隊專門研究移動資料管理，我們為時空軌跡建立了一個開源資料庫系統，稱為MobilityDB，MobilityDB在Postgres資料庫及其空間擴展PostGIS中增加了對時空物件的支持，如果您還不熟悉時空軌跡，請放心，我們將簡要介紹公共交通工具的一些運動軌跡，

我團隊的專案之一是開發MobilityDB的分布式版本，這是我們與Postgres的Citus擴展以及Citus工程團隊取得聯系的地方，這篇文章介紹了運動軌跡資料的分布式查詢處理的問題和解決方案，GPS是軌跡資料的最常見來源，但是本文中的想法也適用于其他位置跟蹤傳感器收集的運動軌跡，例如飛機的雷達系統和船舶的AIS系統，

首先，讓我們探索軌跡資料管理的主要概念，以便您可以了解如何分析地理空間運動軌跡，

下面的影片gif顯示了靠近廣告廣告牌的公交車1的地理空間軌跡，如果您想評估廣告牌對公交車乘客的可見度，該怎么辦？如果您可以對所有廣告牌和車輛執行此操作，那么您將能夠提取有趣的見解，以便廣告代理商為廣告牌定價，并為希望優化其廣告系列的廣告客戶提供資訊，

在整個這篇文章中，我將使用地圖可視化布魯塞爾的公交車軌跡和廣告廣告牌，因此您可以學習如何查詢公交車乘客在哪里看到廣告廣告牌（以及顯示多長時間），背景圖由OpenStreetMap提供，
在上面的影片gif中，我們簡單地假設，如果公共汽車到廣告牌30米以內，那么它對乘客是可見的，當公交車位于廣告牌30米以內時，影片中的“可見性”通過廣告牌周圍的黃色閃爍指示，

如何使用資料庫查詢來衡量廣告牌對行駛中的公交車的可見性？
讓我們準備一個玩具PostGIS資料庫，該資料庫最少地表示以前的gif影片中的示例，然后逐步開發一個SQL查詢，以評估行駛中的公共汽車上的乘客對廣告牌的可見性，

如果您不熟悉PostGIS，則它可能是Postgres最受歡迎的擴展，用于存盤和查詢空間資料，就本文而言，您需要知道的是PostGIS擴展了Postgres的資料型別，包括幾何點，線和面，PostGIS還定義了一些功能，用于測量地理特征之間的距離并測驗拓撲關系（例如交叉點），

在下面的SQL代碼塊中，首先創建PostGIS擴展，然后，您將創建兩個表：gpsPoint和billboard，

CREATE EXTENSION PostGIS;  
CREATE TABLE gpsPoint (tripID int, pointID int, t timestamp, geom geometry(Point, 3812));  
CREATE TABLE billboard(billboardID int, geom geometry(Point, 3812));  
  
INSERT INTO gpsPoint Values  
(1, 1, '2020-04-21 08:37:27', 'SRID=3812;POINT(651096.993815166 667028.114604598)'),  
(1, 2, '2020-04-21 08:37:39', 'SRID=3812;POINT(651080.424535144 667123.352304597)'),  
(1, 3, '2020-04-21 08:38:06', 'SRID=3812;POINT(651067.607438095 667173.570340437)'),  
(1, 4, '2020-04-21 08:38:31', 'SRID=3812;POINT(651052.741845233 667213.026797244)'),  
(1, 5, '2020-04-21 08:38:49', 'SRID=3812;POINT(651029.676773636 667255.556944161)'),  
(1, 6, '2020-04-21 08:39:08', 'SRID=3812;POINT(651018.401101238 667271.441380755)'),  
(2, 1, '2020-04-21 08:39:29', 'SRID=3812;POINT(651262.17004873  667119.331513367)'),  
(2, 2, '2020-04-21 08:38:36', 'SRID=3812;POINT(651201.431447782 667089.682115196)'),  
(2, 3, '2020-04-21 08:38:43', 'SRID=3812;POINT(651186.853162155 667091.138189286)'),  
(2, 4, '2020-04-21 08:38:49', 'SRID=3812;POINT(651181.995412783 667077.531372716)'),  
(2, 5, '2020-04-21 08:38:56', 'SRID=3812;POINT(651101.820139904 667041.076539663)');  
  
INSERT INTO billboard Values  
(1, 'SRID=3812;POINT(651066.289442793 667213.589577551)'),  
(2, 'SRID=3812;POINT(651110.505092035 667166.698041233)');

該資料庫在下面的地圖中可視化，您可以看到gpsPoint表具有兩個公交車出行的點，藍色出行1和紅色出行2，在表中，每個點都有一個時間戳，這兩個廣告牌是地圖上的灰色菱形，

下一步是查找公交車距廣告牌30米以內的位置，以及持續時間，即移動公交車距廣告牌30米以內的時間，

SELECT tripID, pointID, billboardID  
FROM gpsPoint a, billboard b  
WHERE st_dwithin(a.geom, b.geom, 30);  
  
--1    4    1

上面的此PostGIS查詢不能解決問題，是的，該條款中的條件WHERE可以找到距離廣告牌30米以內的GPS點，但是PostGIS查詢不會告訴您此事件的持續時間，

此外，假設沒有給出行程1（藍色行程）中的點4 ，然后，該查詢將回傳null，該查詢的問題在于，它不處理公交車行程的連續性，即查詢不處理公交車的運動軌跡，

我們需要從給定的GPS點中重建連續的運動軌跡，以下是另一個PostGIS查詢，該查詢既可以找到廣告牌對公交車乘客的可見性的位置，也可以找到廣告牌對公交車乘客可見的持續時間，

WITH pointPair AS(  
  SELECT tripID, pointID AS p1, t AS t1, geom AS geom1,  
    lead(pointID, 1) OVER (PARTITION BY tripID ORDER BY pointID) p2,  
    lead(t, 1) OVER (PARTITION BY tripID ORDER BY pointID) t2,  
    lead(geom, 1) OVER (PARTITION BY tripID ORDER BY pointID) geom2  
  FROM gpsPoint  
), segment AS(  
  SELECT tripID, p1, p2, t1, t2,  
    st_makeline(geom1, geom2) geom  
  FROM pointPair  
  WHERE p2 IS NOT NULL  
), approach AS(  
  SELECT tripID, p1, p2, t1, t2, a.geom,  
    st_intersection(a.geom, st_exteriorRing(st_buffer(b.geom, 30))) visibilityTogglePoint  
  FROM segment a, billboard b  
  WHERE st_dwithin(a.geom, b.geom, 30)  
)  
SELECT tripID, p1, p2, t1, t2, geom, visibilityTogglePoint,  
  (st_lineLocatePoint(geom, visibilityTogglePoint) * (t2 - t1)) + t1 visibilityToggleTime  
FROM approach;

是的，上述PostGIS查詢是一個相當復雜的查詢，我們將查詢分為多個公用表運算式CTE，以使其可讀，在Postgres中，CTE使您能夠“命名”子查詢，從而使撰寫包含多個步驟的SQL查詢更加容易，

pointPair第1-7行中的第一個CTE使用window函式lead，以便將屬于同一總線行程的每對連續點打包到一個元組中，

這是segment第7-12行中第二個CTE的準備作業，然后將兩個點與一個線段相連，此步驟可以看作是每兩個GPS點之間的路徑的線性插值，

這兩個CTE的結果可以在下面的地圖中顯示：

然后，第三個CTE接近12-18行，找到了公交車起/停的位置，離廣告牌30米以內，為此，可以在廣告牌周圍繪制一個直徑30米的圓環，并將其與公交車軌跡的各部分相交，因此，我們在下面的地圖中獲得了用黑叉標記的兩個點，

較早的PostGIS查詢的最后一步，第19-22行，使用線性參考來計算這兩個點的時間，即假設每個線段2的速度恒定，

練習：嘗試找到一種更簡單的方式來表示先前顯示的PostGIS查詢，我不能 :-)
PostGIS查詢必須是如此復雜，因為它撰寫了兩個非平凡的概念：

連續運動軌跡：盡管GPS資料是離散的，但我們必須重建連續運動軌跡，
時空接近度：連續運動軌跡用于查找公交車距廣告牌30米以內的位置和時間（即時空），
給您帶來的好訊息是MobilityDB可以幫助您更輕松地分析這些型別的運動軌跡，MobilityDB是PostgreSQL和PostGIS的擴展，已將這些時空概念實作為Postgres中的自定義型別和功能，

讓我們看看如何使用MobilityDB更簡單地表達此PostGIS查詢，

MobilityDB：用于Postgres和PostGIS的移動物件資料庫系統
這是以前的PostGIS查詢在MobilityDB中的表達方式，

SELECT astext(atperiodset(trip, getTime(atValue(tdwithin(a.trip, b.geom, 30), TRUE))))  
FROM busTrip a, billboard b  
WHERE dwithin(a.trip, b.geom, 30)  
  
--{[POINT(651063.737915354 667183.840879818)@2020-04-21 08:38:12.507515+02,  
    POINT(651052.741845233 667213.026797244)@2020-04-21 08:38:31+02,  
    POINT(651042.581085347 667231.762425657)@2020-04-21 08:38:38.929465+02]}

您需要了解有關上面的MobilityDB查詢的什么：

該表busTrip具有型別為的屬性行tgeompoint，這是用于存盤完整軌跡的MobilityDB型別，
的嵌套tdwithin->atValue->getTime將回傳公交車距廣告牌30米以內的時間段，
該功能atperiodset將僅將總線行程限制在這些時間段內，
該函式astext將輸出中的坐標轉換為文本格式，
因此，結果顯示了公交旅行的一部分，該部分始于2020-04-21 08：38：12.507515 + 02，結束于08：38：38.929465 + 02，
該MobilityDB檔案描述了所有MobilityDB的操作，

現在我們退后一步，并顯示busTrip表的創建，

CREATE EXTENSION MobilityDB CASCADE;  
  
CREATE TABLE busTrip(tripID, trip) AS  
  SELECT tripID,tgeompointseq(array_agg(tgeompointinst(geom, t) ORDER BY t))  
FROM gpsPoint  
GROUP BY tripID;  
  
--SELECT 2  
--Query returned successfully in 78 msec.  
  
  
SELECT tripID, astext(trip) FROM busTrip;  
  
1    "[POINT(651096.993815166 667028.114604598)@2020-04-21 08:37:27+02,  
       POINT(651080.424535144 667123.352304597)@2020-04-21 08:37:39+02,  
       POINT(651067.607438095 667173.570340437)@2020-04-21 08:38:06+02,  
       POINT(651052.741845233 667213.026797244)@2020-04-21 08:38:31+02,  
       POINT(651029.676773636 667255.556944161)@2020-04-21 08:38:49+02,  
       POINT(651018.401101238 667271.441380755)@2020-04-21 08:39:08+02]"  
2    "[POINT(651201.431447782 667089.682115196)@2020-04-21 08:38:36+02,  
       POINT(651186.853162155 667091.138189286)@2020-04-21 08:38:43+02,  
       POINT(651181.995412783 667077.531372716)@2020-04-21 08:38:49+02,  
       POINT(651101.820139904 667041.076539663)@2020-04-21 08:38:56+02,  
       POINT(651262.17004873  667119.331513367)@2020-04-21 08:39:29+02]"

上面的第一步是在資料庫中創建MobilityDB擴展，在Postgres中，該CASCADE選項導致對所有依賴項執行相同的陳述句，在上面的查詢中（因為PostGIS是MobilityDB的依賴項）CASCADE，如果尚未創建PostGIS擴展，還將創建PostGIS擴展，

上面的第二個查詢創建busTrip具有兩個屬性的表(tripID int, trip tgeompoint)，tgeompoint是表示運動軌跡的MobilityDB型別，該tgeompoint屬性是根據時間排序的瞬時陣列構造的，每個瞬時實體都是一對空間點和一個時間戳，在上面的查詢中，通過嵌套來表達這種構造tgeompointinst -> array_agg -> tgeompointseq，

SELECT上面的最后一個查詢顯示該busTrip表包含兩個元組，分別對應于兩個行程，每次旅行都有格式[point1@time1, point2@time2, ...]，

比大象大：當單個Postgres節點無法執行時，如何按比例查詢運動軌跡
由于我們現在有兩種可行的解決方案來衡量廣告牌的可見性：一種是在PostGIS中，另一種是在MobilityDB中，下一步自然是將這些解決方案應用到一個大型資料庫中，該資料庫包含去年布魯塞爾所有公交車次以及布魯塞爾總計約有500萬次公交旅行（約50億個GPS點）和數千個廣告牌，這個大小超出了單個Postgres節點可以處理的大小，因此，我們需要分發Postgres資料庫，

這是Citus的作業，Citus是Postgres的擴展，它將Postgres轉換為分布式資料庫，有效地與許多CTE一起分發復雜的PostGIS查詢是我們要交給Citus工程團隊的挑戰，

我要在這里討論的是MobilityDB查詢的分布，Citus不知道MobilityDB的型別和操作，因此，分發受到Citus通常對自定義型別和功能的限制，我的同事Mohamed Bakli進行了此評估，并在ACM BigSpatial研討會（預印本）的題為“ MobilityDB中的分布式移動物件資料管理”的論文中以及在題為“ MobilityDB中的Distributed Mobility Data Management”的演示論文中發表了此評估， IEEE MDM會議（預印本），

論文提出了使用Citus分發MobilityDB的解決方案，Citus資料庫集群中的所有節點都安裝了PostgreSQL，PostGIS，MobilityDB和Citus，目的是評估MobilityDB中的時空功能可以分布到什么程度，

為了進行此評估，使用了BerlinMOD基準（一種用于比較運動物件資料庫的工具），BerlinMOD由軌跡資料生成器和17個基準測驗查詢組成，這些查詢評估運動物件資料庫系統的功能，無需特殊定制，就可以在由Citus管理的MobilityDB資料庫集群上執行17個BerlinMOD基準查詢中的13個，

另請參閱Nils Dijk撰寫的有關在Citus和Postgres中使用自定義型別的精彩博客文章，

回傳我們的MobilityDB廣告牌可見性查詢，我們的任務是計算布魯塞爾一年中所有廣告牌和所有普通運輸車輛的廣告牌可見性，

我們已經建立了一個Citus資料庫集群，并在其所有節點中創建了MobilityDB擴展，然后，我們使用Cituscreate_distributed_table函式將busTrip表分布在Citus資料庫集群中的所有作業節點上，接下來，我們將布告欄表制作為Citus參考表，然后將參考表復制到所有作業節點，

這是生成的分布式查詢計劃：

EXPLAIN  
SELECT atperiodset(trip, getTime(atValue(tdwithin(a.trip, b.geom, 30), TRUE)))  
FROM busTrip a, billboard b  
WHERE dwithin(a.trip, b.geom, 30);  
  
  
Query plan  
----------------------------------------------------------------------------------------  
Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=100000 width=32)  
  Task Count: 32  
  Tasks Shown: One of 32  
  ->  Task  
      Node: host=10.140.135.15 port=5432 dbname=roma  
      ->  Nested Loop  (cost=0.14..41.75 rows=1 width=32)  
          ->  Seq Scan on provinces_dist_102840 b (cost=0.00..7.15 rows=15 width=32)  
          ->  Index Scan using spgist_bustrip_idx_102808 on bustrip_hash_tripid_102808 a  
              (cost=0.14..2.30 rows=1 width=32)  
              Index Cond: (trip && st_expand(b.geom, '30'::double precision))  
              Filter: _dwithin(trip, b.geom, '30'::double precision)

該西特斯分布式查詢執行并行化在西特斯集群中的所有作業人員查詢，每個節點還具有MobilityDB擴展名，這意味著我們可以dwithin在查詢和索引中使用MobilityDB函式，例如，在這里，我們看到Citus worker上的SP-GiST索參考于有效評估該WHERE dwithin(...)子句，

這樣，我們到了這篇文章的結尾，總結起來，這篇文章有兩個主要內容：

如果您想分析運動軌跡以了解事物在空間和時間上的時空相互作用，那么您現在在Postgres和PostGIS工具箱中有一些新的（開源！）選項：

MobilityDB可以幫助您管理和分析PostgreSQL中的地理空間（例如GPS，雷達）運動軌跡，

MobilityDB + Citus開源可立即使用，因此您也可以大規模分析地理空間運動軌跡，只需將兩個Postgres擴展名（連同PostGIS）一起添加到Postgres資料庫中，就可以管理大型地理空間軌跡資料集了，

腳注

對這些資料的來源感到好奇嗎？軌跡是在布魯塞爾的71號線駛入我的大學校園ULB Solbosch時的軌跡，布魯塞爾的公共交通公司發布了一個開放的API，可以在https://opendata.stib-mivb.be中探測其車輛的所有軌跡，廣告牌位置是我發明的，背景圖來自Openstreetmap，?
它仍然需要計算可見性持續時間，即兩個時間戳之間的秒數差，這可以由另一個CTE和視窗函式來完成，為了不進一步使查詢復雜化，我們在此跳過此細節，

作者丨digoal

本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/Use-Postgres-MobilityDB-and-Citus-to-conduct-large-scale-real-time-analysis-of-GPS-tracks.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/531938.html

標籤：其他

上一篇：PostgreSQL：查詢元資料（表、欄位）資訊、庫表匯入匯出命令

下一篇：MySQL 8.0.30動態redo log初探