如何为指定b.id优化SQL查询，统计不同距离范围站点数量

一、先搞定语法错误的问题 #

你添加WHERE b.id=114477时出错，是因为b表只存在于内部的子查询里，外层查询根本找不到它。把过滤条件移到内部查询里就行，修改后的代码如下：

SELECT loc_dist.id, loc_dist.namn1, grps.grp, count(*) 
FROM ( 
    SELECT b.id, b.namn1, ST_Distance_Sphere(b.geom, s.geom) AS dist 
    FROM stations s, bostader b
    WHERE b.id = 114477 -- 把过滤条件放在这个子查询里
) AS loc_dist 
JOIN ( 
    VALUES (1,200.), (2,400.), (3,600.) 
) AS grps(grp, dist) ON loc_dist.dist < grps.dist 
GROUP BY 1,2,3 
ORDER BY 1,2,3;

二、优化查询速度（解决运行极慢的问题） #

原查询慢到没结果，核心原因是做了全表笛卡尔积：stations和bostader各2000+条数据，会生成400万+条临时数据，计算量直接拉满。针对你只需要统计指定/部分房产的需求，给你几个优化方案：

1. 单个目标房产的高效查询

如果只查某一个房产（比如id=114477），可以先单独取出这个房产的空间坐标，再和所有站点计算距离，避免全表关联：

WITH target_property AS (
    SELECT id, namn1, geom 
    FROM bostader 
    WHERE id = 114477
)
SELECT tp.id, tp.namn1, grps.grp, count(*)
FROM target_property tp
CROSS JOIN stations s
JOIN (
    VALUES (1,200.), (2,400.), (3,600.)
) AS grps(grp, dist) 
    ON ST_Distance_Sphere(tp.geom, s.geom) < grps.dist
GROUP BY tp.id, tp.namn1, grps.grp
ORDER BY tp.id, tp.namn1, grps.grp;

2. 多个目标房产的查询优化

如果要查多个房产ID（比如114477、114478），先给空间字段建索引（这步能大幅提升距离计算速度）：

CREATE INDEX IF NOT EXISTS idx_bostader_geom ON bostader USING GIST(geom);
CREATE INDEX IF NOT EXISTS idx_stations_geom ON stations USING GIST(geom);

然后执行查询：

SELECT b.id, b.namn1, grps.grp, count(*)
FROM bostader b
CROSS JOIN stations s
JOIN (
    VALUES (1,200.), (2,400.), (3,600.)
) AS grps(grp, dist) 
    ON ST_Distance_Sphere(b.geom, s.geom) < grps.dist
WHERE b.id IN (114477, 114478) -- 这里填多个目标房产ID
GROUP BY b.id, b.namn1, grps.grp
ORDER BY b.id, b.namn1, grps.grp;

3. 精准的区间独立统计（避免重复计数）

原查询里，一个距离150m的站点会被同时统计到0-200、200-400、400-600三个分组里。如果你需要的是各区间互不重叠的独立数量，可以直接用CASE语句一步到位，省掉后续汇总的麻烦：

WITH target_property AS (
    SELECT id, namn1, geom 
    FROM bostader 
    WHERE id = 114477
),
station_distances AS (
    SELECT 
        tp.id, 
        tp.namn1,
        ST_Distance_Sphere(tp.geom, s.geom) AS dist
    FROM target_property tp
    CROSS JOIN stations s
)
SELECT 
    id,
    namn1,
    SUM(CASE WHEN dist BETWEEN 0 AND 200 THEN 1 ELSE 0 END) AS "0-200m",
    SUM(CASE WHEN dist >200 AND dist <=400 THEN 1 ELSE 0 END) AS "200-400m",
    SUM(CASE WHEN dist >400 AND dist <=600 THEN 1 ELSE 0 END) AS "400-600m"
FROM station_distances
GROUP BY id, namn1;

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