如何根据这些表中的空间关系几何将表中的数据设置为另一个 [英] How can I set data from on table to another according spatial relation geometries in these tables
问题描述
我有两个数据表.我需要根据这些表中数据之间的空间关系将数据 building_h 从一个表设置到另一个表.源表中的空间数据为多边形类型，而目标表中的空间数据为点.我查询了相交的点和多边形.它工作得很好.但是有些点与多边形不相交.我的想法是为这些点中的每一个定义最近的多边形并从中获取 building_h 值.为此，我编写了一个函数，它接受点的 id 并返回 building_h 值.并且在测试中运行正常.
I have two tables with data. I need to set data building_h from one table to another with respect to the spatial relationships between data in these tables. Spatial data in the source table is polygon type and in destination table - points. I made a query for points and polygons which intersect. It works nice. But there are some points that do not intersect with polygons. My idea is to define for each of these points the closest polygon and take the building_h value from it. For this purpose, I wrote the function which takes the id of the point and returns the building_h value. And it works ok in the test.
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 值).它持续了几个小时才停止.
But when I run the query for the whole table, it executes forever.. (for ~3000 rows with NULL value). It lasted several hours before I stopped it.
UPDATE building_from_landuse_spgg AS bfl SET
building_h = (SELECT closest_pol(bfl.id))
WHERE bfl.building_h IS NULL;
你知道我做错了什么吗?
Do you have an idea what I did wrong?
推荐答案
在您的 WHERE 子句中，您使用 ST_Intersects 和在查询时创建的 0.006 缓冲区.考虑对您的缓冲区使用部分 gist 索引(如果出于某种原因必须使用它们):
In your WHERE clause you're using ST_Intersects with 0.006 buffer that is created in query time. Consider using a partial gist index with this buffer of yours (if for whatever reason you must use them):
CREATE INDEX idx_buffer ON building_from_landuse_spgg USING gist (ST_Buffer(geom_centr, 0.0006,'quad_segs=8'));
我认为您不需要为此使用函数，因为您可以将函数内部的查询用作 UPDATE 语句中的子查询.但是如果你有理由坚持这个函数，你可以去掉 SELECT 来调用它:
I'd argue that you do not need a function for that, since you could use the query inside of the function as a subquery in your UPDATE statement. But if you have your reasons to stick to the function, you can get rid of the SELECT to call it:
UPDATE building_from_landuse_spgg
SET building_h = closest_pol(id)
WHERE building_h IS NULL;
编辑:正如@JGH 正确提到的(见评论)，使用缓冲区的效率低于简单地使用 ST_DWithin.因此，如果您负担得起，请在 WHERE 子句中使用的几何图形中创建索引 ..
EDIT: As correctly mentioned by @JGH (see comments), using buffers is less efficient than simply using ST_DWithin. So, if you can afford it, create an index in the geometries used in the WHERE clause ..
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 记录感兴趣:
.. and also partially indexing building_h would speed up things a bit, since you're only interested in the NULL records:
CREATE INDEX idx_landuse_building_h ON building_from_landuse_spgg (building_h)
WHERE building_h IS NULL;
或者，如果您更喜欢更广泛的索引但仍将 NULL 值放在 第一类 ..
Or if you prefer a broader index but still putting NULL values in the first class ..
CREATE INDEX idx_landuse_building_h ON building_from_landuse_spgg
(building_h NULLS FIRST);
也许可以考虑将您的函数代码放在子查询中，例如
And perhaps consider putting the code of your function in a subquery, e.g.
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;
进一步阅读:
这篇关于如何根据这些表中的空间关系几何将表中的数据设置为另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
