2

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.

1
  • You have to wrap uppercase identifiers in ".
    – geozelot
    Oct 31 '21 at 17:48
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.