How to add non geometry column into geopackage using native geopackage SQL / functions

Question

I'd like to add non geometry column into geopackage using its native SQL / functions only.

I'm working with large set of SQLite databases and I'm using geopackage(s) to get spatial data into the set/query. I'd like to modify the data inside geopackage within the SQL script - not to use any other environments, but SQLite editors/tools only. In this particular case Dbeaver.

I'm aware that due to the geopackage standards, using the standard SQL (SQLite) commands it might lead to errors or broken indexes. I did go trough the available info on https://www.geopackage.org/ but I did not read the whole OGC specs nor did review the complete C code tree.

I did the usual:

ALTER TABLE parcel_polygon ADD COLUMN parid TEXT

Which is successfully done. Apparently this is not all to it though as when you try to:

UPDATE parcel_polygon SET parid = 'X'

It yields error:

no such function: ST_IsEmpty

There is only one post as of 11/21/21 I found which is related.

Answer 1

I did not found an exact answer on how to work with goepackage just by its own; nevertheless found solution which utilizes spatialite_mod extension, providing so far desired outcome.

It would be great if all what's needed for the absolute basics be available on the https://www.geopackage.org/.

Expanding on Jay Cummins answer. For which I'm very thankful as it pointed me to right direction after countless hours/days of figuring the geopackage without the need to use other tools, but SQL editor (DBeaver).

I have also tested loading the extensions into :memory: SQLite db and it does work. Providing that geopackage is attached as for example:

attach 'C:\Users\x\desktop\parcel_polygon.gpkg' as parcel_polygon_;

I do reference the table in gpkg (should I use the :memory:) as for example:

SELECT * FROM parcel_polygon_.parcel_polygon;
UPDATE parcel_polygon_.parcel_polygon  SET parid = 'X';

In order to work with the gpkg using the SQLite in DBeaver. You need to load the Spatialite extension module. The main module 'mod_spatialite' itself depend on few other modules.

The easiest way to get all the files at once and from reliable source is to install QGIS. Then add to your path for example:

C:\Program Files\QGIS 3.22.1\bin

Then set the SQLite driver - enable extension loading.

Restart Dbeaver. Load the extension.

SELECT load_extension('mod_spatialite');

Result:

After that test - ALTER TABLE:

ALTER TABLE parcel_polygon ADD COLUMN Parid TEXT;

or

ALTER TABLE parcel_polygon DROP COLUMN Parid;

and consequently UPDATE (on non geometry column in this case):

UPDATE parcel_polygon SET parid = 'X';

all works correctly.

Answer 2

You have two options: Use a GeoPackage without spatian index, or load a few GeoPackage specific functions from some extension, as you already did.

The reason for the behavior that you have seen comes from the standard and is documented there https://www.geopackage.org/spec131/index.html. GeoPackage database has a bunch of triggers which are required to keep the data tables and the r-tree tables which are used as spatial indexes synchronized. One trigger for an example:

/* Conditions: Update of any column
               Row ID change
               Non-empty geometry
   Actions   : Remove record from rtree for old <i>
               Insert record into rtree for new <i> */
CREATE TRIGGER rtree_<t>_<c>_update3 AFTER UPDATE ON <t>
  WHEN OLD.<i> != NEW.<i> AND
       (NEW.<c> NOTNULL AND NOT ST_IsEmpty(NEW.<c>))
BEGIN
  DELETE FROM rtree_<t>_<c> WHERE id = OLD.<i>;
  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;

So updata to any column af a spatial table that has a spatial index requires a few ST_x functions. GeoPackage extension to SQLite requires these functions which are defined in the standard document:

• ST_IsEmpty
• ST_MinX
• ST_MaxX
• ST_MinY
• ST_MinY

Those functions are not a part of core SQLite so they must be loaded somewhere. SpatiaLite extension is one way and for example GDAL has implemented its own functions.

The GPKG functions are only required for spatial indexes and GeoPackage does not need to have spatial indexes. You can use for example GDAL ogr2ogr and create a table without spatial index by using a layer creation option -lco SPATIAL_INDEX=NO https://gdal.org/drivers/vector/gpkg.html.

I made a test with command

ogr2ogr -f gpkg test_gpkg.gpkg test.gpkg input_layer -lco spatial_index=NO -nln test_table

Now DB Browser executes update commands without errors.

ALTER TABLE test_table ADD COLUMN parid TEXT;

UPDATE test_table SET parid = 'X';

I did not test the GDAL function DisableSpatialIndex but probably it also makes a spatial table to allow all kind of edits with any SQLite tools without a need to write a new GeoPackage.