postgresql - 创建 Postgres/PostGIS 函数以根据空间查询更新表值

Question

我在 Postgres 和 PostGIS 运行查询和脚本方面有相当多的经验，但没有创建函数的经验。我觉得我想要实现的目标比我看到的例子要复杂得多，所以我希望有人能帮助我。

我正在创建一个 Web 应用程序，它允许用户根据与 map 上绘制的多边形的交集以及输入到表单中的一些值来更新存储在空间表中的地 block 边界记录。我不知道是否有一种方法可以将数据库记录的子集存储在数组中并对其进行迭代，依次更新每条记录，或者我是否必须在函数中运行单独的更新脚本。我也不确定是否可以将表名作为参数传递给函数，因为我只想运行该函数并让它在不同的表上工作。

如果我要创建一个函数，通过简单地运行一堆单独的更新脚本来完成我想要它做的所有事情，它可能看起来像下面这样(函数没有实际测试):

CREATE OR REPLACE FUNCTION updateLots(wkt_geom text, tablename varchar(25), landuse varchar(25), density NUMERIC(4,1))
  RETURNs VOID AS
$$
  BEGIN
    UPDATE [tablename] SET landuse = [landuse] WHERE ST_Intersection(geom, GeomFromWKT([wkt_geom], 3857));
    UPDATE [tablename] SET density = [density] WHERE ST_Intersection(geom, GeomFromWKT([wkt_geom], 3857)) WHERE landuse = 'Residential';
    UPDATE [tablename] SET density = NULL WHERE ST_Intersection(geom, GeomFromWKT([wkt_geom], 3857)) WHERE landuse != 'Residential';
    UPDATE [tablename] SET yield = area / 10000 * [density] WHERE ST_Intersection(geom, GeomFromWKT([wkt_geom], 3857));
END;
$$
LANGUAGE plpgsql;

虽然这种方法可以避免我从服务器运行多个嵌套的数据库脚本，但它似乎效率低下并且 Postgres 不会接受 tablename 作为参数。因此，我想知道以下两件事:

1. 有没有一种方法可以根据与提供的几何图形的空间交集创建表格的子集，并迭代每条记录，执行必要的更新？如果是，如何指定此功能？
2. 我可以提供表名作为函数的参数吗？

我不确定继续进行的最佳方式，所以如果有人能告诉我我想做的事情是否可行，如果可以让我开始指定一个函数，我将不胜感激。

干杯。

Answer 1

在 PostgreSQL 中，您可以将表名传递给函数，然后对该表进行操作，但您必须 EXECUTE a dynamic query ，这是低效的，因为必须在每次函数调用时解析和计划查询。如果您只有几个表，那么最好将几个表中的每一个的命令放在一个函数中:该函数更大，但您只需调用它一次并且 the queries can be planned and stored for future usage by the query planner .

根据几何之间的交集来制作表格的子集可能不是一件好事。相反，处理你的 UPDATE 命令，它可以被大大优化:

CREATE FUNCTION updateLots(wkt_geom text, lu varchar(25), dens NUMERIC(4,1))
RETURNS void AS $$
BEGIN
  UPDATE t1 SET landuse = lu,
                density = (CASE WHEN lu = 'Residential' THEN dens END), -- ELSE NULL
                yield = area * 0.0001 * dens
  WHERE ST_Intersection(geom, GeomFromWKT(wkt_geom, 3857));

  ...; -- Same for other tables

END; $$ LANGUAGE plpgsql STRICT;

一些注意事项:

• 我 renamed the parameters such that they are different from column names .
• 在一个命令中更新所有列。这意味着 ST_Intersection() 仅被调用一次 - 可能会节省大量成本。
• 乘以 0.0001 而不是除以 10000。
• 该函数被声明为STRICT:如果未提供参数，则不要调用该函数。

另一个可以节省大量成本的方法是将 wkt_geom 作为 geometry 而不是 text 传递。如果这可以在您的情况下完成，那么您就不必执行昂贵的 ST_GeomFromWKT()。

因为你想用传递的表名运行函数，你应该使用以下版本:

CREATE FUNCTION updateLots(wkt_geom geometry, tablename varchar(25), lu varchar(25), dens NUMERIC(4,1))
RETURNS void AS $$
BEGIN
  EXECUTE format('
    UPDATE %I SET landuse = %L,
                  density = (CASE WHEN %2$L = ''Residential'' THEN $1 END),
                  yield = area * 0.0001 * $1
    WHERE ST_Intersection(geom, $2)', tablename, lu)
  USING dens, wkt_geom;
END; $$ LANGUAGE plpgsql STRICT;

在这种情况下，在为每个表名调用此函数一次之前，您绝对应该将 wkt_geom 数据转换为 geometry。