sql - 从多边形中包含的点集中分配人口最多的点

Question

我正在尝试将“基于人口的质心”列添加到一系列美国县多边形中，其中位置不是基于多边形的地理质心，而是基于地名人口稠密地点的位置拥有最多的人口。例如，我想将箭头指示的点(点直径 = 人口)的几何形状分配给所选多边形的 population-based-centroid 列:

我已经测试了这个查询，它为任何给定的多边形(例如波士顿的萨福克郡)返回了正确的几何图形:

SELECT g1.the_geom
FROM counties c1
JOIN geonames g1
ON ST_Contains(c1.the_geom, g1.the_geom)
WHERE c1.name = 'Suffolk County, MA'
ORDER BY g1.population DESC
LIMIT 1;

但是，我正在处理大约 4000 个多边形，当我尝试在这样的更新函数中使用查询时，它会无限期地挂起(或者至少比这个数量的要素应该挂起的时间长得多):

UPDATE counties
    SET the_geom_popcentroid = (
        SELECT g1.the_geom
        FROM counties c1
        JOIN geonames g1
        ON ST_Contains(c1.the_geom, g1.the_geom)
        ORDER BY g1.population DESC
        LIMIT 1
    );

我在哪里错误地嵌套了这个 UPDATE 函数？

Answer 1

仔细检查:由于外部表中的每一行与相关子查询的结果之间没有任何联系，您会得到每个的常量值em> 行。令人费解的是:这应该不是慢，而是出奇的快。也完全不正确。要修复您的查询:

UPDATE counties c
SET    the_geom_popcentroid = (
        SELECT g.the_geom
        FROM   geonames g
        WHERE  ST_Contains(c.the_geom, g.the_geom)
        ORDER  BY g.population DESC
        LIMIT  1
        );

这会更新所有 县。如果一个县根本不应包含任何 geoname，the_geom_popcentroid 将设置为 NULL。

此具有JOIN 语法 的替代版本仅更新包含至少一个地理名称的县:

UPDATE counties c
SET    the_geom_popcentroid = sub.the_geom
FROM (
    SELECT DISTINCT ON (c1.pk)
           c1.pk, g1.the_geom
    FROM   counties c1
    JOIN   geonames g1 ON ST_Contains(c1.the_geom, g1.the_geom)
    ORDER  BY c1.pk, g1.population DESC
    ) sub
WHERE c.pk = sub.pk;

其中 pk 是 counties 的主键列(或任何唯一列)。

DISTINCT ON 的解释:

• Select first row in each GROUP BY group?

不确定哪个更快。尝试在两个 the_geom 列上使用索引时，ST_Contains() 与 ORDER BY third_column LIMIT n 的组合可能会很棘手。如果重要，请使用 EXPLAIN ANALYZE 进行测试。

有时 LATERAL JOIN 可以帮助说服 Postgres 使用索引。相关问题:

• Indexes for SQL query with WHERE condition and GROUP BY