PostgreSQL/PostGIS:ST_距离查询应仅返回“最近的”结果

Question

我想查询表A中每个点（id）到表B中每个多边形（id）的距离。对于距离计算，我使用ST_距离。但是，ST_Distance（显然）返回到每个多边形的距离。但我只需要每个点的“最接近”结果。到目前为止，我尝试了下面的查询，它返回了正确的结果，但是（当然）只返回了一个点。

SELECT polygons.id, points.id, ST_Distance(points.geom, polygons.geom) 
FROM table_A AS points, table_B AS polygons 
ORDER BY st_distance ASC LIMIT 1

结果应该是这样的：

polygon_id | point_id | min(distance)
-------------------------------------
1234       |   876    |   54.32
...        |   ...    |    ...
-------------------------------------

你有什么提示吗？非常感谢你。

更新1

WITH CTE AS
       (SELECT polygons.id as poly_id, points.id as point_id, 
        ST_Distance(points.geom, polygons.geom) as thedistance , 
        row_number() OVER
              (PARTITION BY points.id ORDER BY ST_Distance(points.geom, polygons.geom)) 
    FROM 
        table_A AS points
    INNER JOIN table_B AS polygons
    ON ST_DWithin(points.geom, polygons.geom, 100)) SELECT * FROM CTE WHERE row_number = 1

运行上述查询（3h 24m）后，返回的结果为空。然而，应该有一个结果。括号可能有问题吗？

更新2

SRID为4326（WGS84），多边形为OSM建筑多边形，点为同一城市中的任意点。

Answer 1

WITH CTE AS
   (select polygons.id as poly_id, points.id as point_id, 
    ST_Distance(points.geom, polygons.geom) as thedistance , 
    row_number() OVER
          (PARTITION BY points.id ORDER by ST_Distance(points.geom, polygons.geom) ) 
FROM 
 table_A AS points, table_B AS polygons )
 select poly_id, point_id, thedistance from CTE where row_number = 1

然而，如果你有很多分数，这可能会很慢。如果您大致知道点与多边形之间的距离，您可以通过在使用索引的连接中使用st_dwithin来加快速度。只需设置距离参数，这样你就可以抓住每一点：

WITH CTE AS
   (select polygons.id as poly_id, points.id as point_id, 
    ST_Distance(points.geom, polygons.geom) as thedistance , 
    row_number() OVER
          (PARTITION BY points.id ORDER by ST_Distance(points.geom, polygons.geom) ) 
FROM 
    table_A AS points
INNER JOINT table_B AS polygons
ON st_Dwithin(points.geom, polygons.geom, 5000 )) -- assumes you have a metres projection, limit to 5KM
select * from CTE where row_number = 1

确保你的两个GEOM列都有GIST索引