寻找相邻的多边形-Postgis查询 [英] Finding neighbouring polygons - postgis query

问题描述

问题:我有一个充满多边形(一个国家的地区)的表.其中一些多边形具有某些属性，可以检查或可以不检查.在这种情况下，该属性称为"spread"，"checked"值为1.

The problem: I have a table full of polygons (districts of a country). Some of these polygons have a certain attribute that may or may not be checked. In this case the attribute is called "spread" and the "checked" value is 1.

现在，我想运行一个查询，以查找所有选中"的多边形.像这样:

Now I'd like to run a query that finds all the "checked" polygons. Something like:

SELECT * FROM gemstat WHERE spread = 1

，然后在每个尚未检查的相邻多边形中将"spread"属性设置为"1".(我也想设置第二个属性，但这只是次要的补充)

and then I'd like to set the "spread" attribute to "1" in every neighbouring polygon that isn't already checked. (I also want to set a second attribute, but that's just a minor addition)

首先，让我们从一个查询开始，该查询选择所有与蔓延值= 1的多边形相邻的多边形

First, let's start with a query that selects all polygons that are neighbours of a polygon with a spread-value = 1

SELECT (b."GEM_NR")
FROM gemstat_simple5 as a
JOIN gemstat_simple5 as b
ON ST_Touches((a.the_geom),b.the_geom)
where a.spread =1;

此查询返回与span = 1的多边形相邻的所有多边形

This query returns all polygons that are neighbours of a polygon with spread = 1

现在，我想基于该子查询的结果更新表.这是由John Powell aka Barca提供的这段代码实现的(请参见下面的答案和评论):

Now I want to update the table based on the results of that subquery. This is realized with this piece of code provided by John Powell aka Barca (see answer and also comments below):

Update gemstat_simple5 gem set spread=1, time=2
FROM (
   SELECT (b."GEM_NR")
   FROM gemstat_simple5 as a,
   gemstat_simple5 as b
   WHERE ST_Touches(a.the_geom, b.the_geom) 
   AND a."GEM_NR" != b."GEM_NR"
   AND a.spread = 1
) as subquery
WHERE gem."GEM_NR" = subquery."GEM_NR"

运行此查询，它将在不触摸散度= 1的原始多边形的情况下，将相邻多边形的散度设置为1，时间设置为2.因此，它为我的问题提供了完美的答案.

Run this query and it will set the attributes spread to 1 and time to 2 of the adjacent polygons while not touching the original polygons with spread = 1. Therefore it poses the perfect answer to my question.

推荐答案

如果您要问如何基于子查询来更新表，则仅查找那些具有相邻多边形(即，它们接触另一个多边形)的多边形，然后执行以下操作应该为您工作.

If you are asking how to update a table based on a subquery finding only those polygons that have a neighbor (ie, they touch another polygon), then the following should work for you.

Update gemstat_simple5 gem set spread=1, time=2
  FROM (
     SELECT (b."GEM_NR")
       FROM gemstat_simple5 as a,
            gemstat_simple5 as b
       WHERE ST_Touches(a.the_geom, b.the_geom) 
       AND a."GEM_NR" != b."GEM_NR"
       AND a.spread = 1
     ) as subquery
 WHERE gem."GEM_NR" = subquery."GEM_NR"

请注意，我已输入AND a."GEM_NR"<b."GEM_NR"，它既避免了AND a."GEM_NR" = b."GEM_NR"，即您自己想要避免的情况，也将成对比较减少了一半.我也使用a，b Where方法，而不是在st_touches上使用join b，这是相同的，但是我发现空间联接更加令人困惑.最后，只需将要更新的表GEM_NR与子查询中的表等同即可.

Note, I have put AND a."GEM_NR" < b."GEM_NR", which both avoids the case where AND a."GEM_NR" = b."GEM_NR", ie, itself, which you want to avoid, and also reduces the pair wise comparisons by half. I have also use the a, b Where approach rather then a join b on st_touches, which is the same, but I find more confusing with spatial joins. Finally, you just equate the table GEM_NR you are updating with those found in the sub query.

这篇关于寻找相邻的多边形-Postgis查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！