PostgreSQL从标准输入流中复制表达式

Question

我正在尝试使用COPY FROM STDIN命令将数据导入我的表中。 我的表中有一个类型为geometry的列。我的命令大致如下...

COPY "WeatherStations" ("Station_ID", "Station_Code", "Station_Name", "Station_Location") FROM stdin;
1       KAVP    WILKES-BARRE    ST_GeomFromText('POINT(41.338055 -75.724166)')
2       KOKV    WINCHESTER      ST_GeomFromText('POINT(39.143333 -78.144444)')
3       KSHD    SHENANDOAH      ST_GeomFromText('POINT(38.263611 -78.896388)')
...

然而，我认为它试图插入文本"ST_GeomFromText('POINT..."并失败，而不是评估表达式并插入表达式的结果。有人知道这里可能出了什么问题，以及我如何获得实际的几何对象插入？

- elynnaie

Answer 1

我曾经很费劲地想弄清楚如何使用COPY FROM STDIN命令批量复制/加载几何数据到PostGIS中，但是我找不到关于这个主题的官方文档。

在批量加载期间更改列（ ALTER TABLE / SET DATA TYPE / USING）对我来说不是一个选择，因为它只支持Geometry类型的PostGIS 2.0+，而使用临时表也是不可接受的。

事实上，有一种直接的方法可以做到这一点（至少在PostGIS 1.5.2+中）。您可以通过使用简单的WKT（Well-known text）表示法重写您的复制语句的数据，以此简单地表示您的几何数据：

1       KAVP    WILKES-BARRE    POINT(41.338055 -75.724166)
2       KOKV    WINCHESTER      POINT(39.143333 -78.144444)
3       KSHD    SHENANDOAH      POINT(38.263611 -78.896388)

如果您在几何列上强制执行了SRID约束，则必须使用以下语法（在此示例中，SRID为4326），称为EWKT（扩展已知文本，它是PostGIS特定格式）：

1       KAVP    WILKES-BARRE    SRID=4326;POINT(41.338055 -75.724166)
2       KOKV    WINCHESTER      SRID=4326;POINT(39.143333 -78.144444)
3       KSHD    SHENANDOAH      SRID=4326;POINT(38.263611 -78.896388)

结束语：在“POINT”和开括号“（”之间不得有空格，否则COPY将依然返回错误，指出几何数据格式无效。

- Fulvio

---

对于在家玩的任何人，这也适用于CSV的“COPY FROM”。这应该在PostGIS中有更好的文档记录。我花了一个下午的时间才找到你的帖子。非常感谢您的贡献。 - kindrobot

仍然适用于2017年。至少我可以避免使用shapely的缓慢wkb_hex函数。 - Michael

Answer 2

您可以省略包装文本的函数，将其导入到一个临时表中，并在该步骤中使用执行转换的函数将其插入/选择到永久表中。

INSERT INTO "WeatherStations"
  ("Station_ID", "Station_Code", "Station_Name", "Station_Location")
  SELECT "Station_ID", "Station_Code", "Station_Name",
         ST_GeomFromText("Station_Location")
    FROM "TempWeatherStations";

- kgrittn

---

这基本上就是我最终所做的。我将数据添加到文本列中，创建一个临时几何列，转换数据，然后删除旧列并重命名新列。虽然不是最美观的方法，但它能够正常工作。 - elynnaie

如果您要在一张表中完成这个操作，我认为您可以使用USING子句来更改列类型进行转换，从而稍微简化该过程。 - kgrittn

不知道 ALTER TABLE / SET DATA TYPE / USING 语法！比添加和删除列更漂亮。 - elynnaie

Answer 3

你需要将所有数值保存在 .csv 文件中，然后尝试如下编程：

CAT /path/file/demo.csv | psql -u <username> -h <localhost> -d<database> 
-c "COPY "WeatherStations" ("Station_ID", "Station_Code", "Station_Name", 
"Station_Location") FROM stdin;"

这样做会有效。

- Dhananjayan K

Answer 4

点的值看起来像这样：0101000020E6100000DA722EC555552B40CDCCCCCCCC0C4840。

我通常在我的表中保留纬度和经度列，并使用触发器构建空间数据。

否则，我不知道如何从标准输入中复制POINT。

- vyegorov