5

I've been trying to work out a way to create relationships at the database level of geopackages.

Simple scenario for example - Table 'clearing' is related to table 'sourcefile'. The solution is to setup a foreign key.

This question here provides some reference for foreign key setup in geopackages. Foreign Keys in Geopackage

At first I tried using DB Browser for SQLite to create the relationships. The thing I discovered first is that there is a limitation with SQLite database (and as such, Geopackages) which prevents the update of existing tables (ie: Alter table statements). Therefore, the foreign keys need to be setup during the 'CREATE' process.

The only way to create a table, with a Foreign Key, (that I know of) - is by using a SQL statement. The following statement will create the foreign keys successfully when run in DB Browser for SQlite. (where the tables are called _dev_permit and _dev_sourcefile)

CREATE TABLE "_dev_permit" (
    "fid"   INTEGER NOT NULL,
    "geom"  MULTIPOLYGON,
    "cp_number" TEXT(20),
    "permit_details"    TEXT,
    "status"    TEXT,
    "sourcefile"    TEXT UNIQUE,
    PRIMARY KEY("fid" AUTOINCREMENT)
    FOREIGN KEY (sourcefile)
        references _dev_sourcefile (filename)
);

Whilst this creates the table successfully, what it doesn't do is make the table available when viewing in GIS applications (QGIS as example). This is because the above sql run from within DB Browser does not trigger the other statements which populate the gpkg_contents tables and other required tables and triggers.

So I tried to run the above sql, inside of QGIS, using the DB Manager tool. Before running it, I tested the GUI driven 'Create Table' function from inside of QGIS DB Manager. This created a table correctly AND also made the table available inside of QGIS, which - upon inspection, meant that the GUI also triggered the creation of records in the gpkg_contents tables and other required tables for a gpkg. But of course, no foreign key.

The GUI has a place to enter SQL statements, so I then tried that. The table was created, but ONLY that table. No entry in the gpkg_contents. Foreign key was there, but still essentially not a valid geopackage table. Screen grab below shows the Create Table function on the right, with the sql statement on the left.

enter image description here

The conclusion ive come to is that the Create Table Gui will trigger the other required processes for geopackage table creation, but the SQL execution will only execute the sql in the statement.

So in summary:

  • The only way to create foreign keys is with an SQL statement and ONLY during the CREATE process.
  • I'm not aware of any facility inside of QGIS or anywhere else, which will trigger the population of the required gpgk_* tables in the geopackage database when running the above required SQL.

So the question is - can someone describe what the process would be for creating a new geopackage table which has a foreign key??

1
  • What GDAL version do you have?
    – user30184
    May 24, 2023 at 7:40

1 Answer 1

2

If you want to create the table manually I suppose that you can use the SpatiaLite functions for creating the required triggers and rows into the metadata tables. See https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html and funtions gpkgAddGeometryColumn, gpkgAddGeometryTriggers, and gpkgAddSpatialIndex.

The support for relations is relatively new for GDAL https://gdal.org/drivers/vector/gpkg.html#relationships

Relationships

New in version 3.6.

Relationship retrieval is supported, respecting the OGC GeoPackage Related Tables Extension. If the Related Tables Extension is not in use then relationships will be reported for tables which utilize FOREIGN KEY constraints.

Relationship creation, deletion and updating is supported since GDAL 3.7. Relationships can only be updated to change their base or related table fields, or the relationship related table type. It is not permissible to change the base or related table itself, or the mapping table details. If this is desired then a new relationship should be created instead.

Probably because SQLite is not very flexible with foreign key constraints the GeoPackage Related Tables Extension http://www.geopackage.org/spec/related-tables/ builds on top of an interim table that defines the relations.

CREATE TABLE 'gpkgext_relations' (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  base_table_name TEXT NOT NULL,
  base_primary_column TEXT NOT NULL DEFAULT 'id',
  related_table_name TEXT NOT NULL,
  related_primary_column TEXT NOT NULL DEFAULT 'id',
  relation_name TEXT NOT NULL,
  mapping_table_name TEXT NOT NULL UNIQUE
 );

I guess that it is not possible to create relations with QGIS but you can find an example about how to create them with Python and SQL from the GDAL autotests starting from here https://github.com/OSGeo/gdal/blob/master/autotest/ogr/ogr_gpkg.py#L6533

The next QGIS version will contain this Pull Request https://github.com/qgis/QGIS/pull/49504 and hence support the Related Tables Extension.

1
  • great thanks for that info and confirming what I thought about the metadata tables. ....... Just to add, there isn't necessarily a hard restriction of 'I have to do this within QGIS'........ QGIS seems to have the facilities to create those additional tables within it already, so was hoping someone clever had leveraged it already. But alas I did suspect that adding the metadata tables manually was going to be the way to go. Let me digest this and ill get back to this.
    – nr_aus
    May 24, 2023 at 14:45

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.