閱讀886 返回首頁    go 阿裏雲 go 技術社區[雲棲]

PostgreSQL 空間獨立事件相關性分析 二 - 人車擬合

標簽

PostgreSQL , PostGIS , 人車擬合

背景

獨立事件相關性分析是一件很有意思的事情，例如

探探軟件的擦肩而過功能點，在不同時空與你擦肩而過的人。

輿情分析。

商品最佳銷售組合。

安全係統中的人車擬合，對時空數據進行處理，用於司機、乘客、車輛的擬合。

人車擬合

1、建立表結構

人

create table u_pos (  
  id int8,  
  uid int8,  
  crt_time timestamp,  
  pos geometry  
);

車

create table c_pos (  
  id int8,  
  car_id int8,  
  crt_time timestamp,  
  pos geometry  
);

2、生成測試數據。

以杭州市為例，經緯度範圍如下：

東經118°21′-120°30′，北緯29°11′-30°33′ 計算得東經118.35°-120.5°，北緯29.183°-30.55°。

活躍量假設：

1000萬人口，1000萬車輛。  
  
人的軌跡數，一天10億。  
  
車的軌跡數，一天1億。

2.1、寫入人的活動位置數據，按天分區，保留一年。

for ((i=1;i<=32;i++))  
do  
nohup psql -c "insert into u_pos select id, random()*10000000, '2017-10-01'::date + ((id*2.7648)||' ms')::interval, st_setsrid(st_makepoint(118.35+random()*2.15, 29.183+random()*1.367), 4326) from generate_series(1,31250000) t(id);" >/dev/null 2>&1 &  
done

采用時序數據中最常用的brin索引。

create index idx_u_pos_1 on u_pos using brin(crt_time);

建立人+時間的索引。

create index idx_u_pos_2 on u_pos using btree(uid, crt_time);

2.2、寫入車輛的活動位置數據，按天分區，保留一年。

for ((i=1;i<=32;i++))  
do  
nohup psql -c "insert into c_pos select id, random()*10000000, '2017-10-01'::date + ((id*27.648)||' ms')::interval, st_setsrid(st_makepoint(118.35+random()*2.15, 29.183+random()*1.367), 4326) from generate_series(1,3125000) t(id);" >/dev/null 2>&1 &  
done

采用時序數據中最常用的brin索引。

create index idx_c_pos_1 on c_pos using brin(crt_time);

建立車+時間的索引。

create index idx_c_pos_2 on c_pos using btree(car_id, crt_time);

3、求某個時間區間的人車擬合

3.1、車輛，行駛過程中抓到的N個點，返回時間，位置。

select pos, crt_time from c_pos where car_id=? and crt_time between ? and ?;

返回對應時間區間的N個點附近的人交集

create or replace function merge_car_u(  
  v_car_id int8,       -- 汽車ID  
  s_time timestamp,    -- 搜索範圍，開始時間  
  e_time timestamp,    -- 搜索範圍，結束時間  
  ts_range interval,   -- 每個汽車軌跡點對應的：目標人出現的時間與汽車出現時間的時間差（前後各放大多少）  
  pos_range float8     -- 每個汽車軌跡點對應的：目標人與汽車的距離  
) returns int8[] as $$  
declare  
  res int8[];  
  tmp int8[];  
  v_pos geometry;  
  v_crt_time timestamp;  
  i int := 0;  
begin  
  for v_pos, v_crt_time in select pos, crt_time from c_pos where car_id=v_car_id and crt_time between s_time and e_time  -- 求軌跡點  
  loop  
    select array_agg(uid) into tmp from u_pos where crt_time between v_crt_time-ts_range and v_crt_time+ts_range and (v_pos <-> pos) < pos_range;  -- 求對應目標的ID  
    if (i <> 0) then  
      select array_agg(unnest) into res from (select unnest(res) intersect select unnest(tmp)) t;  -- 求交集  
    else  
      res := tmp;  
    end if;  
    i := i+1;  
  end loop;  
  return res;  
end;  
$$ language plpgsql strict;

例子：

postgres=# select * from merge_car_u(1, '2017-10-01 01:00:00', '2017-10-01 04:00:00', '10 s', 0.004);  
            merge_car_u              
-----------------------------------  
 {5481974,5958009,3682524,1313466}  
(1 row)  
  
Time: 232.960 ms

3.2、人，運動過程中抓到的N個點，返回時間，位置。

返回對應時間區間的N個點附近的車輛的交集

create or replace function merge_u_car(  
  v_uid int8,              -- 人ID  
  s_time timestamp,        -- 搜索範圍，開始時間  
  e_time timestamp,        -- 搜索範圍，結束時間  
  ts_range interval,       -- 每個人軌跡點對應的：目標車輛出現的時間與人出現時間的時間差（前後各放大多少）  
  pos_range float8         -- 每個人軌跡點對應的：目標車輛與人的距離  
) returns int8[] as $$  
declare  
  res int8[];  
  tmp int8[];  
  v_pos geometry;  
  v_crt_time timestamp;  
  i int := 0;  
begin  
  for v_pos, v_crt_time in select pos, crt_time from u_pos where uid=v_uid and crt_time between s_time and e_time  -- 求軌跡點  
  loop  
    select array_agg(car_id) into tmp from c_pos where crt_time between v_crt_time-ts_range and v_crt_time+ts_range and (v_pos <-> pos) < pos_range;  -- 求對應目標的ID  
    if (i <> 0) then  
      select array_agg(unnest) into res from (select unnest(res) intersect select unnest(tmp)) t;  -- 求交集  
    else  
      res := tmp;  
    end if;  
    i := i+1;  
  end loop;  
  return res;  
end;  
$$ language plpgsql strict;

例子：

postgres=# select * from merge_u_car(100, '2017-10-01 01:00:00', '2017-10-01 02:00:00', '100 s', 0.2);  
                                                merge_u_car                                                  
-----------------------------------------------------------------------------------------------------------  
 {6214562,6180159,4534165,7824219,6826437,3020910,1463798,2939986,5786345,7233751,2856178,1719127,7763683}  
(1 row)  
  
Time: 96.986 ms

小結

1、存儲、索引優化思路。

時間截斷 + 空間排序 存儲

例如

(YYYY-MM-DD HH24:MI), (geohash)

存儲修整後，建立以上結構的btree或BRIN索引。

當搜索某個時間點，出現在某個點附近的記錄時，可以並行，並且搜索的數據塊是比較少的，因為密集存儲。

2、其他需求：缺失位置的補齊。某些情況下，可能導致車輛、人的位置信息未采集的情況，例如經過擁堵路段、采集設備死角等。

在位置獲取出現空缺的情況下，使用pgrouting，以及路網信息，生成若幹條路徑，補齊為出現的點。同時估算時間，得到點和經過的時間。

3、其他需求：異常位置糾正。

4、擬合性能，以天為分區。1000萬人口，1000萬車輛。人的軌跡數，一天10億。車的軌跡數，一天1億。

可以做到毫秒級別的擬合響應。

參考

《潘金蓮改變了曆史之 - PostgreSQL輿情事件分析應用》

《為什麼啤酒和紙尿褲最搭 - 用HybridDB/PostgreSQL查詢商品營銷最佳組合》

最後更新：2017-10-28 23:04:05

  上一篇：go  PostgreSQL 空間st_contains，st_within空間包含搜索優化 - 降IO和降CPU(bound box)
  下一篇：go  18項火眼金睛 - 評估一款數據庫