利用Postgres进行地理空间数据分析

帕克纳夫的很大一部分业务包括收集停车信息，并验证我们在现实世界中的人工智能街道停车预测。我们的数据收集团队成员使用我们的数据收集应用程序武装自己，步行、骑自行车和开车进城。这款应用程序持续跟踪和记录他们的GPS位置，将其与他们所在的街道进行匹配，并允许输入与他们当前位置相关的不同类型的信息-从停车限制到街道上可用停车点的数量。

结果，我们在Postgres数据库中得到了大量的GPS位置，每个位置代表我们的收集器在城市中导航时所走的路径上的一个点。随着时间的推移，我们提出了许多查询，使我们能够快速验证和分析这些结果路径，我们认为它们对使用类似数据集的其他人将很有价值。同样的分析也适用于来自逐个转弯导航应用程序、FCD(浮动车数据)、您的跑步/自行车应用程序和许多其他应用程序的数据。

假设记录的收集器路径上的地理空间数据存储在如下所示的表中：

CREATE TABLE gpspoint(id uuid约束gpspoint_pkey主键，date_time bigint NOT NULL，GEOM GEOMETRY(point，3857)NOT NULL，path_id UUID NOT NULL，COLLECTER_ID UUID NOT NULL，.)；

其中date_time以毫秒为单位，表示记录点的日期和时间，geom是具有EPSG：3857投影的笛卡尔几何，path_id是同一收集器路径(TRIP)中所有点的相同ID。

我们通常使用几何而不是地理来存储地理空间坐标-笛卡尔数学对于我们的用例来说要快得多。这意味着纬度和经度最终被投影到一个平面上，这会导致一些变形，具体取决于所选的投影。对于我们在其中运营的每个城市，我们使用单独的数据库，这使得数据库中任意两点之间的失真非常小，特别是在使用绑定到特定城市或州的“本地”投影，而不是像ESPG：3857(Web墨卡托)这样的全球投影时。但是，这确实会使查询变得稍微复杂一些，如您将在下面看到的。

通过查看连续GPS点之间的时间和空间距离，我们可以了解哪些路径的GPS记录可能有问题-例如，GPS信号丢失、数据采集器正在通过隧道等。在一个完美的记录中，空间和时间差异都很小，时间越长，遇到的GPS问题就越多。我们的目标是找到任意两个连续GPS点之间的距离或时间差大于正常距离的路径。

Postgres中的窗口函数在这方面有很大帮助。它们允许我们对表行的分区进行操作(类似于GROUP BY)，但仍保留输出中的所有行，而不是返回一个聚合的输出行。同时，我们可以对分区中的元素进行排序，并获得分区中每一行的相邻行。对于我们来说，分区是自然的-按path_id，因此每个收集器路径最终都在一个单独的分区中。

我们可以分析整个表，并按连续点之间的最大空间或时间差对路径进行排序，如下所示：

SELECT MIN(OBS_TIME)：：TIMESTAMP(0)AS STARTED_ON，MAX(OBS_TIME)：：TIMESTAMP(0)AS Finish_ON，ROUND(max(Distance_Delta))AS max_Distance_Delta，max(Time_Delta)/1000 AS max_time_Delta，path_id from(SELECT path_id，to_Timestamp(Date_Time/1000)AS OBS_Time，ST_Distance(geom，lag(Geom)over w)AS Distance_Delta，来自gpspoint窗口的w上的date_time-lag(Date_Time)as time_Delta(as(Partition By Path_Id Order By Date_Time Asc)deltas，其中Distance_Delta>；0按路径id分组按最大距离增量描述排序；

INTERNAL SELECT语句创建相同path_id内的所有点的窗口w-本质上是以记录它们的相同顺序属于同一路径的所有点。这是通过(PARTITION BY PATH_ID ORDER BY DATE_TIME ASC)实现的。然后，我们使用lag()函数获取用于计算每对行之间的空间和时间差的前一行。最后，我们再次按path_id对结果进行分组，并输出路径记录开始的时间、结束的时间以及任意两个连续GPS点之间的最大空间和时间增量。这可以让我们的数据分析师立即查看是否有任何路径存在任何重大问题。

这里产生的max_Distance_Delta将以EPSG表示：3857“米”。很多时候，它对于内部分析已经足够好了，但是对于需要以实际米为单位报告距离的情况，我们必须计算出笛卡尔投影和ESPG之间的畸变率：4326(纬度/经度)。这可以通过从我们的GPS点表中随机抽取一个点(这是可行的，因为我们每个城市都有一个单独的数据库，这样点就不会散布到世界各地)，在EPSG：4326中将其移动一米，然后查看ESPG：3857中有什么不同。最后，我们将该比率应用于ESPG：3857查询报告的距离，以获得最终结果。

失真为(SELECT ST_DISTANCE(GEOM，st_Transform(st_project(st_Transform(geom，4326)，1，0)：：Geometry，3857))as m_Distance from gppoint Limit 1)select min(Obs_Time)：：timeamp(0)as start_on，max(Obs_Time)：：timeamp(0)as fined_on，round(max(Distance_Delta/m_Distance))as max_Distance_Delta，max(Time_Delta)/1000。path_id from(select path_id，to_timeamp(date_time/1000)as obs_time，st_Distance(geom，lag(Geom)over w)as Distance_Delta，date_time-lag(Date_Time)over w as time_Delta from gppoint window w as(Partition By Path_Id Order By Date_Time Asc))增量，失真WHERE Distance_Delta>；0按路径id分组按最大距离增量描述排序；