如何從另一個表中選擇“最接近”的行？

我有兩個表，map1並且，列和map2之間有多種可能的組合。map1.id1``map2.id2

我嘗試了以下查詢：

SELECT map1.id1, map2.id2, MIN(ST_HausdorffDistance(map1.g1, map2.g2)),map2.g2
FROM map1, map2
WHERE ST_HausdorffDistance(map1.g1, map2.g2) < 2
GROUP BY map1.id1,map2.g2,map2.id2, ST_HausdorffDistance(map1.g1, map2.g2)
ORDER BY map1.id1,map2.id2, ST_HausdorffDistance(map1.g1, map2.g2)

電流輸出

多行id1：

id1   id2   min               g2
----  ----  ----------------  ----------------------------------------------------------------------------------------------------------------------------
6116   338  1.8122154049353   "0102000020E610000002000000590E3EDEF5AB23409255B6B4BF1B4A4031197DBBDBAB23404AF663EEB51B4A40"
6116   645  1.82162999509807  "0102000020E61000000300000057900B73277D2340A1675831011E4A4094C55801197D23406A204C40021E4A40B0CFF7AE9C7C234079AAE8B4131E4A40"
6116   674  1.82397666934862  "0102000020E610000002000000AC0E6F8C53B723405B80118F1F1E4A404EFF48C78BB723406C9159620A1E4A40"
 65   695  1.22999509807     "01456020E61000000300000057900B73277D2340A1675831011E4A4094C55801197D23406A204C40021E4A40B0CFF7AE9C7C234079AAE8B4131E4A40"
 65   689  1.556666934862    "0202000020E610000002000000AC0E6F8C53B723405B80118F1F1E4A404EFF48C78BB723406C9159620A1E4A40"
--  many more ...

期望的輸出

我只想為每個選擇 1st 1 或 2 行id1-由最小Hausdorff 距離定義：

id1   id2   min               g2
----  ----  ----------------  --------------------------------------------------------------------------------------------------------------------------
6116   338  1.8122154049353   "0102000020E610000002000000590E3EDEF5AB23409255B6B4BF1B4A4031197DBBDBAB23404AF663EEB51B4A40"
6116   645  1.82162999509807  "0102000020E61000000300000057900B73277D2340A1675831011E4A4094C55801197D23406A204C40021E4A40B0CFF7AE9C7C234079AAE8B4131E4A40"
 65   695  1.22999509807     "01456020E61000000300000057900B73277D2340A1675831011E4A4094C55801197D23406A204C40021E4A40B0CFF7AE9C7C234079AAE8B4131E4A40"
 65   689  1.556666934862    "0202000020E610000002000000AC0E6F8C53B723405B80118F1F1E4A404EFF48C78BB723406C9159620A1E4A40"

gis.SE 上的相關答案以說明“豪斯多夫距離”一詞：

• 兩條或多條軌蹟之間的相似性

這將實現它：

SELECT m1.id1, m2.*
FROM   map1 m1
CROSS  JOIN LATERAL (
  SELECT ST_HausdorffDistance(m1.g1, m2.g2) AS h_dist, m2.id2, m2.g2
  FROM   map2 m2
  WHERE  ST_HausdorffDistance(m1.g1, m2.g2) < 2
  ORDER  BY 1, 2
  LIMIT  2
  ) m2;

為 in 中的每一行返回 1 或 2 行map1，並與 in 中的前 2 個對應行（map2由最小 Hausdorff 距離定義）和它們之間的所述 Hausdorff 距離一起擴展。如果沒有 Hausdorff 距離 < 2 in 的map2行，則不返回任何行。

關鍵元素是LATERAL子查詢。此查詢有多種變體，具體取決於確切（缺失）的要求。有關的：

• 複雜查詢的 OFFSET 和 LIMIT
• 如何在 PostgreSQL 中使 DISTINCT ON 更快？
• 如何加快查詢時間序列中的最後一個值？
• 優化大表上的 LATERAL JOIN 查詢

我不知道在這裡使用索引的任何方法。所以這將是一個昂貴的查詢。

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