背景
PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理，PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出，将数据变成生产力。
本文将介绍PolarDB 开源版 轨迹应用实践，例如：
出行、配送、快递等业务的调度
快递员有预规划的配送轨迹(轨迹)
客户有发货需求(时间、位置)
根据轨迹估算最近的位置和时间
通过多个嫌疑人的轨迹，计算嫌疑人接触的地点、时间点
根据轨迹, 对传染源进行溯源
测试环境为macOS+docker，PolarDB部署请参考如何用 PolarDB 证明巴菲特的投资理念 - 包括PolarDB简单部署。
轨迹介绍
轨迹的定义：
位置1、位置2、...位置N 组成的线段, 加上 开始时间、结束时间
轨迹的常见计算：
两个轨迹何时最接近
最近的距离是多少
两个轨迹最近时的位置分别是什么
相关函数
https://postgis.net/docs/manual-3.3/reference.html#Temporal
8.18. Linear Referencing
ST_LineInterpolatePoint — Returns a point interpolated along a line at a fractional location.
ST_3DLineInterpolatePoint — Returns a point interpolated along a 3D line at a fractional location.
ST_LineInterpolatePoints — Returns points interpolated along a line at a fractional interval.
ST_LineLocatePoint — Returns the fractional location of the closest point on a line to a point.
ST_LineSubstring — Returns the part of a line between two fractional locations.
ST_LocateAlong — Returns the point(s) on a geometry that match a measure value.
ST_LocateBetween — Returns the portions of a geometry that match a measure range.
ST_LocateBetweenElevations — Returns the portions of a geometry that lie in an elevation (Z) range.
ST_InterpolatePoint — Returns the interpolated measure of a geometry closest to a point.
ST_AddMeasure — Interpolates measures along a linear geometry.
8.19. Trajectory Functions Abstract These functions support working with trajectories. A trajectory is a linear geometry with increasing measures (M value) on each coordinate. Spatio-temporal data can be modeled by using relative times (such as the epoch) as the measure values.
ST_IsValidTrajectory — Tests if the geometry is a valid trajectory.
ST_ClosestPointOfApproach — Returns a measure at the closest point of approach of two trajectories.
ST_DistanceCPA — Returns the distance between the closest point of approach of two trajectories.
ST_CPAWithin — Tests if the closest point of approach of two trajectories is within the specified distance.
轨迹计算举例
构造3维轨迹：
ST_AddMeasure('LINESTRING Z (0 0 0, 10 0 5, 1 1 1)'::geometry, -- 三个3维点
extract(epoch from '2015-05-26 10:00'::timestamptz), -- 开始时间
extract(epoch from '2015-05-26 11:00'::timestamptz) -- 结束时间
) 构造2维轨迹：
ST_AddMeasure('LINESTRING (0 0, 10 0, 1 1)'::geometry, -- 三个2维点
extract(epoch from '2015-05-26 10:00'::timestamptz), -- 开始时间
extract(epoch from '2015-05-26 11:00'::timestamptz) -- 结束时间
) 返回2条轨迹距离最接近时的第一个时间点(因为2条轨迹可能有多个时间处于最近距离, 但是这里只返回最早的时间点, 如果要求后面的时间点, 可以切分线段)。
两个轨迹何时最接近
最近的距离是多少
两个轨迹最近时的位置分别是什么
-- Return the time in which two objects moving between 10:00 and 11:00
-- are closest to each other and their distance at that point
WITH inp AS ( SELECT
ST_AddMeasure('LINESTRING Z (0 0 0, 10 0 5)'::geometry, -- 如果轨迹是一个点, 这里就直接填2个一样位置的点
extract(epoch from '2015-05-26 10:00'::timestamptz),
extract(epoch from '2015-05-26 11:00'::timestamptz)
) a,
ST_AddMeasure('LINESTRING Z (0 2 10, 12 1 2, 15 3 5)'::geometry, -- 两条轨迹的点数可以不一样
extract(epoch from '2015-05-26 10:00'::timestamptz),
extract(epoch from '2015-05-26 11:00'::timestamptz)
) b
), cpa AS (
SELECT ST_ClosestPointOfApproach(a,b) m FROM inp -- 计算a,b 2条轨迹距离最近时的最早时间点
), points AS (
SELECT ST_Force3DZ(ST_GeometryN(ST_LocateAlong(a,m),1)) pa, -- ST_LocateAlong(a,m) 计算a轨迹在某个时间点m对应的位置点(集合点)
ST_Force3DZ(ST_GeometryN(ST_LocateAlong(b,m),1)) pb -- ST_GeometryN 返回集合的第一个点, 由于a,b线段是3维线段, 所以返回后需要再使用ST_Force3DZ格式化一下?
FROM inp, cpa
)
SELECT st_astext(pa) pa, st_astext(pb) pb,
to_timestamp(m) t, -- a,b线段距离最近时的最早的时间点m
ST_Distance(pa,pb) distance -- a,b线段最接近的pa,pb点的距离
FROM points, cpa;
pa | pb | t | distance
-------------------------------------------------+----------------------------------------------------------------+-------------------------------+------------------
POINT Z (5.798478121227689 0 2.899239060613844) | POINT Z (9.041623081002845 1.24653140991643 3.972251279331437) | 2015-05-26 10:34:47.452124+00 | 3.47445388313376
(1 row) 以上SQL应用场景举例：
出行、配送、快递等业务的调度, 例如
快递员预规划的配送轨迹(轨迹a)
客户有发货需求(时间、位置)(轨迹b)
多个嫌疑人的轨迹
计算嫌疑人接触的地点、时间点
根据传染病人的多人多轨迹进行轨迹的碰撞计算, 对传染源进行溯源追踪
