首页 文章

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

提问于
浏览 次
2

我对运行查询和脚本的Postgres和PostGIS有一定的经验，但没有创建功能的经验 . 我觉得我想要实现的东西比我见过的例子复杂得多，所以我希望有人可以帮助我 .

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

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

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 作为参数 . 因此，我想知道以下两件事：

  • 有没有办法根据与提供的几何体的空间交集创建表的子集并迭代每条记录，执行必要的更新？如果是，那么如何指定此功能？

  • 我可以提供表名作为函数的参数吗？

我不确定最好的方法，所以如果有人可以告诉我，我想做什么是可能的，如果是这样，让我开始指定一个功能，我会非常感激 .

干杯 .

postgresql function sql-update postgis

1 回答

  • 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;

    几点说明：

    另一个潜在的大成本节省是将 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 .

    回复于

相关问题