I created a geopackage using QGIS 2.18.3 and it works fine with QGIS. I am using SQLiteStudio 3.1.1 to add check constraints and again, the geopackage has been working fine. However, if I try to execute an update query that sets a single column to NULL (update ParkTrees set system_id = null) using SQLiteStudio I get this error:

[07:33:14] Error while executing SQL query on database 'ParkTreesFileSpec': no such function: ST_IsEmpty

How do I edit data in geopackage created by QGIS without using QGIS?

  • Just drop the trigger if you don't want it. However a null id probably isn't going to be valid later. A better solution would be to load an extension that implements the function (like spatialite). What are you really trying to do here?
    – BradHards
    Jul 24, 2017 at 22:50
  • I'm using QGIS as a field data collection tool, in this case tree locations. Once collected, I save the data as WKT to load into another system. The other system auto-increments the system_id field but it was incorrectly populated by the data collection person.
    – Colin
    Jul 24, 2017 at 23:46
  • Where does the trigger come from? Is it created by QGIS when it creates the geopackage?
    – Colin
    Jul 24, 2017 at 23:47
  • Its created by a CREATE TRIGGER command. When depends on the tools. You might want to edit the important details into the question.
    – BradHards
    Jul 25, 2017 at 0:12
  • 3
    Did you find a solution? I have the same problem and I would like to add ST_ functions to a gpkg template
    – aurel_nc
    Mar 7, 2018 at 2:45

3 Answers 3


Before changing anything substantial in a GeoPakcage with SQLite tools, you must first load the SpatiaLite extension. I.e.:

SELECT load_extension("mod_spatialite");

UPDATE table SET field = value;

ST_IsEmpty, ST_MinX, ST_MaxX, ST_MinY, and ST_MaxY are needed for maintaining the R-Tree spatial index. Any valid GeoPackage database that implements spatial index is using them. As an example SQL for the first trigger that is defined in the standard:

Define Triggers to Maintain Spatial Index Values

For each spatial index in a GeoPackage, corresponding insert, update and delete triggers that update the spatial index SHALL be present on the indexed geometry column. These spatial index triggers SHALL be defined as follows:

/* Conditions: Insertion of non-empty geometry
Actions   : Insert record into rtree */ 
CREATE TRIGGER rtree_<t>_<c>_insert AFTER INSERT ON <t>   
WHEN (new.<c> NOT NULL AND NOT ST_IsEmpty(NEW.<c>)) 
        ST_MinX(NEW.<c>), ST_MaxX(NEW.<c>),
        ST_MinY(NEW.<c>), ST_MaxY(NEW.<c>)   );

It does not mean that if you create a GeoPackage with QGIS you could only edit it with QGIS. You can use any application that implements the SQL functions which are specified in the standard. Obviously it is a bad idea to edit GeoPackage with tools which are not made for it if you need to drop triggers or something like that. If you go that way you must undestand very well what you are doing.


I couldn't get this to work using DBeaver or the sqlite3 python package, but the python OGR bindings worked for me:

from osgeo import ogr
conn = ogr.Open('/path/to/geopackage.gpkg', 1) # open in update mode
conn.ExecuteSql('UPDATE the_table SET the_column = NULL')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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