获取框中包含的最左右上下点

您可以使用距离运算符( <-> )结合 MIN 聚合函数来查找最近的距离,从而减少了为包含 minimal quantity . 严格左/右/上/下运算符( << , >> , |>> , <<| )可用于将点限制在框的一侧。因为两个不同的点可能有相同的距离,我们将 limit 结果为1行。假设屏幕方向,即坐标向下和向右增加(而不是地图方向,即向北和向东增加),我们得到:

-- Above, or North
SELECT * FROM ch WHERE loc <<| screen AND (loc <-> screen) = (
  SELECT MIN(loc <-> screen) AS mindist FROM ch
    WHERE loc <<| screen
) LIMIT 1

-- Right, or East
SELECT * FROM ch WHERE loc >> screen AND (loc <-> screen) = (
  SELECT MIN(loc <-> screen) AS mindist FROM ch 
    WHERE loc >> screen
) LIMIT 1

-- Below, or South
SELECT * FROM ch WHERE loc |>> screen AND (loc <-> screen) = (
  SELECT MIN(loc <-> screen) AS mindist FROM ch
    WHERE loc |>> screen
) LIMIT 1

-- Left, or West
SELECT * FROM ch WHERE loc << screen AND (loc <-> screen) = (
  SELECT MIN(loc <-> screen) AS mindist FROM ch 
    WHERE loc << screen
) LIMIT 1

请注意,水平方向上最近的点也可能是垂直方向上最近的点;也就是说,以上四个语句的并集可能小于四行。

我们可以得到四个最近的点:

SELECT *, (loc <-> screen) AS distance FROM ch 
  WHERE NOT loc <@ screen
  ORDER BY distance
  LIMIT 4

但是,请注意,一些最近的点可能彼此在同一方向上。

我们可以得到最接近的点

SELECT *, (loc <-> screen) AS distance FROM ch 
  WHERE distance = (
      SELECT MIN(loc <-> screen) AS mindist FROM ch
  )
  LIMIT 1

或

SELECT *, (loc <-> screen) AS distance FROM ch 
  WHERE NOT loc <@ screen
  ORDER BY distance
  LIMIT 1

在计算列的最小值(或最大值)时,最好使用第一个值,因为dbms可以对列(如果有)使用索引,而不需要扫描表。由于距离是一个计算值,因此始终需要表扫描,查询的性能也将类似。一 query analysis 可能会有其他的原因让你更喜欢一个陈述,所以你应该在选择一种方法之前这样做。