Geopackage: update query generates an error "no such function: ST_IsEmpty"

Question

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?

Answer 1

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;

Answer 2

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>)) 
BEGIN
INSERT OR REPLACE INTO rtree_<t>_<c> VALUES (
        NEW.<i>,
        ST_MinX(NEW.<c>), ST_MaxX(NEW.<c>),
        ST_MinY(NEW.<c>), ST_MaxY(NEW.<c>)   );
END;

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.

Answer 3

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')