2

I want to create a vectorial GPKG with spatialite.

With the help of the documenation (here: http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html#p16gpkg ), I've created a sql script that look like this:

SELECT EnableGpkgMode();
SELECT gpkgCreateBaseTables();

CREATE TABLE grid ('fid' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);
SELECT gpkgAddGeometryColumn('grid', 'geom', 'GEOMETRY', 0, 0, 4326);

Here is my output:

$ rm -f test.gpkg && spatialite test.gpkg < test.sql
SpatiaLite version ..: 4.3.0a   Supported Extensions:
    - 'VirtualShape'    [direct Shapefile access]
    - 'VirtualDbf'      [direct DBF access]
    - 'VirtualXL'       [direct XLS access]
    - 'VirtualText'     [direct CSV/TXT access]
    - 'VirtualNetwork'  [Dijkstra shortest path]
    - 'RTree'       [Spatial Index - R*Tree]
    - 'MbrCache'        [Spatial Index - MBR cache]
    - 'VirtualSpatialIndex' [R*Tree metahandler]
    - 'VirtualElementary'   [ElemGeoms metahandler]
    - 'VirtualXPath'    [XML Path Language - XPath]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'VirtualGPKG' [OGC GeoPackage interoperability]
    - 'VirtualBBox'     [BoundingBox tables]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 5.2.0, September 15th, 2018
GEOS version ........: 3.7.1-CAPI-1.11.1 27a5e771
TARGET CPU ..........: x86_64-linux-gnu
the SPATIAL_REF_SYS table already contains some row(s)


Error: near line 5: FOREIGN KEY constraint failed

What did I do wrong ?

2 Answers 2

1

The database seems to require that there is a corresponding line in the gpkg_contents table before you can add the gpkg geometry field. Do something like this before SELECT gpkgAddGeometryColumn

insert into gpkg_contents values
('grid','features','grid','description','2018-05-03',0,0,360,90,4326);

I am not sure if the syntax is totally correct. I also guess that SELECT gpkgAddGeometryTriggers('grid','geom') would be good to execute in some place and triggers should perhaps update the gpkg_contents table automatically. However, it did not seem to happen but I did not spend much time for testing.

This thread in spatialite-users forum may give some more information https://groups.google.com/forum/#!topic/spatialite-users/D0MV4Z3k0-E.

1

1: Maybe it's constraint to srs_ref (4326) after SELECT gpkgCreateBaseTables() trow select gpkgInsertEpsgSRID(4326) allows to populate the gpkg_spatial_ref_sys table.

2: In your create table create geom column with a geometry type, like: create table grid ("fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "geom" MULTIPOLYGON)

3: Add table to gpkg_contents insert into gpkg_contents values ('grid','features','grid','description','2018-05-03',0,0,360,90,4326);

4: Refer to that geom column and type in select gpkgAddGeometryColumn("grid", "geom" , "MULTIPOLYGON", 0 , 0, 4326 )

param function:

gpkgAddGeometryColumn( table_name String, geometry_column_name String , geometry_type String , with_z Integer , with_m Integer , srs_id Integer ) : void

geometry_type is a normal WKT name:

"GEOMETRY","POINT","LINESTRING","POLYGON","MULTIPOINT","MULTILINESTRING","MULTIPOLYGON","GEOMCOLLECTION"

ref here: http://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html?

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.