5

I'm tasked to create a simple catalogue of 2000+ geopackages, and hope there is a way to do that with QGIS.

The idea is to batch run over all these geopackages and write the following to a CSV file;

file name, number of features, geometry type, srid, bbox

for eg, a CSV entry like this;

countour.gpkg, 1876, multipolyline, 4326, bbox .. ..

Did anyone had such a requirement in the past? It will be great if this can be done using QGIS processing toolbox as a batch process.

EDIT There are 2000+ gpkg with one table in each file. Add to that, each geopackage has strictly one table with one geometry type only..

5
  • 1
    @BERA, thanks. there are 2000+ gpkg with one table in each file. Add to that, each geopackage has strictly one table with one geometry type only. If this helps. – Garry Jan 19 at 10:41
  • 2
    ogrinfo and a for loop will do this – Ian Turton Jan 19 at 10:45
  • Feels like a small SQL exercise. Table name, geometry type name and srs_id are in the gpkg_geometry_columns table. Min/max x and y (and srs_id again) are in the gpkg_contents. If GeoPackages have been created with GDAL the feature count is in a non-standard ogr_gpkg_contents table. Otherwise run "select count() from table_name". Nothing of that requires spatial capabilities so any SQLite client will do. Only if the geometry type in the gpkg_geometry_columns has been written as generic "geometry" you must run spatial SQL and find the real geometry type. Doable with ogrinfo and -sql. – user30184 Jan 19 at 12:03
  • thanks, Ian Turton. Sure that's useful for many of my work. – Garry Jan 20 at 1:46
  • thanks user30184, looks like it is a long way for me. Not so conversant with SQL yet. – Garry Jan 20 at 1:49
10

I had done something similar with python. The script loops recursive through the directories starting from the startdir and then opens each geopackage and reads the gpkg_contents table - containing geometry type, extent, CRS, date and so on, the second query selects the number of features. The script works on the first featuretable in a geopackage only, may be you have to adjust this to your needs:

import sqlite3
import glob 
import csv
root_dir = 'c:/startdir/'
result = root_dir + 'gpkg_result.csv'
with open(result, 'w', newline='') as myfile:
    wr = csv.writer(myfile, quoting=csv.QUOTE_ALL)
    for filename in glob.iglob(root_dir + '**/*.gpkg', recursive=True):
        con = sqlite3.connect(filename)
        with con:
            cursor = con.cursor()
            cursor.execute("SELECT * FROM gpkg_contents;")
            l1 = list(cursor.fetchall()[0])
            cursor.execute("SELECT Count(*) FROM {};".format(l1[0]))
            l2 = list(cursor.fetchall()[0])
            cursor.execute("SELECT geometry_type_name FROM gpkg_geometry_columns")
            l3 = list(cursor.fetchall()[0])
            wr.writerow(l1+l2+l3)
4
  • thank you very much. The scrips work like a charm and precisely what I wanted it. hugs. – Garry Jan 19 at 22:46
  • HI eurojam, not able to figure out how I can change the code so it writes the "geometry type" in the csv. Looking for a way since that's one of the headaches; to identify if the gpkg is a point, line or area. – Garry Jan 20 at 1:44
  • Hi Garry, you are right, I missed that. I added one more sql statement with the geometry_type_name. Now it should work like expected. – eurojam Jan 20 at 6:22
  • Hi eurojam, thank you very much. Yes, it works great now. :) – Garry Jan 21 at 10:51
3

A pyqgis script

It handles other formats (shp, tab, gpkg, geojson...) all formats that ogr can handle

But it does not handle sublayers

import os
import csv
from qgis.core import QgsVectorLayer

FOLDER = 'c:/folder/to/scan'
RESULT = 'c:/folder/metadata.csv'

def scan_directory(directory, extension=('.tab', '.shp', '.gpkg')):
    """return the list of files that matche the extension

    Args:
        directory (str): the folder to scan
        extension (tuple, optional): the extension. Defaults to ('.tab', '.shp', '.gpkg').
    """
    extension = tuple(map(str.lower, extension))
    for root, dirs, files in os.walk(directory, topdown=False):
        for filename in files:
            if filename.lower().endswith(extension):
                yield(root, filename, os.path.join(root, filename))

def get_metadata(layer):
    """Return metadata

    Args:
        layer ([ogr vectorlayer]): [Not sublayer]

    Returns:
        metadata: (name, comment, encoding....)
    """
    name = layer.name()
    comment = layer.dataComment()
    encoding = layer.dataProvider().encoding()
    datasource = layer.publicSource()
    feature_count = layer.featureCount()
    geom_type = geom_wkbtype = crs  = extent = None
    if layer.isSpatial():
        geom_type = layer.geometryType()
        if geom_type <0 or geom_type > QgsWkbTypes.NullGeometry:
            print(f'{layer} invalid geometry type')
        else:
            geom_wkbtype = QgsWkbTypes.displayString(layer.wkbType())
            geom_type = QgsWkbTypes.geometryDisplayString(geom_type)
        if layer.crs().isValid():
            crs = layer.crs().userFriendlyIdentifier(
                QgsCoordinateReferenceSystem.FullString )
        extent=layer.extent().toString()
    return (name, comment, encoding, geom_type, 
        geom_wkbtype, crs, extent, feature_count, datasource)
    

with open(RESULT, 'w', newline='') as myfile:
    wr = csv.writer(myfile, quoting=csv.QUOTE_ALL)
    wr.writerow(['name', 'comment', 'encoding',
        'geom_type', 'geom_wkbtype', 'crs', 'extent', 'feature_count','datasource'])
    
    for root, filename, path in scan_directory(FOLDER):
        layer = QgsVectorLayer(path, filename,'ogr')
        if not layer.isValid():
            print(f'{layer.name()} is not valid')
            continue
        wr.writerow(get_metadata(layer))

Code available : https://gist.github.com/42a6b3ab8e1069e3e0523ca90c361aaa

1
  • Hi lejedi76. Thank you very much. This variant works great as well. I have two scripts to handle the work. I noticed that your script can be run on shp files too. So, definitely will be useful. hugs and cheers. – Garry Jan 21 at 10:55

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.