我正在使用R和PostgreSQL进行一些轨迹分析。为了形成轨迹段的群组,其中连续位置在时空上靠近,我创建了以下表格。我还缺少的是group_id
列,这就是我的问题所在。
bike_id1 datetime bike_id2 near group_id
1 2016-05-28 11:00:00 2 TRUE 1
1 2016-05-28 11:00:05 2 TRUE 1
1 2016-05-28 11:00:10 2 FALSE NA
[...]
2 2016-05-28 11:00:05 3 TRUE 1
2 2016-05-28 11:00:10 3 TRUE 1
这是每个轨迹与其他所有轨迹(无重复的所有组合)进行多次比较并在
datetime
上进行内部连接(始终在5秒的倍数上采样)的结果。它显示,在某些位置,自行车1和2同时被采样且空间接近(某些任意阈值)。现在,我想为两辆自行车在时空上接近的段落分配唯一的id (
group_id
)。这是我的难点:我希望group_id
尊重具有多个轨迹的组。分配group_id
的方法应该意识到,如果自行车1和2在2016-05-28 11:00:05
的一个组中,则3属于同一组,如果它在同一时间戳(2016-05-28 11:00:05
)附近与2接近。在R或PostgreSQL中是否有工具可帮助我完成此任务?在表中运行循环似乎不是正确的方法。 编辑: 如@wildplasser所指出的那样,这似乎是一个传统上使用SQL解决的间隙和岛屿问题。他友好地提供了一些样本数据,我稍微扩展了一下,并将其包含在问题中。
CREATE TABLE nearness
-- ( seq SERIAL NOT NULL UNIQUE -- surrogate for conveniance
( bike1 INTEGER NOT NULL
, bike2 INTEGER NOT NULL
, stamp timestamp NOT NULL
, near boolean
, PRIMARY KEY(bike1,bike2,stamp)
);
INSERT INTO nearness( bike1,bike2,stamp,near) VALUES
(1,2, '2016-05-28 11:00:00', TRUE)
,(1,2, '2016-05-28 11:00:05', TRUE)
,(1,2, '2016-05-28 11:00:10', TRUE)
,(1,2, '2016-05-28 11:00:20', TRUE) -- <<-- gap here
,(1,2, '2016-05-28 11:00:25', TRUE)
,(1,2, '2016-05-28 11:00:30', FALSE)
,(4,5, '2016-05-28 11:00:00', FALSE)
,(4,5, '2016-05-28 11:00:05', FALSE)
,(4,5, '2016-05-28 11:00:10', TRUE)
,(4,5, '2016-05-28 11:00:15', TRUE)
,(4,5, '2016-05-28 11:00:20', TRUE)
,(2,3, '2016-05-28 11:00:05', TRUE) -- <<-- bike 1, 2, 3 are in one grp @ 11:00:05
,(2,3, '2016-05-28 11:00:10', TRUE) -- <<-- no group here
,(6,7, '2016-05-28 11:00:00', FALSE)
,(6,7, '2016-05-28 11:00:05', FALSE)
;
bike_id
-datetime
组合的情况(特别是因为bike_id
可以位于bike_id1
或bike_id2
列中)。 - Ratnanil