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. Treat them as three gates the frame has to clear, each with its own silent failure if you skip it:

Three alignment gates a GeoDataFrame must clear before an appendAn incoming GeoDataFrame passes through three checks before to_file is called in append mode. The CRS gate reprojects with to_crs, and skipping it writes geometry in the wrong projection. The column gate reindexes to the target's names and order, and skipping it can misalign values between columns. The dtype gate casts to the target's types, and skipping it turns an INTEGER column into REAL.skipped: wrong projectionskipped: values misalignedskipped: INTEGER to REALnew framerows to addCRS gateto_crs(target.crs)column gatereindex(columns=…)dtype gateastype(target[c])to_file(mode="a")appends the rows
None of the three gates raises when it is skipped — that is why the alignment has to be explicit.

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

The chain that produces this is short enough to miss in review. One upstream row with no sensor reading is enough to change the column’s storage class for every row that follows it:

How one missing value changes the stored column typeA three-step chain. The sensor_id column starts as int64. One incoming row carries a null, which pandas represents by promoting the whole column to float64. GeoPandas then maps float64 to a REAL column on write. The target layer's column is still declared INTEGER, so the layer schema and the values written now disagree.One NULL is enough to change the storage classsensor_id: int64as read from sourceone NULL arrivespandas has no int NAcolumn is float64written as REALlayer column is still declared INTEGERvalues and schema now disagree — nothing raises
Casting to the pandas nullable Int64 keeps the integer storage class while still allowing genuine NULLs.

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.

Scope of append mode compared with write mode in GeoPandasTwo views of the same three-layer container. Writing to the observations layer in append mode keeps the basemap and roads layers untouched and adds rows to observations. Writing in write mode also keeps the basemap and roads layers untouched, but replaces the contents of observations entirely. Both modes are scoped to the named layer, not to the file.GeoPandas targets the named layer, not the filemode="a" on observationsbasemap — untouchedobservations — rows addedroads — untouchedmode="w" on observationsbasemap — untouchedobservations — replacedroads — untouched
This is the opposite of raw Fiona semantics, where "w" on an existing path truncates the whole container.

That asymmetry catches people moving between the two APIs. GeoPandas resolves mode against the layer because it always passes a layer name through to the driver; Fiona’s "w" is a file-level operation unless you add the OVERWRITE layer option. If a script mixes both libraries, keep the GeoPandas call for layer-scoped work and reach for Fiona only when you genuinely intend to rebuild the container.

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.

Frequently Asked Questions

Does `mode="a"` create the layer if it does not exist?

Yes — the append lands in a new layer rather than failing, which is convenient for a first run and dangerous for a typo. A misspelled layer name produces a second, near-empty layer beside the real one and no error at all. Read fiona.listlayers or gpd.list_layers first and fail loudly when the name is absent, so an append is always a deliberate act against a known layer.

Which engine should I use for appends, Fiona or pyogrio?

pyogrio for anything bulk. It moves columns as arrays rather than building a Python dict per feature, which on a hundred-thousand-row frame is commonly an order of magnitude faster. Fiona remains useful when you need per-feature control — filtering, transforming or logging as you go — because its record-at-a-time interface is what makes that natural. Choose explicitly with engine=; relying on the default means the behaviour changes with the GeoPandas version.

How do I stop a re-run from duplicating rows?

The container will not stop you: GeoPackage places no uniqueness constraint on appended rows, so running the same job twice writes every row twice. Either declare a UNIQUE constraint on the natural key and catch the integrity error, or read the existing keys and filter the incoming frame before writing. For pipelines that append continuously, a change-tracked sync with an explicit high-water mark is more robust than either.

Can I append a frame whose geometry column has a different name?

Not directly — the driver matches the geometry by the layer’s registered column name, so a frame whose geometry is called geometry will not line up with a layer whose column is geom. Read the target’s geometry.name and rename before the append. This is one of the reasons the alignment step reads a single row from the target rather than assuming defaults: geometry column naming is a per-layer decision, not a format-wide one.