查询以获取表1中每一行的结果，其中包含满足表2中条件的N个最大记录的子查询

Question

我正在尝试使用激光雷达卫星数据集计算我所在城市的建筑物高度，但没有成功。

系统规格

• CPU：核心i7 6700k 4200MHz，4核，8线程
• 内存：32GB DDR4 3200mhz
• SSD：1TB三星970 EVO
• 操作系统：Ubuntu 18.04

Postgres设置

我在PostGIS中使用最新版本的Postgres v12.1数据库，并在不同来源中建议进行以下调整：

 shared_buffers = 256MB 
 maintenance_work_mem = 4GB
 max_parallel_maintenance_workers = 7
 max_parallel_workers = 7
 max_wal_size = 60GB
 min_wal_size = 90MB
 random_page_cost = 1.0

数据库设置

< li >在激光雷达表中，我有超过30亿行，在建筑物表中有超过15万行。 < li >在激光雷达表中创建了GiST索引:< code >使用GIST (geom)在激光雷达上创建索引lidar _ idx < li >构建表格:| gid | geom | < li >激光雷达表:| z | geom |

高度计算

目前，为了计算建筑物的高度，需要检查30亿个点(行)中的每一个点是否都在每个建筑物的区域内，并计算在建筑物区域内发现的所有点的平均值。

我尝试的查询需要很长时间（可能超过 5 天甚至更长时间），我想简化查询，以便我可以用更少的点获得建筑物的高度，而不必与每次每个建筑物的所有疯狂的 30 亿条记录进行比较。

例如：

• 对于使用 id1 的建筑，我只想获取在建筑几何区域内找到的前 100 条记录（ST_Within（l.geom，e.geom）），一旦找到这 100 条记录，就传递到下一个建筑。
• 对于使用 id2 构建，我想要同样的东西，只获取在建筑区域内找到的前 100 条记录。
• 等等..

我的主要查询是

SELECT e.gid, AVG(l.z) AS height 
FROM lidar l, 
     buildings e 
WHERE ST_Within(l.geom, e.geom) 
GROUP BY e.gid) t

我已经尝试过另一个查询，但无法使其生效。

SELECT e.gid, AVG(l.z), COUNT(1) FILTER (WHERE ST_Within(l.geom, e.geom)) AS gidc
FROM lidar l, buildings e
WHERE gidc < 100
GROUP BY e.gid

Answer 1

我认为你根本不想这样做。首先，您应该尝试通过处理任意（但不是随机）数据子集来加快正确查询的速度，而不是损害正确性。

但是如果你真的想要，你可以用横向连接。

SELECT e.gid from 
    buildings e cross join lateral 
    (select AVG(l.z) AS height FROM lidar l WHERE ST_Within(l.geom, e.geom) LIMIT 100) 

有必要检查3000万个点（行）中的每一个是否在每个建筑的区域内，并计算在建筑区域内发现的所有点的平均值。

这正是几何索引的用途。你不需要查看每一个点来获取建筑区域内的点。如果没有正确的索引，例如< code > on lidar using gist(geom)，那么横向连接查询也会很糟糕。