基於時間和距離的連接表：PostGIS 與 PostgreSQL？

我有一個包含數千行的表，其中包含時間和位置數據。具有相同 group_id 的行具有相同的時間戳。讓我們將此表稱為“小”：

Small Table:
id  event_time                   group_id   item_id  position
1   '2018-06-21 18:35:01.631094+00' '123a'  1   '01010230...'
2   '2018-06-21 18:35:01.630881+00' '123a'  2   '01010044...'
3   '2018-06-18 10:35:01.630663+00' '321b'  1   '01015600...'
4   '2018-06-18 10:35:01.630305+00' '321b'  2   '01010031...'

我有另一個表（表“大”），它具有相似的數據列（時間、位置、data1、data2 等）。此表中的時間戳是連續的，並且與第一個表重疊，並且有 80+ 百萬行：

Big Table:
id  event_time                       Data1   position
1   '2018-06-21 18:45:01.631094+00' 'john'  '01013000...'
2   '2018-06-21 18:41:01.630881+00' 'joe'   '01016000...'
3   '2018-06-21 18:33:01.630663+00' 'john'  '01017000...'
4   '2018-06-21 18:30:01.630305+00' 'rory'  '01018000...'

我在兩張表上有地理空間和時間索引。

我想做的是找到 Big 和 Small 之間最接近的匹配項，並返回 data1、data2 以及空間和時間的差異。簡而言之，我想知道“john”是“123a”組第 2 項的最佳匹配，他距離它 100m 和 2 分鐘，但“rory”最接近第 1 項（5 分鐘和 1 公里或任何）。

我已經嘗試過類似的命令，但它太慢了。它似乎沒有使用索引。

SELECT 
 big.id, 
 small.id,
 st_distance(big.position, small.position) as pos_delta,
 (big.event_time, small.event_time) as time_delta,
 big.data1,
 small.item_id
FROM big, small 
WHERE
 (big.event_time - small.event_time) < '2 hours'
ORDER BY login_sar_vessel.position <-> login_pos_report.position
LIMIT 1

有沒有辦法可以先從與 group_id 關聯的時間戳周圍的大表中選擇 2 小時的數據，找到距離最小（big.position 到 small.position）距離的大行，然後為每個 group_id 重複？這似乎有點亂。

哦，數據庫是 postgres 9.6 和 postgis 2.4。

我想你想要這樣的東西，

SELECT
 big.id,
 small.id,
 ST_Distance(big.geom, small.geom),
 big.event_time <-> small.event_time
FROM small
CROSS JOIN LATERAL (
 SELECT *
 FROM big
 WHERE (small.event_time <-> big.event_time) < '2 hours'::interval
 ORDER BY small.geom <-> big.geom
 OFFSET 0
 LIMIT 1
) AS big;

您可以使用btree_gist將這兩個都放在時間圖和 geom 上以獲得額外的樂趣

CREATE EXTENSION btree_gist;

CREATE INDEX ON big USING gist (event_time, geom);
CREATE INDEX ON small USING gist (event_time, geom);
VACUUM FULL ANALYZE big;
VACUUM FULL ANALYZE small;

注意：我會升級到 PostgreSQL 10，這樣你就有了並行的東西，因為它會對這個工作負載產生重大影響。

您還可以考慮時間戳上的 BRIN 索引，並按時間戳進行分群。完成後進入 GIS。或者甚至MATERIALIZED VIEW只使用最後一周的數據或其他數據創建一個新的索引。

引用自：https://dba.stackexchange.com/questions/210349