Create geometry column in Geopackage with SQL

Question

How to create a table with geometry column that is recognized as spatial layer? I use the DB manager from QGIS 3.4.2

I use the following:

CREATE TABLE 'sample_attribute_with_geometry' (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  geometry MULTIPOLYGON NOT NULL,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  date INTEGER NOT NULL,
  source TEXT NOT NULL)

The table created is not spatial.

How could i do?

Answer 1

Have a look at the database structure of GeoPackage in Figure 1 of the GeoPackage standard http://www.geopackage.org/spec/

Read also the table definitions of the metadata tables from the standard:

gpkg_contents Table Definition SQL
CREATE TABLE gpkg_contents (
  table_name TEXT NOT NULL PRIMARY KEY,
  data_type TEXT NOT NULL,
  identifier TEXT UNIQUE,
  description TEXT DEFAULT '',
  last_change DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  min_x DOUBLE,
  min_y DOUBLE,
  max_x DOUBLE,
  max_y DOUBLE,
  srs_id INTEGER,
  CONSTRAINT fk_gc_r_srs_id FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys(srs_id)
);


gpkg_geometry_columns Table Definition SQL
CREATE TABLE gpkg_geometry_columns (
  table_name TEXT NOT NULL,
  column_name TEXT NOT NULL,
  geometry_type_name TEXT NOT NULL,
  srs_id INTEGER NOT NULL,
  z TINYINT NOT NULL,
  m TINYINT NOT NULL,
  CONSTRAINT pk_geom_cols PRIMARY KEY (table_name, column_name),
  CONSTRAINT uk_gc_table_name UNIQUE (table_name),
  CONSTRAINT fk_gc_tn FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),
  CONSTRAINT fk_gc_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id)
);

This should work for your original example:

Create table:

CREATE TABLE 'sample_attribute_with_geometry' (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  geometry MULTIPOLYGON NOT NULL,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  date INTEGER NOT NULL,
  source TEXT NOT NULL);

Add metadata into gpkg_contents (with meaningful numbers for min and max x ann y):

insert into gpkg_contents values ('sample_attribute_with_geometry', 'features','some description','sample_attribute_with_geometry','2019-02-19T10:49:06.022Z',74572.400000,6631280.000000,732294.000000,7774890.000000,31370);

Add metadata into gpkg_geometry_columns

insert into gpkg_geometry_columns values ('sample_attribute_with_geometry','geometry','MULTIPOLYGON',31370, 0, 0 )

Notice that because of the foreign key constraint you must already have an entry for epsg:31370 in gpkg_spatial_ref_sys or otherwise the entries into gpkg_contents will fail.

I do not understand how you have managed to do things in the order that you wrote into your comment. Foreign key constraints should allow only order Create table - Insert into gpkg_contents - Insert into gpkg_geometry_columns. You must have constraints turned off from the SQLite PRAGMA https://sqlite.org/foreignkeys.html.