使用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/ ...

背景

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