具有ST_Distance，kNN的PostGIS最近点

CREATE TABLE traffic_signs
(
  id numeric(8,0) ),
  "GEOMETRY" geometry,
  CONSTRAINT traffic_signs_pkey PRIMARY KEY (id),
  CONSTRAINT traffic_signs_id_key UNIQUE (id)
)
WITH (
  OIDS=TRUE
);

CREATE TABLE entrance_halls
(
  id numeric(8,0) ),
  "GEOMETRY" geometry,
  CONSTRAINT entrance_halls_pkey PRIMARY KEY (id),
  CONSTRAINT entrance_halls_id_key UNIQUE (id)
)
WITH (
  OIDS=TRUE
);

SELECT senal.id,port.id,ST_Distance(port."GEOMETRY",senal."GEOMETRY")  as dist
    FROM traffic_signs As senal, entrance_halls As port   
    ORDER BY senal.id,port.id,ST_Distance(port."GEOMETRY",senal."GEOMETRY")

#1 楼

你快到了。有一个小技巧是使用Postgres的独特运算符，该运算符将返回每个组合的第一个匹配项-当您按ST_Distance进行排序时，它将有效地将每个参量中的最接近点返回到每个端口。

SELECT 
   DISTINCT ON (senal.id) senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY")  as dist
FROM traffic_signs As senal, entrance_halls As port   
ORDER BY senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY");

如果您知道每种情况下的最小距离不超过x的某个数量（并且表上有空间索引），则可以通过将WHERE ST_DWithin(port."GEOMETRY", senal."GEOMETRY", distance)，例如，如果已知所有最小距离都不超过10km，则：

SELECT 
   DISTINCT ON (senal.id) senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY")  as dist
FROM traffic_signs As senal, entrance_halls As port  
WHERE ST_DWithin(port."GEOMETRY", senal."GEOMETRY", 10000) 
ORDER BY senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY");

显然，需要谨慎使用，就像最小距离越大，您将无法获得针对该传感器和端口的组合的任何行。

注意：按订单的顺序必须匹配订单上的不重复订单，这很有意义，因为不重复订单采用第一个

假定您在两个表上都有一个空间索引。

编辑1.还有另一种方法，可以使用Postgres的<->和<＃>运算符（分别计算中心点和边界框距离），可以更有效地利用空间索引，并且不需要ST_DWithin hack来避免n ^ 2比较。有一篇很好的博客文章解释了它们如何工作。一般要注意的是，这两个运算符在ORDER BY子句中起作用。

SELECT senal.id, 
  (SELECT port.id 
   FROM entrance_halls as port 
   ORDER BY senal.geom <#> port.geom LIMIT 1)
FROM  traffic_signs as senal;

EDIT 2.由于这个问题已经引起了很多关注和k-nearest邻居（kNN）在GIS中通常是一个难题（就算法运行时而言），似乎有必要在此问题的原始范围上进行扩展。

查找x的标准方法一个对象的最近邻居是使用LATERAL JOIN（概念上类似于每个循环的）。从dbaston的答案中无耻地借钱，您会做类似的事情：

SELECT
  signs.id,
  closest_port.id,
  closest_port.dist
 FROM traffic_signs
CROSS JOIN LATERAL 
  (SELECT
      id, 
      ST_Distance(ports.geom, signs.geom) as dist
      FROM ports
      ORDER BY signs.geom <-> ports.geom
     LIMIT 1
   ) AS closest_port

因此，如果要查找最接近的10个端口（按距离排序），只需更改横向子查询中的LIMIT子句。如果没有LATERAL JOINS，就很难做到这一点，并且需要使用ARRAY类型的逻辑。
虽然这种方法行之有效，但是如果您知道只需要搜索给定的距离，则可以大大加快这种方法。在这种情况下，您可以在子查询中使用ST_DWithin（signs.geom，ports.geom，1000），由于使用<->运算符建立索引的方式-几何之一应该是常量，而不是a列参考-可能会快得多。因此，例如，要获取10公里以内的3个最近的端口，您可以编写以下内容。

 SELECT
  signs.id,
  closest_port.id,
  closest_port.dist
 FROM traffic_signs
CROSS JOIN LATERAL 
  (SELECT
      id, 
      ST_Distance(ports.geom, signs.geom) as dist
      FROM ports
      WHERE ST_DWithin(ports.geom, signs.geom, 10000)
      ORDER BY ST_Distance(ports.geom, signs.geom)
     LIMIT 3
   ) AS closest_port;

和往常一样，使用情况会因数据而异分布和查询，所以EXPLAIN是您最好的朋友。

最后，如果使用LEFT而不是CROSS JOIN LATERAL，则有一个小问题，因为您必须在横向查询别名之后添加ON TRUE，例如，

SELECT
  signs.id,
  closest_port.id,
  closest_port.dist
 FROM traffic_signs
LEFT JOIN LATERAL 
  (SELECT
      id, 
      ST_Distance(ports.geom, signs.geom) as dist
      FROM ports          
      ORDER BY signs.geom <-> ports.geom
      LIMIT 1
   ) AS closest_port
   ON TRUE;

#2 楼

这可以通过PostgreSQL 9.3+中的LATERAL JOIN完成：

SELECT
  signs.id,
  closest_port.id,
  closest_port.dist
FROM traffic_signs
CROSS JOIN LATERAL 
  (SELECT
     id, 
     ST_Distance(ports.geom, signs.geom) as dist
     FROM ports
     ORDER BY signs.geom <-> ports.geom
   LIMIT 1) AS closest_port

#3 楼

带有交叉联接的方法不使用索引，并且需要大量内存。因此，您基本上有两种选择。在9.3之前的版本中，您将使用相关子查询。 9.3+可以使用LATERAL JOIN。

KNN GIST具有横向扭曲很快就会进入您附近的数据库

（即将进行确切的查询）

#4 楼

@JohnBarça

ORDER BY错误！

ORDER BY senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY");

右

senal.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY"),port.id;

否则，它将不返回最接近的值，而仅返回具有较小端口号的

#5 楼

如果您只是对将最接近的点返回到参考点的查询感兴趣，那么这也可以解决问题：

select senal.id,port.id, st_closestPoint(port."GEOMETRY",senal."GEOMETRY" 
    FROM traffic_signs As senal, entrance_halls As port