Extracting catalog consisting of name of layerdata/table of different geopackages

Question

Currently we have an organizing and tracking of files problem.
Right now we have our geodata organized in different geopackages (.GPKG files) for specific areas.
The problem is that the amount of  geopackages is growing and it becomes harder and harder to track where each of the data are. Sure, we organized it logically but at the same time it still hard to keep track.
To keep better track of the data, we want to keep an inventory of the contents of the GPKG file (data tables, layer names) including its metadata.

Is there any way this can be done in QGIS itself or do we need to develop a separate script for it?
Contents:

I currently have found a way to extract the information of geopackage so far using SQL, or more specifically SQLite. So far I have created some querry's to extract the tables that are "common" in a geopackage. As for the shematics below, this is the one for Vector data:

And this is the one for raster data:

However, the problem I still have is the extraction of the sample_feature_table and the sameple_title_pyramid because depending on the geopackage the name of the feature table is different.
I know that the name of the feature table's are in the table name column, but I do not see a way to make this below assumption of code workable.
Select * from sample_feature_table;
Select * from sample_pyramid_title;
Where sample_feature_table = gpgk_contents.table_name?
Where sample_tile_pyramid= gpgk_tile_matrix.table_name column?

The sample_feature_table and its raster counterpart are "placeholder table names", the actual name of all the tables are in the table name column.

ThunderSpark · Answer

If your geo-package only consist of raster data, this will not work because your geo-package will only be recognized as a "Raster". To circumvent this you have to add a "Dummy" table to your geo-package. This way you the geo-package is recognized as a Vector. This dummy table you can filter out.
To "extract" the most important tables that are relevant to this case I have created A QGIS model, with two input parameters:
The geopackage location (A Vector layer input) : @Geopackage
Geopackage name input (string layer input):  @Vulhierdenaamvanuwgeopackagein

The Geopackage name input (string layer input) is meant to rename the tables later and adding the geopackage naam itself to the tables to create an index for the future.
There are three GDAL SQL query's that are relevant to this to:
select * from gpkg_contents where table_name IS NOT 'Dummy';

select * from gpkg_geometry_columns where table_name IS NOT 'Dummy';

select * from gpkg_ogr_contents where table_name IS NOT 'Dummy';

As you can see the dummy table is already being filtered out.
Then it goes to refactor mode where I add the geopackage name to all three tables using this formula
replace(replace( @Geopackage,regexp_substr(@Geopackage ,'.*/'),''),'.gpkg','')

Which strips the filepath and .gpkg extension as a string.
Next up renaming the files goes like this : I use a load layer in project algorithm for loading the layers and then putting this as the name as "calculated value".
@Vulhierdenaamvanuwgeopackagein  || '_contents'
@Vulhierdenaamvanuwgeopackagein  || '_geometry_columns'
@Vulhierdenaamvanuwgeopackagein  || '_ogr_contents'

If you want to extract as batch process (aka extract many geopackages at the same time): then you must use this formula, after you have loaded in all geopackages you want to extract.
replace(replace( @Geopackage,regexp_substr(@Geopackage ,'.*/'),''),'.gpkg','')

nr_aus · Answer

First of all - great initiative and great thinking to use the internal of the geopackage for this info.
Secondly - not sure you use to browse your geopackage internals, but DB Browser for SQLite is pretty good and may help.
Thirdly - Im not 100% sure I fully understand the problem, however - the gpkg_contents table contains a field called 'data_type' which im pretty sure identifies it as raster or not (vector is listed as 'features').
get this first
select data_type, table_name from gpkg_contents
This returns a vector/array of table_names and data_types, which should then allow you to differentiate between tables that has the same name, but different data type.
Does that help?

Extracting catalog consisting of name of layerdata/table of different geopackages

2 Answers

Add your own answers!

Ask a Question