3

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.

Screenshot

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

Contents:

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.

1

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','')
2
  • glad you got it sorted, was it always your original plan to rename geopackage files as part of this cataloging? Will this result in a consolidation of the number of geopackages or is that irrelevant once the catalogue is created?
    – nr_aus
    Aug 17 '20 at 3:49
  • Not really that was not originally my plan, I wanted to create one catalogues at once. I have created another script to merge them all back together into one thanks to the vector layer merge algorithm with some adaptations. Like for example looking at a specific folder etc. Aug 17 '20 at 8:49
-1

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?

4
  • 1
    Thx for the tip but it sadly does not solve the problem. The thing is I need to build a "standard" selection querry for the individual feature/raster table. So that I do not need to change the name "from" table every time. I will post an edit later for explanation. Jul 27 '20 at 13:26
  • ahhh I understand a bit more now, this will be on-going and needs to be abstracted from the table names. …….Turning the solution completely on its head, have you investigated a data catalogue solution to solve you problem instead? Leave your packages as they are and have users discover data using a catalogue and search portal solution instead? It helps answer questions of "Can you show me all surveys that have been done in this area here?"
    – nr_aus
    Jul 31 '20 at 1:52
  • I have already found a sollution to extract gpkg_contents and etc by creating a QGIS model with GDAL Execute SQl and refactor algorithms. ignore my previous comment. I will post my sollution later. And ask this new problem in a different question. Jul 31 '20 at 10:25
  • sounds good, look forward to hearing the solution.
    – nr_aus
    Aug 3 '20 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.