24.1. 什么是时空碰撞

以疫情为例,我们需要排查出密切接触者

1:一名携带者每天会途径多个不同的位置,这个位置我们用经纬度+时间表示,每分钟记录一个点位

2:我们可以按照经纬度+时间与这名携带者匹配,如果我们在某些时空的位置上有重叠,且重叠次数超过N次(如30分钟),这称为密切接触者.

因此时空碰撞是 按某一人/物 照时空位置进行匹配,如果匹配次数超过设定的阈值,则返回匹配的实体ID的过程.

24.2. 建表示例

CREATE FOREIGN TABLE test(
 jwd float8[] options(store 'idsg2'),
 queryscore float8,
 entityId text  OPTIONS(key 'unique'),
 upmode text
 ) SERVER lxdb options(store 'ids');

24.3. 具体选项的解释

24.4. 入库例子

INSERT INTO test(jwd, entityId, upmode) VALUES
(ARRAY[115.37,26.96,1,101,118.07,29.27,0,102,116.96,27.47,1,103,117.86,27.99,5,104],'0号','incr'),
(ARRAY[116.37,27.96,0,106,119.07,30.27,2,107,115.96,26.47,3,108,116.86,26.99,5,109],'1号','incr'),
(ARRAY[115.37,26.96,7,111,120.07,31.27,6,112,116.96,27.47,4,113,115.86,25.99,7,114],'2号','incr'),
(ARRAY[116.37,27.96,4,116,119.07,30.27,3,117,117.96,28.47,2,118,114.86,24.99,5,119],'3号','incr'),
(ARRAY[115.37,26.96,5,121,118.07,29.27,5,122,116.96,27.47,3,123,115.86,25.99,0,124],'4号','incr'),
(ARRAY[116.37,27.96,2,126,117.07,28.27,5,127,115.96,26.47,1,128,114.86,24.99,5,129],'5号','incr'),
(ARRAY[117.37,28.96,4,131,118.07,29.27,0,132,114.96,25.47,1,133,113.86,23.99,3,134],'6号','incr'),
(ARRAY[118.37,29.96,0,136,117.07,28.27,5,137,115.96,26.47,0,138,112.86,22.99,5,139],'7号','incr'),
(ARRAY[117.37,28.96,1,141,116.07,27.27,1,142,114.96,25.47,5,143,113.86,23.99,0,144],'8号','incr'),
(ARRAY[118.37,29.96,4,146,115.07,26.27,5,147,113.96,24.47,1,148,112.86,22.99,1,149],'9号','incr'),
(ARRAY[119.37,30.96,2,151,114.07,25.27,3,152,114.96,25.47,0,153,113.86,23.99,6,154],'10号','incr');

24.5. 入库例子的解释

24.6. 查询

select entityid,queryscore,jwd from test where jwd @> 
spatial(
array[array[115.37,26.96,1,101]::float8[],
array[118.07,29.27,0,102]::float8[],
array[116.96,27.47,1,103]::float8[],
array[117.86,27.99,5,104]::float8[]
]::float8[],array[100000,5,70,3]::float8[]);

24.7. 查询语句的解释

24.8. 采用累加更新的方式追加轨迹点

INSERT INTO test(jwd, entityId, upmode) VALUES
(ARRAY[115.37,26.96,1,201,118.07,29.27,0,202,116.96,27.47,1,203,117.86,27.99,5,204],'0号','incr')

24.9. 按照匹配度排序

select entityid,queryscore,jwd from test where jwd @> ARRAY[0,115.37,26.96,1,101,118.07,29.27,0,102,116.96,27.47,1,103,117.86,27.99,5,104,115.37,26.96,1,201,118.07,29.27,0,202,116.96,27.47,1,203,117.86,27.99,5,204,100000,5,70,1]::float8[] 
order by **queryscore** desc limit 100 ;