计算平均距离w/极限的子查询

Question

我有一个选择过程，它依赖于创建两个中间表。我需要帮助重写代码来使用子查询而不是中间表。

我真的不理解子查询，而且我尝试过的每个查询都会返回一个错误。

这段代码提供了我想要的结果，但依赖于创建中间表：

Select modded_vgi_ptsutm.pplx,ST_Distance(modded_vgi_ptsutm.geom,split_polygons.geom) as dist into intermediate from modded_vgi_ptsutm,split_polygons where split_polygons.objectid = 87 order by dist as limit 10;

Select pplx,count(pplx) as count,avg(dist) as dist into intermediate2 from intermediate group by pplx order by dist asc;

Select pplx,dist from intermediate2 where count > 1; 

注意:我在这里发布了这个，而不是gis页面，因为我认为我的问题更多的是sql而不是gis。

Answer 1

您应该能够使用子查询来组合这些：

Select pplx, avg(dist) as dist
into intermediate2
from (Select modded_vgi_ptsutm.pplx, ST_Distance(modded_vgi_ptsutm.geom, split_polygons.geom) as dist
      from modded_vgi_ptsutm cross join
           split_polygons
      where split_polygons.objectid = 87
      order by dist
      limit 10
     ) t
group by pplx
having count(pplx) > 1
order by dist;

注意:在from子句中使用逗号是可疑的，因为它会导致笛卡尔乘积。我用一个显式的cross join替换了它。

Answer 2

这应该能起作用：

SELECT pplx,dist FROM 
    (
    SELECT pplx,count(pplx) AS count,avg(dist) AS dist FROM 
        (
        SELECT modded_vgi_ptsutm.pplx,ST_Distance(modded_vgi_ptsutm.geom,split_polygons.geom) AS dist FROM 
        modded_vgi_ptsutm,split_polygons where split_polygons.objectid = 87 ORDER BY dist AS LIMIT 10
        ) 
    GROUP BY pplx ORDER BY dist asc
    ) 
WHERE count > 1;