使用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。

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

复制代码

该数据库在下面的地图中可视化。您可以看到gpsPoint表具有两个公交车出行的点，蓝色出行1和红色出行2。在表中，每个点都有一个时间戳。这两个广告牌是地图上的灰色菱形。
下一步是查找公交车距广告牌30米以内的位置，以及持续时间，即移动公交车距广告牌30米以内的时间。

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

复制代码

上面的此PostGIS查询不能解决问题。是的，该条款中的条件WHERE可以找到距离广告牌30米以内的GPS点。但是PostGIS查询不会告诉您此事件的持续时间。
此外，假设没有给出行程1（蓝色行程）中的点4 。然后，该查询将返回null。该查询的问题在于，它不处理公交车行程的连续性，即查询不处理公交车的运动轨迹。
我们需要从给定的GPS点中重建连续的运动轨迹。以下是另一个PostGIS查询，该查询既可以找到广告牌对公交车乘客的可见性的位置，也可以找到广告牌对公交车乘客可见的持续时间。
 

1. WITH pointPair AS(  
2.   SELECT tripID, pointID AS p1, t AS t1, geom AS geom1,  
3.     lead(pointID, 1) OVER (PARTITION BY tripID ORDER BY pointID) p2,  
4.     lead(t, 1) OVER (PARTITION BY tripID ORDER BY pointID) t2,  
5.     lead(geom, 1) OVER (PARTITION BY tripID ORDER BY pointID) geom2  
6.   FROM gpsPoint  
7. ), segment AS(  
8.   SELECT tripID, p1, p2, t1, t2,  
9.     st_makeline(geom1, geom2) geom  
10.   FROM pointPair  
11.   WHERE p2 IS NOT NULL  
12. ), approach AS(  
13.   SELECT tripID, p1, p2, t1, t2, a.geom,  
14.     st_intersection(a.geom, st_exteriorRing(st_buffer(b.geom, 30))) visibilityTogglePoint  
15.   FROM segment a, billboard b  
16.   WHERE st_dwithin(a.geom, b.geom, 30)  
17. )  
18. SELECT tripID, p1, p2, t1, t2, geom, visibilityTogglePoint,  
19.   (st_lineLocatePoint(geom, visibilityTogglePoint) * (t2 - t1)) + t1 visibilityToggleTime  
20. 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中的表达方式。

1. SELECT astext(atperiodset(trip, getTime(atValue(tdwithin(a.trip, b.geom, 30), TRUE))))  
2. FROM busTrip a, billboard b  
3. WHERE dwithin(a.trip, b.geom, 30)  
4.   
5. --{[POINT(651063.737915354 667183.840879818)@2020-04-21 08:38:12.507515+02,  
6.     POINT(651052.741845233 667213.026797244)@2020-04-21 08:38:31+02,  
7.     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表的创建。

1. CREATE EXTENSION MobilityDB CASCADE;  
2.   
3. CREATE TABLE busTrip(tripID, trip) AS  
4.   SELECT tripID,tgeompointseq(array_agg(tgeompointinst(geom, t) ORDER BY t))  
5. FROM gpsPoint  
6. GROUP BY tripID;  
7.   
8. --SELECT 2  
9. --Query returned successfully in 78 msec.  
10.   
11.   
12. SELECT tripID, astext(trip) FROM busTrip;  
13.   
14. 1    "[POINT(651096.993815166 667028.114604598)@2020-04-21 08:37:27+02,  
15.        POINT(651080.424535144 667123.352304597)@2020-04-21 08:37:39+02,  
16.        POINT(651067.607438095 667173.570340437)@2020-04-21 08:38:06+02,  
17.        POINT(651052.741845233 667213.026797244)@2020-04-21 08:38:31+02,  
18.        POINT(651029.676773636 667255.556944161)@2020-04-21 08:38:49+02,  
19.        POINT(651018.401101238 667271.441380755)@2020-04-21 08:39:08+02]"  
20. 2    "[POINT(651201.431447782 667089.682115196)@2020-04-21 08:38:36+02,  
21.        POINT(651186.853162155 667091.138189286)@2020-04-21 08:38:43+02,  
22.        POINT(651181.995412783 667077.531372716)@2020-04-21 08:38:49+02,  
23.        POINT(651101.820139904 667041.076539663)@2020-04-21 08:38:56+02,  
24.        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参考表，然后将参考表复制到所有工作节点。
这是生成的分布式查询计划：

1. EXPLAIN  
2. SELECT atperiodset(trip, getTime(atValue(tdwithin(a.trip, b.geom, 30), TRUE)))  
3. FROM busTrip a, billboard b  
4. WHERE dwithin(a.trip, b.geom, 30);  
5.   
6.   
7. Query plan  
8. ----------------------------------------------------------------------------------------  
9. Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=100000 width=32)  
10.   Task Count: 32  
11.   Tasks Shown: One of 32  
12.   ->  Task  
13.       Node: host=10.140.135.15 port=5432 dbname=roma  
14.       ->  Nested Loop  (cost=0.14..41.75 rows=1 width=32)  
15.           ->  Seq Scan on provinces_dist_102840 b (cost=0.00..7.15 rows=15 width=32)  
16.           ->  Index Scan using spgist_bustrip_idx_102808 on bustrip_hash_tripid_102808 a  
17.               (cost=0.14..2.30 rows=1 width=32)  
18.               Index Cond: (trip && st_expand(b.geom, '30'::double precision))  
19.               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
 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！