PostGIS统计邻域多边形内两类点数量的SQL语句修正请求

嘿，我明白你遇到的统计问题了——原来的多表连接方式会产生笛卡尔积，比如一个邻域里有1个警察局和100起犯罪事件，连接后会生成100条配对记录，这时候count(ps)统计的其实是配对后的行数，而不是实际的警察局数量。

下面是修正后的代码，通过先分别独立统计每个邻域的警察局和犯罪事件数量，再合并结果，就能得到准确的三列数据：

WITH police_counts AS (
    -- 先统计每个邻域内的警察局数量
    SELECT 
        nc.nbh_names,
        COUNT(ps.geom) AS num_police_stations
    FROM public."Neighborhood_Clusters" nc
    LEFT JOIN public."Police_Stations" ps 
        ON ST_Contains(nc.geom, ps.geom)
    GROUP BY nc.nbh_names
),
crime_counts AS (
    -- 再统计每个邻域内的犯罪事件数量
    SELECT 
        nc.nbh_names,
        COUNT(ci.geom) AS num_crimes
    FROM public."Neighborhood_Clusters" nc
    LEFT JOIN public."Crime_Incidents_in_2020" ci 
        ON ST_Contains(nc.geom, ci.geom)
    GROUP BY nc.nbh_names
)
-- 合并两个统计结果，得到最终视图
SELECT 
    pc.nbh_names AS "Neighbourhood name",
    pc.num_police_stations AS "Number of police stations",
    cc.num_crimes AS "Number of crimes"
FROM police_counts pc
JOIN crime_counts cc 
    ON pc.nbh_names = cc.nbh_names
ORDER BY pc.nbh_names;

代码说明： #

• CTE子查询分离统计：用WITH子句分别创建两个统计结果集，避免多表连接带来的笛卡尔积问题，确保每个统计都是独立基于邻域和单个数据集的关联。
• LEFT JOIN保留所有邻域：即使某个邻域没有警察局或者没有犯罪事件，也会被保留，COUNT(geom)会自动返回0（因为NULL不会被计数）。
• 准确映射字段：最后通过邻域名称将两个统计结果合并，得到你需要的三列数据，每个邻域的警察局数量会正确显示1-2个（符合你的预期）。

如果需要将这个结果创建为视图，只需要在开头加上CREATE VIEW neighborhood_stats AS即可。

内容的提问来源于stack exchange，提问作者Taylor Marie