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?

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.

Geographic Information Systems Asked on November 21, 2021

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


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

Related Questions

Assigning bands for cart classification

0  Asked on August 17, 2021

How to create a roadway using a topo values shapefile (survey points data) in Sketchup Pro?

0  Asked on August 17, 2021

Iterate Field Value and Select by Attribute in ArcMap does not select rows after first iteration

1  Asked on August 17, 2021 by miermoto

Create PDF Reports with a previous design for all records (Or selected ones) in QGIS

0  Asked on August 16, 2021

Documenting inability to Start Editing SQLite geodatabase layers in ArcMap?

3  Asked on August 16, 2021

QGIS update feature geometry from attribute fields

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

How to extract source headers from gdal WMS driver?

0  Asked on August 16, 2021 by maxmhuggins

Shifting extent of overview map, keeping scale, centering on extent indicator in ArcGIS Pro

1  Asked on August 16, 2021

Rasterio and OpenCV reads JPEG differently

2  Asked on August 16, 2021

0  Asked on August 16, 2021

Creating a mosaic out of rotated raster with gdal_translate

0  Asked on August 16, 2021 by karol-wojtulewicz

Concat fields which are not NULL

1  Asked on August 16, 2021

Making legend from layer in ArcGlobe

1  Asked on August 16, 2021

Is there an automated way to import a GPX file and use the filename as the layer name in QGIS?

0  Asked on August 16, 2021

ogr2ogr shapefile to PostgreSQL

1  Asked on August 16, 2021 by sep7696

Running a tool on subset of layer two points at a time PyQGIS

1  Asked on August 16, 2021

Adding Color Map to MrSID Imagery using QGIS

0  Asked on August 16, 2021

Calculate accuracy of georeferencing

2  Asked on August 16, 2021 by gvanhavre

Adding a base OBS file in RTKLIB post processing program

1  Asked on August 16, 2021 by da53