postgresql - 如何在 PostGIS/PostgreSQL 中选择排名最高的几何体？

Question

我的 PostgreSQL 9.5 数据库中有两个表，即 pt 和 lines。 pt 表包含点几何，lines 表包含线几何 和值(数字)。以下查询，对于每个 pt，选择 50 米搜索距离内的 lines，并将新点转换到值 > 500 的线上。

Select distinct on (pt_id)
pt.gid as pt_id,
case when lines.value > 500
then st_closestpoint(lines.geom, pt.geom)
else null
end as new_pt
from
pt
left join lines on
st_dwithin(pt.geom, lines.geom, 50)
order by pt_id;

示例场景如下图所示:

我需要修改上面的查询以添加排名/优先级条件，以便具有最高值的行应该具有最高排名并且 new_pt 应该被投影到这个排名最高的行。我如何修改上面的代码，以便在查询大型数据集时，应该首先为每一行分配排名，然后项目指向具有最高值的行？

Answer 1

ORDER BY 列表应该有比 DISTINCT ON 更多的表达式。 DISTINCT ON 用于告知新数据集的主键，ORDER BY 决定源表中的哪一行进入数据集。

自然地，两个列表应该共享一个头部。

你的情况我认为应该是

SELECT DISTINCT ON (pt_id)
  pt.gid AS pt_id,
  CASE
    WHEN lines.value > 500
      THEN st_closestpoint(lines.geom, pt.geom)
    ELSE NULL
  END AS new_pt
FROM
  pt
LEFT JOIN lines
  ON st_dwithin(pt.geom, lines.geom, 50)
ORDER BY pt_id, lines.value DESC;