3

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.

1
  • I use pandas a lot for similar requirements - due to the overlap between data structures you can use just a few lines of code. But obvs that is limited to output formats supported by pandas e.g. json, csv, excel.
    – Mike Honey
    May 18 at 5:36
3

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
1
  • Thank you @ThomasG77, that worked. I just had to add a missing parameter for the host ip to ogr.Open. I'm surprised that GeoPandas doesn't handle this kind of stuff natively.
    – Encomium
    May 17 at 14:08

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.