Count number of features in a geopackage and write to a file

Question

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..

Answer 1

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)

Answer 2

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