How do I create a geometry using WKT in SQL INSERT statement for geopackage layer in QGIS

Question

In QGIS 3.18.2, I am trying to create a geometry from WKT in a geopackage layer using SQL. My ultimate goal is to use this in a trigger, but first I need to get this INSERT STATEMENT working.

In the SQL below, the layer I am trying to insert into is "Crown". The geometry column is "geom". The geometry type for "Crown" is POLYGON. (I have double checked all this).

I have tried

INSERT INTO Crown(geom)
VALUES ('POLYGON((0 5, 4 0, 0 -3, -2 0, 0 5))');

and

INSERT INTO Crown(geom)
VALUES ('SRID=27700;POLYGON((0 5, 4 0, 0 -3, -2 0, 0 5))');

and

INSERT INTO Crown(geom)
VALUES  (
            st_geomfromtext(
                'POLYGON((0 5, 4 0, 0 -3, -2 0, 0 5))'
            )
        );

and

INSERT INTO Crown(geom)
VALUES  (
            st_geomfromtext(
                'SRID=27700; POLYGON((0 5, 4 0, 0 -3, -2 0, 0 5))'
            )
        );

On executing each of the above, I receive the following error (or similar adjusted for the SQL used).

Query execution error on CREATE TEMP VIEW _tview AS insert into Crown(geom) values ('POLYGON((0 5, 4 0, 0 -3, -2 0, 0 5))'): 1 - near "insert": syntax error

I have tried the all the above but also with aother attributes. I can see that a record is inserted in the table with values of the other attributes as supplied in the SQL INSERT statement, but the record has no geometry.

I have also tried creating a geometry using a function like st_buffer(), and this works. But I do need to create the geometry from WKT format.

Answer 1

You can try with

INSERT INTO mytable(geom) VALUES (GeomFromText('MULTIPOLYGON(((417693.0394878195 253646.2835958739,406023.2932235879 346280.7062234726,486677.6282769066 365863.0401758771,417693.0394878195 253646.2835958739)))', 27700))

Tested with

ogrinfo mygpkg.gpkg -sql "INSERT INTO mytable(geom) VALUES (GeomFromText('MULTIPOLYGON(((417693.0394878195 253646.2835958739,406023.2932235879 346280.7062234726,486677.6282769066 365863.0401758771,417693.0394878195 253646.2835958739)))', 27700))"

If you have any other issue, it may be related to having a table name with case issue e.g Crown whereas my test has been done using lower case table name