如何根据这些表中的空间关系几何图形将数据从一个表设置到另一个表

Question

我有两个包含数据的表。关于这些表中数据之间的空间关系，我需要从一个表到另一个表设置databuilding_h。源表中的空间数据是多边形类型，目标表中的数据是点。我查询了相交的点和多边形。效果很好。但也有一些点与多边形不相交。我的想法是为这些点中的每一个定义最接近的多边形，并从中获取building_h值。为此，我编写了一个函数，该函数获取该点的id并返回uilding_h值。它在测试中运行良好。

CREATE OR REPLACE FUNCTION closest_pol(int4)
RETURNS NUMERIC 
AS
$$
DECLARE
retVal NUMERIC;
BEGIN
SELECT bgs.building_h INTO retVal
FROM buildings_geoalert_spgg bgs, building_from_landuse_spgg bfl 
WHERE ST_INTERSECTS(bgs.geom, ST_BUFFER(bfl.geom_centr, 0.0006,'quad_segs=8')) AND bfl.id = $1
ORDER BY ST_INTERSECTION(bgs.geom, ST_BUFFER(bfl.geom_centr, 0.0006,'quad_segs=8')) ASC 
LIMIT 1;
RETURN retVal;
END;
$$
LANGUAGE plpgsql 
STABLE 
RETURNS NULL ON NULL INPUT;

但是，当我对整个表运行查询时，它将永远执行。。(对于约3000行NULL值(。它持续了几个小时我才停下来。

UPDATE building_from_landuse_spgg AS bfl SET 
building_h = (SELECT closest_pol(bfl.id))
WHERE bfl.building_h IS NULL;

你知道我做错了什么吗？

Answer 1

在WHERE子句中，您使用的是在查询时创建的具有0.006缓冲区的ST_Intersects。考虑将部分gist索引与您的缓冲区一起使用(如果出于任何原因必须使用它们(：

CREATE INDEX idx_buffer ON building_from_landuse_spgg USING gist (ST_Buffer(geom_centr, 0.0006,'quad_segs=8'));

我认为您不需要函数，因为您可以将函数内部的查询用作UPDATE语句中的子查询。但如果你有理由坚持使用这个函数，你可以去掉SELECT来调用它：

UPDATE building_from_landuse_spgg 
SET building_h = closest_pol(id)
WHERE building_h IS NULL;

EDIT：正如@JGH正确提到的(请参阅注释(，使用缓冲区的效率不如简单使用ST_DWithin。因此，如果您负担得起，请在WHERE子句中使用的几何图形中创建一个索引。。

CREATE INDEX idx_landuse_geom_centr ON building_from_landuse_spgg USING gist (geom_centr);
CREATE INDEX idx_geoalert_geom ON buildings_geoalert_spgg USING gist (geom);

并且部分索引building_h会加快速度，因为您只对NULL记录感兴趣：

CREATE INDEX idx_landuse_building_h ON building_from_landuse_spgg (building_h) 
WHERE building_h IS NULL;

或者，如果您喜欢更宽的索引，但仍将NULL值放在第一类中。。

CREATE INDEX idx_landuse_building_h ON building_from_landuse_spgg 
(building_h NULLS FIRST);

也许可以考虑将函数的代码放入子查询中，例如

UPDATE building_from_landuse_spgg AS bfl 
SET building_h = (
SELECT bgs.building_h 
FROM   buildings_geoalert_spgg bgs 
WHERE  ST_DWithin(bgs.geom, bfl.geom_centr, 0.0006)
ORDER BY ST_Distance(bgs.geom, bfl.geom_centr) ASC LIMIT 1)
WHERE bfl.building_h IS NULL;

进一步阅读：

• Getting all Buildings in range of 5 miles from specified coordinates
• ST_Distance