PostGIS，更新表，设置“ true”在具有相同ID的最大区域

发布于 2025-01-30 19:18:35 字数 841 浏览 2 评论 0原文

我在Postgres/Postgis中有一个表格，其中包含以下列： ID（PK），官方ID，AREANAME，IS_MAINAREA（BOOL），GEOM（Polygon）数据中的行可能具有相同的名称和官方身份证，但几乎不同。具有相同名称和ID的“主要区域”有一个或多个次要的“卫星区域”。示例：

Id   officialId  areaname  is_mainarea  geom
3034 217044      Boda      false        POLYGON(xxxx)
4381 217044      Boda      false        POLYGON(xxxx)
4382 217044      Boda      false        POLYGON(xxxx)
3047 217026      Djura     false        POLYGON(xxxx)
4516 217026      Djura     false        POLYGON(xxxx)

我想做的是更新表格，以便具有相同官方ID的最大区域的行在is_mainarea中具有“ true”。因此，在上面的示例中，IS_MainaRea应该在ID 3034和2047上行时。

是否可以使用SQL进行此操作？

多边形的示例。我需要找到一种方法来设置IS_MainArea = true对于具有同一官方ID的较大多边形。

原文

I have a table in Postgres/PostGis with the following columns:
Id (PK), officialId, areaname, is_mainarea (bool), geom (Polygon)
The rows in the data could have the same name and official id, but different geom. There are one or more minor "satellite areas " to a "main area" that have the same name and id.
Example:

Id   officialId  areaname  is_mainarea  geom
3034 217044      Boda      false        POLYGON(xxxx)
4381 217044      Boda      false        POLYGON(xxxx)
4382 217044      Boda      false        POLYGON(xxxx)
3047 217026      Djura     false        POLYGON(xxxx)
4516 217026      Djura     false        POLYGON(xxxx)

What I want to do is to update the table so that the row with the largest area of the ones with the same official id has "true" in is_mainarea. So, in the example above is_mainarea should be true on row with Id 3034 and 2047.

Is there a way to do this with just SQL?

Example of polygons. I would need to find a way to set is_mainarea=true for the larger of the polygons with the same officialid.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

巷雨优美回忆 2025-02-06 19:18:36

我可以通过以下对Netanels的调整来解决：

WITH max_area_id AS (
      select id
        from geo.geoareas as a
        and a.id = (select id from geo.geoareas as b where a.officialid = b.officialid order by st_area(b.geom) desc limit 1)
    )
update geo.geoareas a
    set ismainarea=true
    from max_area_id
    where a.id = max_area_id.id

I got it to work with the following adjustment to Netanels answer:

WITH max_area_id AS (
      select id
        from geo.geoareas as a
        and a.id = (select id from geo.geoareas as b where a.officialid = b.officialid order by st_area(b.geom) desc limit 1)
    )
update geo.geoareas a
    set ismainarea=true
    from max_area_id
    where a.id = max_area_id.id

回复收藏 0 原文

夕嗳→ 2025-02-06 19:18:35

该查询应该为您提供帮助：

WITH max_area_id AS (
  select id 
  from table_name
  group by id, officialId 
  having ST_AREA(geom) = max(ST_AREA(geom))
)
update table_name
set is_mainarea=true
from max_area
where table.id = max_area_id.id

That query should help you:

WITH max_area_id AS (
  select id 
  from table_name
  group by id, officialId 
  having ST_AREA(geom) = max(ST_AREA(geom))
)
update table_name
set is_mainarea=true
from max_area
where table.id = max_area_id.id

回复收藏 0 原文

~没有更多了~