29. 最鄰近搜尋¶
29.1. 什麼是最鄰近搜尋？¶
一個常見的空間查詢是：「<候選特徵> 中哪個最鄰近 <查詢特徵>？」
與距離搜尋不同，最鄰近搜尋不包括任何限制候選幾何可能有多遠的測量值，只要候選幾何是 最鄰近 的，則將接受任何距離的特徵。
PostgreSQL 透過使用「order by distance」（<->）運算子來解決最鄰近搜尋問題，此運算子將資料庫引導至使用索引來加速分類回傳資料集。有了「order by distance」運算子，最鄰近搜尋查詢只要新增排序並限制回傳資料集至 N 項，即可回傳「N 個最鄰近特徵」。
「order by distance」運算子適用於幾何和地理類型。兩者在運作方式上的唯一不同是回傳的距離值。對於幾何，<-> 的回傳值與 ST_Distance 相同，取決於所使用空間參考系統的單位。而對於地理資料，回傳的距離值是球面距離，而不是 ST_Distance(geography,geography) 回傳的更精確球形距離。
以下為三個最鄰近「Broad St」地鐵站的道路
-- Get the geometry of Broad St
SELECT ST_AsEWKT(geom, 1)
FROM nyc_subway_stations
WHERE name = 'Broad St';
SRID=26918;POINT(583571.9 4506714.3)
-- Plug the geometry into a nearest-neighbor query
SELECT streets.gid, streets.name,
ST_Transform(streets.geom, 4326),
streets.geom <-> 'SRID=26918;POINT(583571.9 4506714.3)'::geometry AS dist
FROM
nyc_streets streets
ORDER BY
dist
LIMIT 3;
gid | name | dist
-------+-----------+--------------------
17385 | Wall St | 0.749987508809928
17390 | Broad St | 0.8836306235191059
17436 | Nassau St | 1.3368280241070414
我們如何確定能得到一個索引輔助查詢？對於最鄰近搜尋查詢查看 EXPLAIN 輸出是很好的主意，因為可以從非索引的 SQL 中取得正確解答，並且在表格大小放大之前，可能不甚明顯地發現缺乏索引。
以下是來自 EXPLAIN 的輸出，注意 order by 上的索引掃描
QUERY PLAN
---------------------------------------------------------------------------------
Limit (cost=0.28..79.58 rows=3 width=31)
-> Index Scan using nyc_streets_geom_idx on nyc_streets streets
(cost=0.28..504685.12 rows=19091 width=31)
Order By:
(geom <-> '0101000020266900000EEBD4CF27CF2141BC17D69516315141'::geometry)
29.2. 最鄰近連接¶
由操作員協助的索引順序有一個主要的缺點：它只適用於操作員單側的一個 **單一幾何文字** 。這對於尋找最接近一個查詢對象的物件來說很好，但對於一個目標是要找到所有候選者當中每個鄰近點的空間聯結則沒有幫助。
幸運的是，SQL 語言有一個功能允許我們反覆執行一個查詢並在迴圈中驅動：LATERAL 聯結。
在這裡，我們將找到最接近每個地鐵站的街道
SELECT subways.gid AS subway_gid,
subways.name AS subway,
streets.name AS street,
streets.gid AS street_gid,
streets.geom::geometry(MultiLinestring, 26918) AS street_geom,
streets.dist
FROM nyc_subway_stations subways
CROSS JOIN LATERAL (
SELECT streets.name, streets.geom, streets.gid, streets.geom <-> subways.geom AS dist
FROM nyc_streets AS streets
ORDER BY dist
LIMIT 1
) streets;
請注意，像 CROSS JOIN LATERAL 這樣的方式作為由地鐵表格驅動的迴圈的內部。地鐵表格中的每筆記錄都會一次一個的提供給 lateral 次查詢，所以每個地鐵記錄都可以得到一個最近的結果。
說明解釋了地鐵站上的迴圈，和在我們想要的迴圈內部的索引協助訂購
QUERY PLAN
-------------------------------------------------------------------------
Nested Loop (cost=0.28..13140.71 rows=491 width=37)
-> Seq Scan on nyc_subway_stations subways
(cost=0.00..15.91 rows=491 width=46)
-> Limit
(cost=0.28..1.71 rows=1 width=170)
-> Index Scan using nyc_streets_geom_idx on nyc_streets streets
(cost=0.28..27410.12 rows=19091 width=170)
Order By: (geom <-> subways.geom)
