3

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 '17 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 '17 at 23:46
  • Where does the trigger come from? Is it created by QGIS when it creates the geopackage? – Colin Jul 24 '17 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 '17 at 0:12
  • 2
    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 '18 at 2:45
1

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.

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.