SpatiaLite & GeoPackage Automation with Python

How to Append a GeoDataFrame to a GeoPackage Layer

Call gdf.tofile("data.gpkg", layer="mylayer", driver="GPKG", mode="a") — but first make the GeoDataFrame's CRS, column names, and column dtypes match the…

Call gdf.to_file("data.gpkg", layer="my_layer", driver="GPKG", mode="a") — but first make the GeoDataFrame’s CRS, column names, and column dtypes match the existing layer exactly, because to_file appends rows without validating that the incoming schema lines up.

This page is part of the GeoPandas & GeoPackage Integration guide. Where converting Shapefiles to GeoPackage with GeoPandas writes a whole new layer from scratch, this walkthrough targets the incremental case: adding rows to a layer that already exists without rewriting the file.

Why This Matters

Appending is the common shape of a real pipeline. A daily job pulls the latest field observations and needs to add them to a observations layer that already holds a month of history; a sync process merges edits from several devices into one master GeoPackage. Rewriting the entire layer on every run is wasteful and, on large tables, risks leaving a half-written file if the process is interrupted.

GeoPandas makes the append itself a one-liner with mode="a", which was added to to_file in GeoPandas 0.8 and routes through the Fiona or pyogrio engine. The risk is not the call — it is the data. GeoPandas does not reproject on append, does not reconcile column order, and maps pandas dtypes to GeoPackage column types on the fly. A mismatched coordinate reference system writes geometry in the wrong projection; a stray NaN in an integer column silently promotes it to a float. This page is about getting those three things — CRS, columns, dtypes — right before you append.

Prerequisites

  • Python 3.9+
  • geopandas 0.12+ with either the fiona (1.9+) or pyogrio engine
  • GDAL/OGR 3.4+ with the GPKG driver
  • An existing GeoPackage with at least one feature layer
  • Familiarity with GeoDataFrame CRS handling (gdf.crs, to_crs)

Primary Method

python
# GeoPandas 0.12+ / GDAL 3.4+ — safely append rows to an existing GeoPackage layer
import geopandas as gpd


def append_to_layer(gdf: gpd.GeoDataFrame, gpkg_path: str, layer: str) -> int:
    """
    Append gdf to an existing GeoPackage layer after aligning CRS,
    column order, and dtypes to the target layer. Returns rows written.
    """
    # 1. Read the target layer's schema (empty read is cheap: rows=0 via a slice)
    target = gpd.read_file(gpkg_path, layer=layer, rows=1)

    # 2. Reproject to the layer's CRS — appending in the wrong CRS is silent corruption
    if gdf.crs != target.crs:
        gdf = gdf.to_crs(target.crs)

    # 3. Align columns: same names, same order, drop extras, fill missing
    geom_name = target.geometry.name
    cols = [c for c in target.columns if c != geom_name]
    gdf = gdf.reindex(columns=cols + [geom_name])

    # 4. Match dtypes so an int column does not drift to float on append
    for col in cols:
        if col in gdf and gdf[col].dtype != target[col].dtype:
            gdf[col] = gdf[col].astype(target[col].dtype)

    gdf.to_file(gpkg_path, layer=layer, driver="GPKG", mode="a")
    return len(gdf)

The mode="a" argument is what turns to_file from “create or overwrite” into “append”. Everything before it exists to guarantee the incoming frame is shaped like the layer it is joining.

Step-by-Step Walkthrough

1. Inspect the target layer

Read one row to learn the layer’s CRS, geometry column name, and dtypes without loading the whole table:

python
import geopandas as gpd

target = gpd.read_file("field.gpkg", layer="observations", rows=1)
print(target.crs)                 # EPSG:4326
print(target.geometry.name)       # 'geometry' or 'geom'
print(target.dtypes)

2. Reproject the incoming frame

to_file writes coordinates verbatim, so a frame in EPSG:3857 appended to an EPSG:4326 layer produces geometry in the wrong place. Reproject explicitly:

python
if new_gdf.crs != target.crs:
    new_gdf = new_gdf.to_crs(target.crs)

3. Align column names and order

The append matches columns by position under some engine/driver combinations, so reorder to the target and drop columns the layer does not have:

python
geom_name = target.geometry.name
cols = [c for c in target.columns if c != geom_name]
new_gdf = new_gdf.reindex(columns=cols + [geom_name])

4. Reconcile dtypes

An integer column containing a missing value becomes float64 in pandas, which GeoPackage stores as REAL — a schema drift from the original INTEGER. Cast back before appending, using a nullable integer type if you truly have missing values:

python
new_gdf["sensor_id"] = new_gdf["sensor_id"].astype("int64")
# or, if NULLs are legitimate:
new_gdf["sensor_id"] = new_gdf["sensor_id"].astype("Int64")   # pandas nullable int

5. Append

python
new_gdf.to_file("field.gpkg", layer="observations", driver="GPKG", mode="a")

6. Choose your engine deliberately

pyogrio is markedly faster than Fiona for bulk appends because it moves data in bulk rather than feature-by-feature. Select it explicitly when appending large frames:

python
new_gdf.to_file(
    "field.gpkg", layer="observations", driver="GPKG",
    mode="a", engine="pyogrio",
)

Verification

Confirm the row count grew by exactly the number you appended and that the CRS did not change:

python
import geopandas as gpd

after = gpd.read_file("field.gpkg", layer="observations")
print(len(after), after.crs)

A direct SQL count avoids loading geometry and is fast on large layers:

sql
-- GeoPackage: row count and registered SRS for the layer
SELECT
    (SELECT count(*) FROM observations) AS n_rows,
    (SELECT srs_id FROM gpkg_contents WHERE table_name = 'observations') AS srs;

Alternative Approaches or Edge Cases

Replace instead of append. To swap a layer’s contents wholesale, use mode="w" — but note that on a GeoPackage this replaces only the named layer, leaving other layers intact, unlike a raw file truncation. Use mode="w" when the run is authoritative and mode="a" when it is incremental.

Deduplicating on append. GeoPackage does not enforce uniqueness on appended rows, so a re-run can create duplicates. Either add a UNIQUE constraint and catch the integrity error, or read existing keys and filter the incoming frame with new_gdf = new_gdf[~new_gdf["obs_id"].isin(existing_ids)] before writing. For change-tracked syncs, see tracking row changes for incremental GeoPackage sync.

Troubleshooting

Appended features land in the wrong location on the map

Cause: The incoming GeoDataFrame was in a different CRS than the target layer and to_file wrote the coordinates without reprojecting. Fix: Call gdf = gdf.to_crs(target.crs) before appending, and keep per-layer CRS consistent as covered in managing spatial reference systems in SQLite.

sqlite3.OperationalError: database is locked during the write

Cause: Another connection (QGIS, a second script, an open reader) holds a lock on the .gpkg. Fix: Close other readers, or wrap the write with a busy-timeout and retry as described in how to retry locked database writes in SQLite.

Integer column becomes REAL after appending

Cause: A missing value promoted the pandas column to float64, and the driver mapped that to REAL, diverging from the layer’s INTEGER type. Fix: Cast with astype("int64"), or astype("Int64") if NULLs are legitimate, before calling to_file.