postgresql - 误解 UPDATE FROM 语法

Question

使用 PostGIS 我有两个表，第一个包含 250 个城市的边界，第二个包含世界上所有国家/地区的边界。

我试图影响每个城市所属的国家/地区。下面的查询可以让我得到我想要的结果。

SELECT DISTINCT ON (cities.id) cities.id, country.id
FROM cities
LEFT JOIN country ON st_intersects(country.geom, cities.geom)

但是当我使用这个查询时:

UPDATE cities
SET country_id=subq.id
FROM (SELECT DISTINCT ON (cities.id) country.id
    FROM cities
    LEFT JOIN country ON st_intersects(country.geom, cities.geom)) AS subq

country_id 列充满了相同的数字。

我在使用 UPDATE FROM 语法时遗漏了什么？

Answer 1

您需要将两个查询与连接条件相关联:

UPDATE cities
  SET country_id=subq.id
FROM (
  SELECT DISTINCT ON (cities.id) country.id
  FROM cities
    LEFT JOIN country ON st_intersects(country.geom, cities.geom)
) AS subq
  where subq.id = cities.id;

但我认为您不需要从子选择开始。您可以将国家表直接连接到城市表:

UPDATE cities
  SET country_id = country.id
FROM country 
where st_intersects(country.geom, cities.geom)