PostgreSQL空间查询：计算各选址潜在总营收并写入新表求助

一、前置优化：创建空间索引 #

为提升1公里范围空间查询的性能，先给两张表的geometry字段创建GIST空间索引（3000条数据虽少，但索引能避免后续数据量扩大时的性能瓶颈）：

-- 给消费者表创建空间索引
CREATE INDEX idx_p_consumers_geom ON p_consumers USING GIST (geometry);

-- 给选址表创建空间索引
CREATE INDEX idx_p_locations_geom ON p_locations USING GIST (geometry);

二、核心查询逻辑：计算各选址的分类型及总潜在营收 #

使用ST_DWithin替代ST_Distance（前者能利用空间索引，查询效率更高），关联三张表统计数据：

SELECT
    l.fid AS location_id,
    ct.consumer_type,
    COUNT(c.fid) AS consumer_count,
    ct.potential_value,
    COUNT(c.fid) * ct.potential_value AS type_revenue,
    SUM(COUNT(c.fid) * ct.potential_value) OVER (PARTITION BY l.fid) AS total_revenue
FROM
    p_locations l
LEFT JOIN p_consumers c
    -- 若geometry是WGS84经纬度（EPSG:4326），转geography确保距离单位为米；若为投影坐标系（如UTM，单位米），可直接用ST_DWithin(c.geometry, l.geometry, 1000)
    ON ST_DWithin(c.geometry::geography, l.geometry::geography, 1000)
LEFT JOIN p_consumer_type ct
    ON c.consumer_type = ct.consumer_type
GROUP BY
    l.fid, ct.consumer_type, ct.potential_value
ORDER BY
    l.fid, ct.consumer_type;

• LEFT JOIN确保即使选址1公里内无某类消费者，也会显示该类型（consumer_count为0），避免遗漏类型；若只需显示有消费者的类型，可改为INNER JOIN。
• SUM(...) OVER (PARTITION BY l.fid)用于计算每个选址的总潜在营收，无需额外子查询。

三、将结果写入新表 #

方式1：直接创建表并写入（简单快捷）

CREATE TABLE location_potential_revenue AS
SELECT
    l.fid AS location_id,
    ct.consumer_type,
    COUNT(c.fid) AS consumer_count,
    ct.potential_value,
    COUNT(c.fid) * ct.potential_value AS type_revenue,
    SUM(COUNT(c.fid) * ct.potential_value) OVER (PARTITION BY l.fid) AS total_revenue
FROM
    p_locations l
LEFT JOIN p_consumers c
    ON ST_DWithin(c.geometry::geography, l.geometry::geography, 1000)
LEFT JOIN p_consumer_type ct
    ON c.consumer_type = ct.consumer_type
GROUP BY
    l.fid, ct.consumer_type, ct.potential_value;

方式2：先定义表结构再插入（支持自定义主键、约束）

-- 创建带约束的表结构
CREATE TABLE location_potential_revenue (
    id SERIAL PRIMARY KEY,
    location_id INT REFERENCES p_locations(fid),
    consumer_type VARCHAR(50) REFERENCES p_consumer_type(consumer_type),
    consumer_count INT,
    potential_value NUMERIC(10,2),
    type_revenue NUMERIC(12,2),
    total_revenue NUMERIC(12,2)
);

-- 插入计算结果
INSERT INTO location_potential_revenue (location_id, consumer_type, consumer_count, potential_value, type_revenue, total_revenue)
SELECT
    l.fid AS location_id,
    ct.consumer_type,
    COUNT(c.fid) AS consumer_count,
    ct.potential_value,
    COUNT(c.fid) * ct.potential_value AS type_revenue,
    SUM(COUNT(c.fid) * ct.potential_value) OVER (PARTITION BY l.fid) AS total_revenue
FROM
    p_locations l
LEFT JOIN p_consumers c
    ON ST_DWithin(c.geometry::geography, l.geometry::geography, 1000)
LEFT JOIN p_consumer_type ct
    ON c.consumer_type = ct.consumer_type
GROUP BY
    l.fid, ct.consumer_type, ct.potential_value;

关键注意事项 #

• 空间单位：若geometry字段是WGS84经纬度，必须转geography类型才能用米作为距离单位；若为投影坐标系（单位米），可直接使用ST_DWithin的原始geometry参数。
• 数据精度：potential_value和营收字段建议用NUMERIC类型，避免浮点运算的精度丢失。

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