[Feature request] Support non-spatial tables in Geopackages according to the specification

[ffrosch]

Describe the feature

Add non-spatial tables to gpkg_contents according to the Geopackage specification (2.4 Attributes and 1.1.2.1.2. Table Data Values).

Differences compared to spatial tables:

• data_type="attributes"
• srs_id=0

Example Use

This feature request is important for end users handling Geopackage data in GUI applications.

Here is a quick sketch for the additional code needed.

geopackage.after_create adds an entry to gpkg_contents for every non-spatial table:

def after_create(table, bind, **kw):

    # ... previous code

    is_spatial = any(
        _check_spatial_type(col.type, Geometry, dialect) for col in table.columns
    )

    if not is_spatial:
        bind.execute(
            text(
                """INSERT INTO gpkg_contents
                VALUES (
                    :table_name,
                    'attributes',
                    :table_name,
                    "",
                    strftime('%Y-%m-%dT%H:%M:%fZ', CURRENT_TIMESTAMP),
                    NULL,
                    NULL,
                    NULL,
                    NULL,
                    0
                );"""
            ).bindparams(
                table_name=table.name,
            )
        )

    # ... remaining code

geopackage.before_drop removes the entry from gpkg_contents for every non-spatial table:

def after_create(table, bind, **kw):

    # ... previous code

    if len(gis_cols) == 0:
        bind.execute(
            text(
                """DELETE FROM gpkg_contents
        WHERE LOWER(table_name) = LOWER(:table_name);"""
            ).bindparams(table_name=table.name)
        )

    # ... remaining code

Use cases

All tables in a Geopackage should be listed in the table gpkg_contents to make sure they are discoverable by all applications.

QGIS does not properly recognize non-spatial tables that are not listed in gpkg_contents. By adding them to gpkg_contents they work as expected in QGIS.

[ffrosch]

I'd be happy to submit a pull request to implement this feature :-)

If there is anything else I would have to pay attention to or add, please let me know.

[adrien-berchet]

Hi @ffrosch Thank for your interest in GeoAlchemy2! This feature could be interesting indeed. I'm just wondering if another lib can not already do this? Because GeoAlchemy2 is supposed to just be a 'simple' wrapper for spatial types, so I would prefer to stay focused on these specific types as much as possible. Though in this case I think the changes for this feature should be quite small in the code so we could add it. Anyway, if you have some time to create a PR for this I would be happy to review it :)