背景
隨著移動設備的普及，越來越多的業務具備了時空屬性，例如快遞，試試跟蹤包裹、快遞員位置，例如物體，具備了空間屬性，
例如餐飲配送，送貨員位置屬性，例如車輛，實時位置，等等，
其中兩大需求包括：
1、物件位置實時跟蹤，例如實時查詢某個位點附近、或某個多邊形區域內的送貨員，
2、物件位置軌跡記錄和分析，結合地圖，分析軌跡，結合路由演算法，預測、生成最佳路徑等，
DEMO
以快遞配送為例，GPS設備實時上報快遞員軌跡，寫入位置跟蹤系統，同時將軌跡記錄永久保存到軌跡分析系統，
由于快遞員可能在配送程序中停留時間較長（比如在某個小區配送時），上報的多條位置可能變化并不大，同時考慮到資料庫更新消耗，以及位置的時效性，可以避免一些點的更新（打個比方，上一次位置和當前位置變化量在50米時，不更新），
動態更新可以減少資料庫的更新量，提高整體吞吐能力，
設計
實時位置更新
1、建表
create table t_pos ( uid int primary key, -- 傳感器、快遞員、車輛、，，，物件ID pos point, -- 位置 mod_time timestamp -- 最后修改時間 ); create index idx_t_pos_1 on t_pos using gist (pos);
真實環境中，我們可以使用PostGIS空間資料庫插件，使用geometry資料型別來存盤經緯度點，
create extension postgis; create table t_pos ( uid int primary key, -- 傳感器、快遞員、車輛、，，，物件ID pos geometry, -- 位置 mod_time timestamp -- 最后修改時間 ); create index idx_t_pos_1 on t_pos using gist (pos);
2、上報位置，自動根據移動范圍，更新位置，
例如，移動距離50米以內，不更新，
insert into t_pos values (?, st_setsrid(st_makepoint($lat, $lon), 4326), now()) on conflict (uid) do update set pos=excluded.pos, mod_time=excluded.mod_time where st_distancespheroid(t_pos.pos, excluded.pos, 'SPHEROID["WGS84",6378137,298.257223563]') > ?; -- 超過多宣告不更新
歷史軌跡保存
通常終端會批量上報資料，例如每隔10秒上報10秒內采集的點，一次上報的資料可能包含多個點，在PostgreSQL中可以以陣列存盤，
create table t_pos_hist ( uid int, -- 傳感器、快遞員、車輛、，，，物件ID pos point[], -- 批量上報的位置 crt_time timestamp[] -- 批量上報的時間點 ); create index idx_t_pos_hist_uid on t_pos_hist (uid); -- 物件ID create index idx_t_pos_hist_crt_time on t_pos_hist ((crt_time[1])); -- 對每批資料的起始時間創建索引
有必要的話，可以多存一個時間欄位，用于磁區，
歷史軌跡分析
動態位置變更壓測
寫入并合并，同時判斷當距離大于50時，才更新，否則不更新，
（測驗）如果使用point型別，則使用如下SQL
insert into t_pos values (1, point(1,1), now()) on conflict (uid) do update set pos=excluded.pos, mod_time=excluded.mod_time where t_pos.pos <-> excluded.pos > 50;
（實際生產）如果使用PostGIS的geometry型別，則使用如下SQL
insert into t_pos values (1, st_setsrid(st_makepoint(120, 71), 4326), now()) on conflict (uid) do update set pos=excluded.pos, mod_time=excluded.mod_time where st_distancespheroid(t_pos.pos, excluded.pos, 'SPHEROID["WGS84",6378137,298.257223563]') > 50;
壓測
首先生成1億隨機空間物件資料，
postgres=# insert into t_pos select generate_series(1,100000000), point(random()*10000, random()*10000), now(); INSERT 0 100000000 Time: 250039.193 ms (04:10.039)
壓測腳本如下，1億空間物件，測驗動態更新性能（距離50以內，不更新），
vi test.sql \set uid random(1,100000000) insert into t_pos select uid, point(pos[0]+random()*100-50, pos[1]+random()*100-50), now() from t_pos where uid=:uid on conflict (uid) do update set pos=excluded.pos, mod_time=excluded.mod_time where t_pos.pos <-> excluded.pos > 50;
壓測結果，動態更新 21.6萬點/s，187億點/天，
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120 number of transactions actually processed: 26014936 latency average = 0.295 ms latency stddev = 0.163 ms tps = 216767.645838 (including connections establishing) tps = 216786.403543 (excluding connections establishing)
軌跡寫入壓測
每個UID，每批寫入50條：寫入速度約 467.5萬點/s，4039億點/天，
壓測時，寫多表，壓測使用動態SQL，
do language plpgsql $$ declare begin for i in 0..127 loop execute 'create table t_pos_hist'||i||' (like t_pos_hist including all)'; end loop; end; $$;
create or replace function import_test(int) returns void as $$ declare begin execute format('insert into t_pos_hist%s values (%s, %L, %L)', mod($1, 128), $1, array[point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1), point(1,1)] , array['2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10', '2018-01-01 10:10:10']); end; $$ language plpgsql strict;
vi test1.sql \set uid random(1,100000000) select import_test(:uid);
pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 56 -j 56 -T 120 number of transactions actually processed: 11220725 latency average = 0.599 ms latency stddev = 5.452 ms tps = 93504.532256 (including connections establishing) tps = 93512.274135 (excluding connections establishing)
黑科技
1、塊級索引（BRIN），在時序屬性欄位上，建立塊級索引，既能達到高效檢索目的，又能節約索引空間，還能加速寫入，
《PostgreSQL BRIN索引的pages_per_range選項優化與內核代碼優化思考》
《萬億級電商廣告 - brin黑科技帶你(最低成本)玩轉毫秒級圈人(視覺挖掘姊妹篇) - 阿里云RDS PostgreSQL, HybridDB for PostgreSQL最佳實踐》
《PostGIS空間索引(GiST、BRIN、R-Tree)選擇、優化 - 阿里云RDS PostgreSQL最佳實踐》
《自動選擇正確索引訪問介面(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》
《PostgreSQL 并行寫入堆表，如何保證時序線性存盤 - BRIN索引優化》
《PostgreSQL 9種索引的原理和應用場景》
2、阿里云HDB PG特性：sort key , metascan
與BRIN類似，適合線性資料，自動建立塊級元資料(取值范圍、平均值、CNT、SUM等)進行過濾，
3、空間索引
GiST, SP-GiST空間索引，適合空間資料、以及其他異構資料，
4、動態合并寫，根據位置變化量，自動判斷是否需要合并更新，
insert on conflict語法，在do update里面，可以進行條件過濾，當位置變化超過N米時，才進行更新，
5、陣列、JSON、KV等多值型別，
特別適合多值屬性，例如批量上傳的軌跡，通常GPS終端上報位置并不是實時的，可能存在一定的 延遲（例如批量上報），使用陣列、JSON都可以存盤，
如果使用陣列存盤，將來分析軌跡時，依舊可以unnest解開，繪制軌跡，
性能
1、動態位置變更：1億被跟蹤物件，TPS：21.6萬，動態更新21.6萬點/s，187億點/天，
2、軌跡寫入：tps約10萬，寫入467.5萬點/s，4039億點/天，
參考
《PostGIS 空間資料學習建議》
《PostgreSQL + PostGIS + SFCGAL 優雅的處理3D資料》
《PostGIS 距離計算建議 - 投影 與 球 坐標系, geometry 與 geography 型別》
《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(適合新用戶)》
《PostGIS 空間索引(GiST、BRIN、R-Tree)選擇、優化 - 阿里云RDS PostgreSQL最佳實踐》
《PostGIS 坐標轉換(SRID)的邊界問題引發的專業知識 - ST_Transform》
《無人駕駛背后的技術 - PostGIS點云(pointcloud)應用 - 2》
《無人駕駛背后的技術 - PostGIS點云(pointcloud)應用 - 1》
《geohash vs PostGIS》
《視覺挖掘與PostGIS空間資料庫的完美邂逅 - 廣告營銷\圈人》
《PostGIS 點面疊加視覺判斷輸出》
《PostGIS 多點幾何型別 空字符構造例外CASE》
《開放地圖OpenStreetMap與PostGIS的三生三世十里桃花》
《PostGIS 地理資訊、柵格資料 多核并行處理(st_memunion, st_union)》
《蜂巢的藝術與技術價值 - PostgreSQL PostGIS's hex-grid》
《如何建立GIS測驗環境 - 將openstreetmap的樣本資料匯入PostgreSQL PostGIS庫》
《GIS附近查找性能優化 - PostGIS long lat geometry distance search tuning using gist knn function》
作者丨digoal本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/PostgreSQL_real-time_position_tracking-trajectory_analysis_system_practice.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/532628.html
標籤：其他
上一篇：第七章-概念結構設計