2

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?

  • did you add it to geomerty_columns? – Ian Turton Feb 18 at 15:27
  • You should have a look at the specification geopackage.org/spec and then add reference to the new table into gpkg_contenst and gpkg_geometry_columns as well. – user30184 Feb 18 at 15:29
  • i don't know. Is it other tables? When i browse the geopackage in QGIS i don't see other tables. Do i need to insert the column_name, srid in gpkg_contenst and gpkg_geometry_columns? – jlSta Feb 18 at 20:44
  • okay, i did a SELECT * FROM gpkg_geometry_columns and SELECT * FROM gpkg_contents . I can see the content. Thank you – jlSta Feb 18 at 20:50
  • First i did: insert into 'gpkg_geometry_columns' values ( 'test2', 'geometry', 'MULTIPOLYGON', '31370', '0', '0' ) then CREATE TABLE test2 ( fid INTEGER NOT NULL PRIMARY KEY, geometry MULTIPOLYGON, name TEXT NOT NULL ); Finally, the file cannot open anymore – jlSta Feb 19 at 7:01
2

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

enter image description here

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.

  • one typo in insert into gpkg_geometry_columns values 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');, ' is missing in the last parameter: '31370' – jlSta Feb 20 at 12:41
  • 1
    Corrected. Actually there was one single quetation mark too much, the data type of srs_id is integer so the last parameter should be plain number 31370 but in SQLite databases that does not matter so much. – user30184 Feb 20 at 12:50

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.