空间数据SQL重投影函数问题 [英] Spatial Data SQL Reprojection Function issues

问题描述

您好，我只是学习postGIS，因此也学习了postgresql(9.1)，并试图通过创建sql函数来重新投影一些空间数据来一次又一次地复制相同的代码，以节省一些时间.

Hello I am just learning postGIS and thus postgresql (9.1) and am trying to save some time copying the same code over and over by creating an sql function to reproject some spatial data.

Create Function reproject_shapefile(text,text,numeric) returns void as $$

    -- Reprojects shapefiles given that they follow the pattern "gid * the_geom"

    CREATE TABLE $2 AS
        SELECT *, ST_Transform(the_geom,$3) AS the_geom2
        FROM $1;
    Alter table $2 add Primary Key (gid);
    Alter table $2 drop column the_geom;
    Alter table $2 rename column the_geom2 to the_geom;
$$ Language SQL;

我仔细阅读了文档，详细说明了如何执行此操作，但是每次尝试通过pgAdmin中的sql编辑器创建函数时，都会收到以下错误消息:

I read over the docs specifying how to do this, but everytime I try to create the function from the sql editor in pgAdmin, I receive the following error:

ERROR:  syntax error at or near "$2"
LINE 5:     CREATE TABLE $2 AS
                     ^

********** Error **********

ERROR: syntax error at or near "$2"
SQL state: 42601
Character: 175

与python中的错误消息不同，这绝对没有告诉我任何用处，因此我希望有人可以向我指出正确的方向，以解决该错误.

Unlike the error messages in python, this tells me absolutely nothing of use, so I am hoping that someone can point me in the right direction on how to fix this error.

如果有某种方法可以使用python执行相同的功能，请随时将其发布为解决方案，因为与古老的SQL相比，python语法对我来说更容易理解.

If there is some way to perform this same function using python feel free to post that as a solution instead/ as well, as python syntax is much easier for me to understand than ancient SQL.

任何帮助将不胜感激！

推荐答案

您不能以这种形式编写动态SQL.参数只能传入值，而不能传入标识符.这样的事情在SQL函数中是不可能的:

You can not write dynamic SQL in this form. Parameters can only pass in values, not identifiers. Something like this is impossible in an SQL function:

CREATE TABLE $2 AS

您需要为此编写一个plpgsql函数，并使用 EXECUTE .可能看起来像这样:

You need to write a plpgsql function for that and use EXECUTE. Could look like this:

CREATE OR REPLACE FUNCTION reproject_shapefile(text, text, numeric)
  RETURNS void as $$
BEGIN

EXECUTE '
   CREATE TABLE ' || quote_ident($2) || ' AS
   SELECT *, ST_Transform(the_geom,$1) AS the_geom2
   FROM  ' || quote_ident($1)
USING $3;

EXECUTE 'ALTER TABLE ' || quote_ident($2) || ' ADD PRIMARY KEY (gid)';
EXECUTE 'ALTER TABLE ' || quote_ident($2) || ' DROP COLUMN the_geom';
EXECUTE 'ALTER TABLE ' || quote_ident($2) || ' RENAME column the_geom2 TO the_geom';

END;
$$ Language plpgsql;

要点

• plpgsql函数，而不是sql
• EXECUTE 任何具有动态标识符的查询
• 使用 quote_ident 来防范SQLi
• 使用USING子句传递值以避免投掷和引用疯狂.

Major points

• plpgsql function, not sql
• EXECUTE any query with dynamic identifiers
• use quote_ident to safeguard against SQLi
• pass in values with the USING clause to avoid casting and quoting madness.

这篇关于空间数据SQL重投影函数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！