postgresql - 如何插入到 Postgresql 几何列

Question

我有一个 WKT 数据，如下所示:

GEOMETRYCOLLECTION(
POINT(-1763555.1165955865 310640.0829509564),
POINT(-1421117.229877997 -300856.1433304538)
)

默认投影为“EPSG:3857”。在 postgresql 中，我创建了一个包含通用几何列的父表和几个包含特定几何类型列的子表。架构如下所示:

# parent table with generic geometry column "geom"
CREATE TABLE "public"."layer_261_" (
"id" int4 DEFAULT nextval('layer_261__id_seq'::regclass) NOT NULL,
"feature_id" int4 DEFAULT 0 NOT NULL,
"feature_name" varchar(200),
"feature_type" varchar(50),
"geom" "public"."geometry",
"object_id" int4 DEFAULT 0 NOT NULL,
"row_id" int4 DEFAULT 0 NOT NULL
) 
WITH (OIDS=FALSE);
ALTER TABLE "public"."layer_261_" ADD CHECK (st_srid(geom) = 3857);
ALTER TABLE "public"."layer_261_" ADD CHECK (st_ndims(geom) = 2);
ALTER TABLE "public"."layer_261_" ADD PRIMARY KEY ("id");

# child table which is supposed to contain only POINT data type
CREATE TABLE "public"."layer_261_points" (
"id" int4 DEFAULT nextval('layer_261__id_seq'::regclass) NOT NULL,
"feature_id" int4 DEFAULT 0 NOT NULL,
"feature_name" varchar(200),
"feature_type" varchar(50),
"geom" "public"."geometry",
"object_id" int4 DEFAULT 0 NOT NULL,
"row_id" int4 DEFAULT 0 NOT NULL
)
INHERITS ("public"."layer_261_") 
WITH (OIDS=FALSE);
ALTER TABLE "public"."layer_261_points" ADD CHECK (st_ndims(geom) = 2);
ALTER TABLE "public"."layer_261_points" ADD CHECK (geometrytype(geom) = 'POINT'::text);
ALTER TABLE "public"."layer_261_points" ADD CHECK (st_srid(geom) = 3857);

那么，我怎样才能插入我的数据(两个指向数据库)？例如，我不确定是否应该将点的坐标转换为经纬度。此外，我不确定是否应该插入 GEOMETRYCOLLECTION 或所有点。

编辑

我刚刚尝试使用真实数据点执行查询:

INSERT INTO layer_261_ (geom) VALUES (ST_Point(105177.3509204, -85609.471679397))

但结果我得到了这个错误信息:

new row for relation "layer_261_" violates check constraint "enforce_srid_geom"

有人知道怎么解决吗？

编辑

此查询导致完全相同的错误消息:

INSERT INTO layer_261_ (geom) VALUES (ST_SetSRID(ST_Point(105177.3509204, -85609.471679397), 
4326))

Answer 1

您只能将 WKT 插入父表，因为点表不接受 GEOMETRYCOLLECTION :

INSERT INTO "public"."layer_261_" ("geom", <other columns>)
VALUES (ST_GeomFromText(<your WKT>, 3857), <other values>);

一旦父表中有了数据，您就可以轻松地从 GEOMETRYCOLLECTION 转换为分开POINT使用ST_Dump()并将它们插入到点表中:

INSERT INTO "public"."layer_261_points" ("geom", <other columns>)
  SELECT p.geom, <other columns>
  FROM "public"."layer_261_" m, ST_Dump("geom") p
  WHERE ...;

当然你也可以忘记第一步而做 ST_Dump(ST_GeomFromText(<your WKT>, 3857))在第二步中，但这不太直观且更容易出错。

请注意 ST_Dump()是 table function所以它应该用在 FROM 中条款。然后它可以使用函数之前指定的表中的列。

您使用 ST_Point() 时遇到的错误是因为你的几何图形有一个 NULL SRID。您应该使用 ST_SetSRID() 明确设置它(我对 PostGIS 的一大烦恼...)。