Multiple entries of same layer in GeoPackage

Question

I have a GeoPackage of many layers which I have added and deleted over time. I think because of that, I get several entries of the same layer. For example when I use the statistics tool, I get a list of all my layers in my GeoPackage, but most layers appear up to 5 times. It makes it difficult to find the right layer.

I guess each version (new and old) gets stored within the GeoPackage whenever I add a layer with the same name even though the layer exist only one time.

How can I clean up my GeoPackage?

Answer 1

This is a manual fix, no guarantees that it will work without side effects. Make sure you backup your file beforehand. Make sure the file is not opened by another program (like QGIS).

GeoPackage is a SQLite based format, a database in one file. You can use a SQLite client to connect to this file and execute SQL statements on it to alter its contents. For Linux you can use the sqlite3 tool, for a cross-platform GUI I like DB Browser for SQLite.

The list of available layers is stored in a table called gpkg_contents. In your case I assume that there are multiple, identical lines in it. Delete all duplicates and just leave one entry. If they are not identical, remove all but the latest (when ordered by id).

How you do that in the GUI should hopefully be self-explanatory. How you alternatively do it via SQL queries is another question.

Answer 2

I would suggest you to use GDAL command line to create or manipulate OGC GeoPackage instead of UI. QGIS is good to visualise vector data or raster data stored in geopackage.

Answer 3

In QGIS Browser, search the GPKG, right click on it and use VACUUM.