Back to Details

postgis-nearest

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Nearest Feature (KNN / 
<->
)

最近要素（KNN / 
<->
）

Use this skill when you need the nearest feature to a point/geometry (e.g., nearest feeder, nearest segment, nearest hub).
PostGIS supports fast nearest-neighbor searches using the KNN operator 
<->
when a GiST index exists.
当你需要查找某点/几何体的最近要素时（例如最近的馈线、最近的线段、最近的枢纽），可以使用该技能。
当存在GiST索引时，PostGIS支持使用KNN运算符
<->
进行快速最近邻搜索。

When to use

使用场景

  • “Find the nearest X to this point”
  • “Rank candidates by proximity”
  • “Pick the closest feature per input row”
  • “查找该点附近最近的X”
  • “按距离对候选要素排序”
  • “为每个输入行选择最近的要素”

Core rules

核心规则

  • KNN 
    <->
    works best with GiST indexes on geometry columns
  • <->
    orders by distance in the geometry’s coordinate system (SRID units)
  • For meaningful distances, use a projected SRID or 
    geography
    (meters)
  • Keep queries index-friendly: avoid wrapping the indexed column in transforms inside ORDER BY when possible
  • KNN 
    <->
    与几何体列上的GiST索引配合使用效果最佳
  • <->
    按几何体坐标系（SRID单位）的距离排序
  • 若要获得有实际意义的距离值，请使用投影SRID或
    geography
    类型（单位为米）
  • 保持查询对索引友好：尽可能避免在ORDER BY子句中对已索引列进行转换操作

Index requirement

索引要求

CREATE INDEX IF NOT EXISTS features_geom_gix ON features USING gist (geom);
Without this, KNN will not be fast.
CREATE INDEX IF NOT EXISTS features_geom_gix ON features USING gist (geom);
如果没有该索引，KNN搜索会很慢。

Canonical pattern: nearest feature to a single point

标准范式：单点的最近要素

-- point is EPSG:4326 here WITH p AS ( SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326) AS geom ) SELECT f.* FROM features f, p ORDER BY f.geom <-> p.geom LIMIT 1;
-- 此处点使用EPSG:4326坐标系 WITH p AS ( SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326) AS geom ) SELECT f.* FROM features f, p ORDER BY f.geom <-> p.geom LIMIT 1;

Nearest N features

最近N个要素

WITH p AS ( SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326) AS geom ) SELECT f.* FROM features f, p ORDER BY f.geom <-> p.geom LIMIT 10;
WITH p AS ( SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326) AS geom ) SELECT f.* FROM features f, p ORDER BY f.geom <-> p.geom LIMIT 10;

Add an exact distance (optional)

添加精确距离（可选）

Compute the distance separately (don’t replace the KNN order):
WITH p AS (
  SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326) AS geom
)
SELECT
  f.*,
  ST_Distance(f.geom::geography, p.geom::geography) AS distance_m
FROM features f, p
ORDER BY f.geom <-> p.geom
LIMIT 1;
Here:
  • ordering stays fast via KNN
  • distance is computed in meters via geography
单独计算距离（不要替换KNN排序）：
WITH p AS (
  SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326) AS geom
)
SELECT
  f.*,
  ST_Distance(f.geom::geography, p.geom::geography) AS distance_m
FROM features f, p
ORDER BY f.geom <-> p.geom
LIMIT 1;
说明：
  • 通过KNN保持排序速度
  • 通过geography类型以米为单位计算距离

Recommended: constrain candidates with ST_DWithin

推荐：使用ST_DWithin约束候选范围

For large tables, reduce work and avoid weird global matches:
WITH p AS (
  SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326) AS geom
)
SELECT f.*
FROM features f, p
WHERE ST_DWithin(f.geom::geography, p.geom::geography, 5000) -- 5km
ORDER BY f.geom <-> p.geom
LIMIT 1;
对于大型表，缩小搜索范围可减少计算量并避免全局匹配的异常情况：
WITH p AS (
  SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326) AS geom
)
SELECT f.*
FROM features f, p
WHERE ST_DWithin(f.geom::geography, p.geom::geography, 5000) -- 5公里
ORDER BY f.geom <-> p.geom
LIMIT 1;

Different SRIDs

不同SRID的处理

If your features are stored in a projected/client SRID, build the point in that SRID (or transform once):
WITH p AS (
  SELECT ST_Transform(
    ST_SetSRID(ST_MakePoint($1, $2), 4326),
    $3  -- client_srid
  ) AS geom
)
SELECT f.*
FROM features f, p
ORDER BY f.geom <-> p.geom
LIMIT 1;
Prefer storing and indexing 
features.geom
in the SRID you query most often.
如果要素存储在投影/客户端SRID中，请在该SRID下构建点（或转换一次）：
WITH p AS (
  SELECT ST_Transform(
    ST_SetSRID(ST_MakePoint($1, $2), 4326),
    $3  -- client_srid
  ) AS geom
)
SELECT f.*
FROM features f, p
ORDER BY f.geom <-> p.geom
LIMIT 1;
建议在最常用的查询SRID中存储并索引
features.geom
。

Common mistakes

常见错误

  • No GiST index (query is slow)
  • Using 
    <->
    on EPSG:4326 and assuming the distance is meters
  • Transforming 
    f.geom
    inside ORDER BY (can kill index usage)
  • Not bounding the search (use ST_DWithin when appropriate)
  • 未创建GiST索引（查询速度慢）
  • 在EPSG:4326坐标系上使用
    <->
    并假设距离单位是米
  • 在ORDER BY子句中转换
    f.geom
    （会导致索引失效）
  • 未限制搜索范围（适当时使用ST_DWithin）

Summary

总结

  • Use 
    ORDER BY geom <-> point LIMIT 1
    for nearest neighbor
  • Ensure GiST index on 
    geom
  • Use geography or projected SRID for real-world distance values
  • Add ST_DWithin to keep searches local and fast
  • 使用
    ORDER BY geom <-> point LIMIT 1
    查找最近邻
  • 确保
    geom
    列上存在GiST索引
  • 使用geography类型或投影SRID获取真实世界的距离值
  • 添加ST_DWithin使搜索本地化且快速