代码之家 › 专栏 › 技术社区 › Arnold

如何避免Postgis中重叠区域的重复计数?

postgis gis geolocation

Arnold · 技术社区 · 4 年前

我想用Postgis计算一个城镇的事件的影响。我有一张表格,上面有活动的点位置(活动计数,2019年地理位置),还有一张表格,里面有城镇的所有建筑(乌得勒支2020年)以及点位置。我在200米多一点的范围内统计了活动周边的所有房屋,并统计了有人居住的房屋数量。见下面的代码。

-- In a range of ~200 meters
UPDATE event_count_2019_geo
SET gw200 = temp.aantal_woningen
FROM (SELECT locatie, count(event_count_2019_geo.locatie) AS aantal_woningen
      FROM event_count_2019_geo
           INNER JOIN utrecht_2020 AS bag ON (ST_DWithin(bag.geo_lokatie, event_count_2019_geo.geo_lokatie, 0.002))  
      WHERE  bag.verblijfsobjectgebruiksdoel LIKE '%woonfunctie%'
      GROUP BY locatie
     ) AS temp
WHERE event_count_2019_geo.locatie = temp.locatie;

麻烦的是,我最终会有太多的房屋受到这一事件的影响。我画了一幅每项比赛200米范围的图(见下图)。重叠区域数两次,三次或四次。每场比赛房子都算对了,但我不能把结果加起来。有没有办法纠正这些重叠,以便我可以在所有选定的活动中得到正确的房屋总数?

编辑:示例

0 回复 | 直到 4 年前

Arnold 4 年前

多亏了@JimJones的建议,我找到了解决办法。我定义了两种观点:一种是以旧的方式找到所有的房子(find_houses_all),另一种是只返回唯一的房子(find_houses_unique)。

-- Find all houses within a radius of ~200m of an event
DROP VIEW IF EXISTS find_houses_all;

CREATE VIEW find_houses_all AS 
    SELECT bag.openbareruimte, bag.huisnummer, bag.huisletter, bag.huisnummertoevoeging,
           event_count_2019_geo.locatie
    FROM event_count_2019_geo
         INNER JOIN utrecht_2020 AS bag ON (ST_DWithin(bag.geo_lokatie, event_count_2019_geo.geo_lokatie, 0.002));  

-- Find all *unique* houses within a radius of ~200m of an event 
-- Each house is uniquely identiefied by openbareruimte, huisnummer, huisletter
-- and huisnummertoevoeging, so these are the columns to apply DISTINCT ON
DROP VIEW IF EXISTS find_houses_unique;

CREATE VIEW find_houses_unique AS 
    SELECT DISTINCT ON(bag.openbareruimte, bag.huisnummer, bag.huisletter, bag.huisnummertoevoeging) 
           bag.openbareruimte, bag.huisnummer, bag.huisletter, bag.huisnummertoevoeging,
           event_count_2019_geo.locatie
    FROM event_count_2019_geo
         INNER JOIN utrecht_2020 AS bag ON (ST_DWithin(bag.geo_lokatie, event_count_2019_geo.geo_lokatie, 0.002));

SELECT locatie, COUNT (locatie)
FROM find_houses_all -- find_houses_unique
GROUP BY locatie
ORDER BY locatie;

在所有情况下,find_houses_all的输出都大于或等于find_houses_unique的输出。电子表格中的示例输出和减去的结果如下所示:

Locatie         All Unique  All - Unique
achter st.-ptr. 617 222     395
berlijnplein    87   87       0
boothstraat     653 175     478
breedstraat    1057 564     493
buurkerkhof     914 163     751
catharijnesngl. 134  38      96
domplein        842 149     693
 ...
Total         35399 13196   22203

负数表示错误。