How to write attribute-only tables in Geopackages with GeoPandas

Question

I searched around and found similar questions (Writing Non Spatial Table into Geopackage - Python), but that discussion is on a slightly different topic.

Is there a way to write attribute-only tables into a Geopackage using GeoPandas?

You need to specify a geometry field to create a GeoDataFrame. This is the error you get if there isn't one:

Import geopandas as gpd

sql = "select * from attronly"
con = ###your connection###
gdf = gpd.read_postgis(sql, con)

ValueError: Query missing geometry column 'geom'

But you can create an empty geom field and then drop it afterwards. If you do that, however, there's a different error as the function still expects there to be geometries to parse:

Import geopandas as gpd
    
sql = "select null as geom,* from attronly"
con = ###your connection###
gdf = gpd.read_postgis(sql, con)
gdf.drop(columns=['geom'],inplace=True)
gdf.to_file("file.gpkg", layer='attronly', driver="GPKG")

AttributeError: No geometry data set yet (expected in column 'geom'.)

I suspect the solution is either using Pandas to write into a Geopackage (though I can't find any documentation about that), or going back and using Fiona directly. I can also write in that nulled geometry field and then remove it afterwards from the Geopackage, but I'm hoping for some expert advice on doing this without bandaid solutions.

Answer 1

Did not found a way with Geopandas but below a recipe using Python OGR and a pure command line at the end using ogr2ogr (creation option ASPATIAL_VARIANT=GPKG_ATTRIBUTES used need GDAL >=2.2)

from osgeo import ogr

host = 'localhost'
dbname = 'dbname'
username = 'username'
password = 'password'
port = 5432

conn = ogr.Open(f"PG:host={host} dbname={dbname} user={username} password={password} port={port}")
sql = "SELECT * FROM table_no_geom;"
input_layer = conn.ExecuteSQL(sql)

outDriver = ogr.GetDriverByName("GPKG")
ds = outDriver.CreateDataSource('out.gpkg', )

lyr = ds.CreateLayer('layernameout', geom_type=ogr.wkbNone,
                     options=['ASPATIAL_VARIANT=GPKG_ATTRIBUTES'])

defn = input_layer.GetLayerDefn()
for i in range(defn.GetFieldCount()):
    lyr.CreateField(defn.GetFieldDefn(i))

for feature in input_layer:
    print(feature)
    lyr.CreateFeature(feature.Clone())

del lyr
del ds
del conn

Shorter version using gdal.VectorTranslate (equivalent of ogr2ogr within Python GDAL API)

from osgeo import gdal

host = 'localhost'
dbname = 'dbname'
username = 'username'
password = 'password'
port = 5432

ds = gdal.OpenEx(f"PG:host={host} dbname={dbname} user={username} password={password} port={port}", gdal.OF_VECTOR)

gdal.VectorTranslate(
    'out.gpkg',
    ds,
    options='-f "GPKG" -sql "SELECT * FROM table_no_geom" -lco ASPATIAL_VARIANT=GPKG_ATTRIBUTES -nln layernameout'
)

Command line version using pure ogr2ogr

ogr2ogr -f "GPKG" out.gpkg \
PG:"host=localhost user=username dbname=dbname password=password" \
-sql "SELECT * FROM table_no_geom" \
-lco ASPATIAL_VARIANT=GPKG_ATTRIBUTES \
-nln layernameout

Answer 2

Ran into this issue a bunch and finally figured it out.

You can write non-spatial tables to geopackages using GeoDataFrame.to_file() as long as you pass a schema that includes "geometry": "None" and the geometry column contains only None or empty shapely geometries.

i.e.

import geopandas as gpd
        
sql = "select null as geom,* from attronly"
con = ###your connection###
gdf = gpd.read_postgis(sql, con)

# Geometry column must be filled with None or empty shapely geometries
gdf["geometry"] = [None for i in gdf.index]

# Schema must be defined with geometry type specified as "None"
attronly_schema = {
    "geometry": "None",
    "attributes": {
        "att_1": "att_1_dtype",
        "att_2": "att_2_dtype", # etc.
    }
}

# Schema passed to GeoDataFrame.to_file()
gdf.to_file(
    "file.gpkg",
    layer='attronly',
    driver="GPKG",
    schema=attronly_schema,
)

Other observations:

• Having a CRS specified makes no difference
• Specifying "ASPATIAL_VARIANT=GPKG_ATTRIBUTES" seems to have no effect when passed to GeoDataFrame.to_file() or fiona.open()
• A geometry column must exist when using GeoDataFrame.to_file()

Answer 3

I was able to use @JF_NZ's answer, but only after changing the schema as follows:

attronly_schema = {
    "geometry": "None",
    "properties": {
        "att_1": "att_1_dtype",
        "att_2": "att_2_dtype", # etc.
    }
}

Fiona threw an error with "attributes"

If you have the attribute_only data in a GeoDataFrame already, this function can be used to build the schema:

def attronly_schema(df):
    """ see: https://gis.stackexchange.com/questions/396752 """
    def remap(dtype):
        correction = {"int64": "int", "float64": "float"}
        return correction[dtype] if dtype in correction else dtype

    return {
        "geometry": "None",
        "properties": {
            column: remap(str(dtype)) for column, dtype in zip(df.columns, df.dtypes) if column != 'geometry'},
    }

The correction remapping function is needed because Fiona won't accept those dtype strings.

Finally:

gdf.to_file(filepath, layer=layer, driver='GPKG', schema=attronly_schema(attr_only_layers[layer]))