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

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?


enter image description here

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:

enter image description here

And this is the one for raster data:
enter image description here

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.

Geographic Information Systems Asked on November 21, 2021

2 Answers

2 Answers

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

enter image description here

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','')

Answered by ThunderSpark on November 21, 2021

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?

Answered by nr_aus on November 21, 2021

Add your own answers!

Related Questions

QGIS update feature geometry from attribute fields

2  Asked on August 16, 2021 by benny-the-spaceman


Creating a mosaic out of rotated raster with gdal_translate

0  Asked on August 16, 2021 by karol-wojtulewicz


ogr2ogr shapefile to PostgreSQL

1  Asked on August 16, 2021 by sep7696


Calculate accuracy of georeferencing

2  Asked on August 16, 2021 by gvanhavre


Ask a Question

Get help from others!

© 2021 All rights reserved.