[gdal-dev] GPKG: Drop table but how to drop the rest?
even.rouault at spatialys.com
Thu Sep 29 04:13:17 PDT 2016
> I wonder if it could be possible to do something like "DROP CASCADING" in
> GeoPackage and delete the spatial index by the same.
I will work in a short term on this (as well as renaming columns)
The "usual" syntax "ogrinfo -sql 'DELLAYER:bar' foo.gpkg" will be available,
and will be done through the proper OGRLayer::DeleteLayer() implementation.
> It will for sure be
> unusable once the table is dropped. I feel that SQLite does not support
> that because cascading drop seems to require that parent and child tables
> have foreign key relation, but RTree index which is created through the
> Virtual RTRee does not offer any way for defining the foreign key.
> I know I can delete the index with another SQL request with ogrinfo
> ogrinfo -sql "drop table bar" foo.gpkg
> ogrinfo -sql "drop table rtree_bar_geom" foo.gpkg
> Metadata tables are not cleaned automatically and two more commands would
> be needed:
> ogrinfo -sql "delete from gpkg_contents where table_name='bar' and
> identifier='bar'" foo.gpkg ogrinfo -sql "delete from gpkg_geometry_columns
> where table_name='bar' and column_name='geom'" foo.gpkg
> However, it is impossible to run the latter one because GPKG database is at
> this moment in a bad shape
You can turn off integrity checking with OGR_GPKG_INTEGRITY_CHECK = NO as a
config option (documented only in code, since there's little reason to turn
that off usually)
> ogrinfo -sql "delete from gpkg_geometry_columns where table_name=bar' and
> column_name='geom'" foo.gpkg ERROR 1: bad result for PRAGMA
> foreign_key_check, got 1 rows, expected 0 ERROR 1: pragma
> foreign_key_check on 'foo.gpkg' failed
> ERROR 1: bad result for PRAGMA foreign_key_check, got 1 rows, expected 0
> ERROR 1: pragma foreign_key_check on 'foo.gpkg' failed
> Unable to open datasource `foo.gpkg' with the following drivers.
> I can delete that row from gpkg_geometry_columns with spatialite_gui.
> So what is the right procedure and order for dropping a table from
> GeoPackage with ogrinfo?
Probably removing any reference to the table in gpkg_metadata*, gpkg_contents
and gpkg_geometry_columns tables, then drop the rtree, then drop the main
Spatialys - Geospatial professional services
More information about the gdal-dev