How to insert to Postgresql geometry column

Question

I have a WKT data which looks like so:

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

The default projection is 'EPSG:3857'. In postgresql I created a parent table whith generic geometry column and several child tables with columns of specific geometry type. The schema looks like so:

# 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);

So, how can I insert my data (two points to the database)? For example, I'm not sure whether I should convert points' coordinates to lat-lon. And besides, I'm not sure whether I should insert GEOMETRYCOLLECTION or all points one by one.

EDIT

I've just tried to execute a query with a real data point:

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

But as a result I got this error message:

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

Does anybody know how to fix it?

EDIT

This query leads to the very same error message:

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

Patrick Patrick · Accepted Answer · 2016-03-13T17:05:25

You can only insert the WKT into the parent table because the point table won't accept a GEOMETRYCOLLECTION:

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

Once you have the data in the parent table you can easily convert from the GEOMETRYCOLLECTION to separate POINTs using ST_Dump() and insert those in the point table:

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

You can of course also forget about the first step and do ST_Dump(ST_GeomFromText(<your WKT>, 3857)) in the second step but that is less intuitive and more prone to errors.

Note that ST_Dump() is a table function so it should be used in a FROM clause. It can then use columns from tables specified before the function.

The error you were getting from using ST_Point() is because you the geometry has a NULL SRID. You should set that explicitly with ST_SetSRID() (one of my great annoyances with PostGIS...).

How to insert to Postgresql geometry column

1 Answers