How to Load a Folder of Shapefiles into PostGIS with Python

Problem statement

You have a folder of shapefiles from a data supplier and a PostGIS database. The loop looks trivial:

for path in Path("data").glob("*.shp"):
    gpd.read_file(path).to_postgis(path.stem, engine, if_exists="replace")

It runs, and then you discover what it actually produced:

• Some tables are in EPSG:4326 and some in EPSG:27700, because the source files were mixed.
• Every table has two identical spatial indexes — GeoPandas built one, and the script built another.
• One file was E06 unitary authorities.shp, giving a table called E06 unitary authorities that needs quoting forever after.
• The MultiPolygon/Polygon split means one table rejects half its own data on the next append.
• The job died at file 212 and rerunning it starts from zero.
• Column names were truncated to ten characters by the shapefile format long before Python saw them.

None of these are hard problems individually. The point is that a folder load has to solve all of them at once, and the one-line version solves none.

Quick answer

from pathlib import Path
import geopandas as gpd
from sqlalchemy import create_engine, text
import re

engine = create_engine("postgresql+psycopg://user@localhost/gis")
SRID = 27700

def safe_name(path):
    name = re.sub(r"[^a-z0-9_]+", "_", path.stem.lower()).strip("_")
    return f"t_{name}" if not name[0].isalpha() else name

for path in sorted(Path("data").glob("*.shp")):
    table = safe_name(path)
    gdf = gpd.read_file(path)
    if gdf.crs is None:
        print(f"  ✗ {path.name}: no CRS, skipped"); continue
    gdf = gdf.to_crs(SRID)
    gdf.columns = [c.lower() for c in gdf.columns]
    gdf.to_postgis(table, engine, if_exists="replace", index=False, chunksize=10_000)
    with engine.begin() as con:                    # to_postgis has built idx_<table>_geometry
        con.execute(text(f'ANALYZE "{table}"'))
    print(f"  ✓ {table:<28} {len(gdf):>8,} rows")

  ✓ e06_unitary_authorities            63 rows
  ✓ e07_districts                     164 rows
  ✓ e08_metropolitan_districts         36 rows
  ✓ e09_london_boroughs                33 rows
  ✓ n09_ni_districts                   11 rows
  ✓ naptan_west_yorkshire          16,677 rows
  ✓ s12_scottish_council_areas         32 rows
  ✓ w06_welsh_principal_areas          22 rows
  ✗ Ward Areas (final).shp: no CRS, skipped
  ✓ ne_110m_admin_0_countries         177 rows

Audit, sanitise names, unify CRS and types

1. Audit the folder before writing anything

from pathlib import Path
import geopandas as gpd, pandas as pd, pyogrio

def audit(folder, pattern="*.shp"):
    rows = []
    for path in sorted(Path(folder).glob(pattern)):
        try:
            info = pyogrio.read_info(path)
            rows.append({
                "file": path.name,
                "rows": info["features"],
                "crs": info["crs"],
                "geom_type": info["geometry_type"],
                "fields": len(info["fields"]),
                "mb": round(sum(p.stat().st_size for p in path.parent.glob(path.stem + ".*")) / 1e6, 1),
            })
        except Exception as exc:
            rows.append({"file": path.name, "error": str(exc)[:60]})
    df = pd.DataFrame(rows)
    print(df.groupby("crs", dropna=False)["rows"].agg(["count", "sum"]))
    print(df["geom_type"].value_counts().to_dict())
    return df

df = audit("data/")

            count    sum
crs                     
EPSG:27700      7    361
EPSG:4326       2  16854
NaN             1     33
{'Polygon': 9, 'Point': 1}

pyogrio.read_info reads the header only, so the audit stays fast however large the files are. Three findings are already visible: two files need reprojecting, one has no CRS at all (the NaN row), and every polygon file reports its type as plain Polygon. That last one is the shapefile format, not the data — a shapefile header cannot say whether a layer mixes single and multi-part polygons, and all seven council files here do. The split only appears once the rows are read.

2. Sanitise table names

PostgreSQL folds unquoted identifiers to lower case. A table created as "Cheshire East" can only ever be referenced as "Cheshire East", quotes included, by everyone, forever.

import re

def safe_name(stem, prefix="", max_len=63):
    name = re.sub(r"[^a-z0-9_]+", "_", stem.lower()).strip("_")
    name = re.sub(r"_+", "_", name)
    if not name or not name[0].isalpha():
        name = "t_" + name
    return (prefix + name)[:max_len]        # 63 bytes is Postgres's limit

for stem in ["Cheshire East", "2026-boundaries", "Ward Areas (final)"]:
    print(f"{stem!r:<24} → {safe_name(stem)}")

'Cheshire East'          → cheshire_east
'2026-boundaries'        → t_2026_boundaries
'Ward Areas (final)'     → ward_areas_final

Watch for collisions: Ward-Areas and Ward Areas both sanitise to ward_areas. Check before loading rather than discovering it when the second file silently replaces the first.

from collections import Counter
names = [safe_name(p.stem) for p in Path("data").glob("*.shp")]
clashes = {n: c for n, c in Counter(names).items() if c > 1}
if clashes:
    raise SystemExit(f"table name collisions: {clashes}")

3. Unify the CRS and the geometry type

TARGET_SRID = 27700

def prepare(gdf, srid=TARGET_SRID, promote_multi=True):
    if gdf.crs is None:
        raise ValueError("no CRS — cannot load safely")
    if gdf.crs.to_epsg() != srid:
        gdf = gdf.to_crs(srid)

    gdf = gdf[~gdf.geometry.isna() & ~gdf.geometry.is_empty].copy()

    if promote_multi:
        from shapely.geometry import MultiPolygon, MultiLineString
        types = set(gdf.geom_type)
        if types <= {"Polygon", "MultiPolygon"} and len(types) > 1:
            gdf["geometry"] = [
                g if g.geom_type == "MultiPolygon" else MultiPolygon([g])
                for g in gdf.geometry
            ]
        elif types <= {"LineString", "MultiLineString"} and len(types) > 1:
            gdf["geometry"] = [
                g if g.geom_type == "MultiLineString" else MultiLineString([g])
                for g in gdf.geometry
            ]
    gdf.columns = [c.lower() for c in gdf.columns]
    return gdf

The mixed-type promotion matters because to_postgis types the geometry column from the frame it is given. A frame of only Polygons gets a geometry(Polygon, 27700) column; a frame mixing both gets an untyped geometry column. Both go wrong later:

from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg://user@localhost/gis")
boroughs = gpd.read_file("tiles/E09 London boroughs.shp")
single = boroughs[boroughs.geom_type == "Polygon"]            # a tile of single-part areas
multi = boroughs[boroughs.geom_type == "MultiPolygon"]
single.to_postgis("boroughs_naive", engine, if_exists="replace")
try:
    multi.to_postgis("boroughs_naive", engine, if_exists="append")
except Exception as exc:
    print(type(exc).__name__, str(exc).splitlines()[0])

boroughs.to_postgis("boroughs_mixed", engine, if_exists="replace")      # both types at once
stops = gpd.read_file("data/NaPTAN West Yorkshire.shp").to_crs(27700).head(5)[["geometry"]]
stops.to_postgis("boroughs_mixed", engine, if_exists="append")           # bus stops, accepted
with engine.begin() as con:
    print(con.execute(text("SELECT type FROM geometry_columns WHERE f_table_name = 'boroughs_mixed'")).scalar(),
          con.execute(text("SELECT GeometryType(geometry), COUNT(*) FROM boroughs_mixed GROUP BY 1 ORDER BY 1")).all())

InvalidParameterValue Geometry type (MultiPolygon) does not match column type (Polygon)
GEOMETRY [('MULTIPOLYGON', 6), ('POINT', 5), ('POLYGON', 27)]

The first failure is loud. The second is worse: every one of this folder's seven council files mixes the two types, so every table gets an untyped column — and an untyped column accepts anything, including five bus stops appended to a table of boroughs.

Promoting the other way round — a single Polygon into a geometry(MultiPolygon) column — is accepted by PostGIS, which promotes it silently. Only MultiPolygon into geometry(Polygon) is rejected. Promoting everything to multi is therefore the safe direction. See PostGIS write fails on SRID or geometry type.

4. Load, then analyse — the index is already there

from sqlalchemy import text

def load_one(gdf, table, engine, srid=TARGET_SRID, chunksize=10_000):
    gdf.to_postgis(table, engine, if_exists="replace",
                   index=False, chunksize=chunksize)
    with engine.begin() as con:
        con.execute(text(f'ANALYZE "{table}"'))
    return table


load_one(prepare(gpd.read_file("data/E08 metropolitan districts.shp")), "index_check", engine)
with engine.begin() as con:
    print(con.execute(text("SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'index_check'")).all())

[('idx_index_check_geometry', 'CREATE INDEX idx_index_check_geometry ON public.index_check USING gist (geometry)')]

to_postgis creates the table through GeoAlchemy2, which adds a GiST index on the geometry column as part of creating it — idx_<table>_geometry. A script that then runs its own CREATE INDEX … USING GIST ends up with two identical indexes, both maintained on every write. What is still needed is ANALYZE: it gives the planner the statistics to actually choose the index; without it, a fresh table's index is often ignored. See PostGIS spatial indexes explained.

chunksize bounds how much is in flight at once. Without it, a 4-million-row frame becomes one enormous statement and one very long transaction.

5. Make the job resumable

A folder load is a batch job, and it should behave like one:

def already_loaded(engine, table):
    with engine.begin() as con:
        return con.execute(text(
            "SELECT to_regclass(:t) IS NOT NULL"), {"t": f"public.{table}"}).scalar()

Skipping existing tables means a job that died part-way resumes where it stopped instead of starting from zero. The general pattern is in how to build a resumable batch GIS job.

6. Verify

def verify(engine, table, expected_rows, srid=TARGET_SRID):
    with engine.begin() as con:
        got = con.execute(text(f'SELECT COUNT(*) FROM "{table}"')).scalar()
        srids = con.execute(text(
            f'SELECT DISTINCT ST_SRID(geometry) FROM "{table}"')).scalars().all()
        invalid = con.execute(text(
            f'SELECT COUNT(*) FROM "{table}" WHERE NOT ST_IsValid(geometry)')).scalar()
        has_idx = con.execute(text(
            "SELECT COUNT(*) FROM pg_indexes WHERE tablename = :t "
            "AND indexdef LIKE '%gist%'"), {"t": table}).scalar()
    problems = []
    if got != expected_rows: problems.append(f"{expected_rows:,} in → {got:,} out")
    if srids != [srid]:      problems.append(f"SRIDs {srids}")
    if not has_idx:          problems.append("no GiST index")
    if invalid:              problems.append(f"{invalid:,} invalid geometries")
    return problems

Row count in versus out is the check that catches a partial load, which otherwise looks exactly like a complete one.

The complete loader and many files into one table

The data used on this page

A supplier-style folder built from real open data. The ONS Local Authority Districts (December 2025, generalised) are split by code prefix into seven shapefiles with names like E06 unitary authorities.shp, written once into data/ and once into tiles/; Natural Earth's countries and the NaPTAN bus stops for West Yorkshire arrive in EPSG:4326; and Ward Areas (final).shp is a copy of the London boroughs with its .prj deleted — the file with no CRS. The database is a local PostGIS 3.4 in Docker (postgis/postgis:16-3.4); the connection details come from PGPORT and PGPASSWORD.

import io, os, shutil, time, zipfile
from pathlib import Path

import geopandas as gpd
import pandas as pd
import requests
from sqlalchemy import create_engine, text

# the server: PostGIS 3.4 in Docker on localhost:55432, emptied so every load below starts fresh
if os.system("docker start guide-postgis-55432 >/dev/null 2>&1") != 0:
    os.system("docker run -d --name guide-postgis-55432 -e POSTGRES_USER=user -e POSTGRES_PASSWORD=spatialworkflow "
              "-e POSTGRES_DB=gis -p 127.0.0.1:55432:5432 postgis/postgis:16-3.4 >/dev/null")
for _ in range(60):
    if os.system("docker exec guide-postgis-55432 pg_isready -h 127.0.0.1 -U user -d gis >/dev/null 2>&1") == 0:
        break
    time.sleep(2)
os.environ.setdefault("PGPORT", "55432")
os.environ.setdefault("PGPASSWORD", "spatialworkflow")
with create_engine("postgresql+psycopg://user@localhost/gis").begin() as con:
    for (table,) in con.execute(text("SELECT tablename FROM pg_tables WHERE schemaname = 'public' "
                                     "AND tablename <> 'spatial_ref_sys'")):
        con.execute(text(f'DROP TABLE "{table}" CASCADE'))

ONS = "https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services"

r = requests.get(f"{ONS}/Local_Authority_Districts_DEC_2025_Boundaries_UK_BGC/FeatureServer/0/query",
                 params={"where": "1=1", "outFields": "LAD25CD,LAD25NM", "outSR": 27700, "f": "geojson"},
                 timeout=300)
r.raise_for_status()
lads = gpd.read_file(io.BytesIO(r.content)).set_crs(27700, allow_override=True)
names = {"E06": "E06 unitary authorities", "E07": "E07 districts", "E08": "E08 metropolitan districts",
         "E09": "E09 London boroughs", "W06": "W06 Welsh principal areas",
         "S12": "S12 Scottish council areas", "N09": "N09 NI districts"}
for folder in ("data", "tiles"):
    Path(folder).mkdir(exist_ok=True)
    for prefix, name in names.items():
        lads[lads["LAD25CD"].str.startswith(prefix)].to_file(f"{folder}/{name}.shp")

ne = requests.get("https://naciscdn.org/naturalearth/110m/cultural/ne_110m_admin_0_countries.zip", timeout=120)
zipfile.ZipFile(io.BytesIO(ne.content)).extractall("data")

# NaPTAN area 450 as saved on 9 October 2026 (the register changes weekly); live:
# naptan.api.dft.gov.uk/v1/access-nodes?dataFormat=csv&atcoAreaCodes=450
stops = pd.read_csv(io.StringIO(requests.get("https://data.spatialworkflow.io/naptan-area-450-2026-10-09.csv", timeout=120).text), low_memory=False)
stops = stops.dropna(subset=["Longitude", "Latitude"])
gpd.GeoDataFrame(stops[["ATCOCode", "CommonName", "StopType"]],
                 geometry=gpd.points_from_xy(stops["Longitude"], stops["Latitude"]), crs=4326
                 ).to_file("data/NaPTAN West Yorkshire.shp")

for ext in ("shp", "shx", "dbf"):                      # a copy with its .prj missing
    shutil.copy(f"data/E09 London boroughs.{ext}", f"data/Ward Areas (final).{ext}")
print(sorted(p.name for p in Path("data").glob("*.shp")))

['E06 unitary authorities.shp', 'E07 districts.shp', 'E08 metropolitan districts.shp', 'E09 London boroughs.shp', 'N09 NI districts.shp', 'NaPTAN West Yorkshire.shp', 'S12 Scottish council areas.shp', 'W06 Welsh principal areas.shp', 'Ward Areas (final).shp', 'ne_110m_admin_0_countries.shp']

Example 1: the complete loader

from pathlib import Path
from collections import Counter
import re, time
import geopandas as gpd, pandas as pd, pyogrio
from sqlalchemy import create_engine, text

def load_folder(folder, engine, *, srid=27700, pattern="*.shp",
                prefix="", resume=True, chunksize=10_000):
    paths = sorted(Path(folder).glob(pattern))
    names = [safe_name(p.stem, prefix) for p in paths]
    clashes = {n: c for n, c in Counter(names).items() if c > 1}
    if clashes:
        raise ValueError(f"table name collisions: {clashes}")

    results = []
    for path, table in zip(paths, names):
        t0 = time.perf_counter()
        if resume and already_loaded(engine, table):
            results.append({"file": path.name, "table": table, "status": "skipped"})
            continue
        try:
            expected = pyogrio.read_info(path)["features"]
            gdf = prepare(gpd.read_file(path), srid=srid)
            load_one(gdf, table, engine, srid=srid, chunksize=chunksize)
            problems = verify(engine, table, len(gdf), srid=srid)
            results.append({
                "file": path.name, "table": table, "rows": len(gdf),
                "dropped": expected - len(gdf),
                "status": "ok" if not problems else "warn",
                "problems": "; ".join(problems),
                "seconds": round(time.perf_counter() - t0, 1),
            })
        except Exception as exc:
            results.append({"file": path.name, "table": table,
                            "status": "failed", "problems": str(exc)[:120]})

    df = pd.DataFrame(results)
    marks = {"ok": "✓", "warn": "!", "skipped": "·", "failed": "✗"}
    for r in results:
        print(f"  {marks[r['status']]} {r['table']:<30} "
              f"{r.get('rows', 0):>9,}  {r.get('problems', '')}")
    print(df["status"].value_counts().to_dict())
    return df

engine = create_engine("postgresql+psycopg://user@localhost/gis")
report = load_folder("data/", engine, srid=27700, prefix="lf_")
report.to_csv("load_report.csv", index=False)
print("--- run it again ---")
again = load_folder("data/", engine, srid=27700, prefix="lf_")

  ✓ lf_e06_unitary_authorities            63  
  ✓ lf_e07_districts                     164  
  ✓ lf_e08_metropolitan_districts         36  
  ✓ lf_e09_london_boroughs                33  
  ✓ lf_n09_ni_districts                   11  
  ✓ lf_naptan_west_yorkshire          16,677  
  ✓ lf_s12_scottish_council_areas         32  
  ✓ lf_w06_welsh_principal_areas          22  
  ✗ lf_ward_areas_final                    0  no CRS — cannot load safely
  ! lf_ne_110m_admin_0_countries         177  4 invalid geometries
{'ok': 8, 'failed': 1, 'warn': 1}
--- run it again ---
  · lf_e06_unitary_authorities             0  
  · lf_e07_districts                       0  
  · lf_e08_metropolitan_districts          0  
  · lf_e09_london_boroughs                 0  
  · lf_n09_ni_districts                    0  
  · lf_naptan_west_yorkshire               0  
  · lf_s12_scottish_council_areas          0  
  · lf_w06_welsh_principal_areas           0  
  ✗ lf_ward_areas_final                    0  no CRS — cannot load safely
  · lf_ne_110m_admin_0_countries           0  
{'skipped': 9, 'failed': 1}

Four statuses, not two. "Warn" means the table loaded but something needs attention; collapsing it into "ok" hides the four invalid country outlines in Natural Earth, which will break the next spatial join, and collapsing it into "failed" would send someone re-running a load that worked. dropped records the difference between the header count and the loaded count — null and empty geometries removed by prepare, which you want to know about rather than discover later.

Example 2: many files into one table

Often the files are tiles of one dataset, and the useful output is one table:

def append_folder(folder, table, engine, *, srid=27700,
                  source_col="source_file", pattern="*.shp"):
    """Load every file into a single table, tagged with its source."""
    paths = sorted(Path(folder).glob(pattern))
    total = 0
    for i, path in enumerate(paths):
        gdf = prepare(gpd.read_file(path), srid=srid)
        gdf[source_col] = path.stem
        gdf.to_postgis(table, engine,
                       if_exists="replace" if i == 0 else "append",
                       index=False, chunksize=10_000)
        total += len(gdf)
        print(f"  {path.name:<28} +{len(gdf):>8,}  total {total:>10,}")

    with engine.begin() as con:                    # the GiST index came with the table
        con.execute(text(f'CREATE INDEX ON "{table}" ({source_col})'))
        con.execute(text(f'ANALYZE "{table}"'))
    return total

append_folder("tiles/", "districts", engine)

  E06 unitary authorities.shp  +      63  total         63
  E07 districts.shp            +     164  total        227
  E08 metropolitan districts.shp +      36  total        263
  E09 London boroughs.shp      +      33  total        296
  N09 NI districts.shp         +      11  total        307
  S12 Scottish council areas.shp +      32  total        339
  W06 Welsh principal areas.shp +      22  total        361

Three things make this work where the naive version fails. replace on the first file and append afterwards creates the schema once — using replace throughout leaves you with only the last file. The source_file column preserves provenance that the merge would otherwise destroy, and makes a single tile reloadable with a DELETE WHERE source_file = …. And the source_file index is built once at the end rather than maintained across every insert.

Column mismatches are the remaining risk: appending a frame with different columns raises. Align them first if the supplier's schema drifts between tiles.

Example 3: ogr2ogr when Python is not the right tool

For a straight bulk load with no per-file logic, GDAL's own loader is faster because it streams and never builds a DataFrame:

import subprocess
from pathlib import Path

def ogr_load(path, table, dsn, srid=27700):
    cmd = [
        "ogr2ogr", "-f", "PostgreSQL", f"PG:{dsn}", str(path),
        "-nln", table,
        "-nlt", "PROMOTE_TO_MULTI",       # solves the Polygon/MultiPolygon split
        "-t_srs", f"EPSG:{srid}",
        "-lco", "GEOMETRY_NAME=geom",
        "-lco", "SPATIAL_INDEX=GIST",
        "-lco", "PRECISION=NO",
        "-overwrite",
        "--config", "PG_USE_COPY", "YES",  # COPY instead of INSERT — much faster
    ]
    proc = subprocess.run(cmd, capture_output=True, text=True)
    if proc.returncode != 0:
        raise RuntimeError(proc.stderr.strip()[:300])
    return table

DSN = "host=localhost dbname=gis user=user"          # port and password from PGPORT / PGPASSWORD
for path in sorted(Path("data").glob("*.shp")):
    try:
        ogr_load(path, safe_name(path.stem), DSN)
        print(f"  ✓ {path.name}")
    except RuntimeError as exc:
        print(f"  ✗ {path.name}: {exc}")

  ✓ E06 unitary authorities.shp
  ✓ E07 districts.shp
  ✓ E08 metropolitan districts.shp
  ✓ E09 London boroughs.shp
  ✓ N09 NI districts.shp
  ✓ NaPTAN West Yorkshire.shp
  ✓ S12 Scottish council areas.shp
  ✓ W06 Welsh principal areas.shp
  ✗ Ward Areas (final).shp: ERROR 1: Can't transform coordinates, source layer has no
coordinate system.  Use -s_srs to set one.
ERROR 1: Terminating translation prematurely after failed
translation of layer Ward Areas (final) (use -skipfailures to skip errors)
  ✓ ne_110m_admin_0_countries.shp

PG_USE_COPY=YES switches from per-row INSERT to PostgreSQL's bulk COPY protocol, the fast path for large loads. ogr2ogr also refuses the file with no CRS, with its own message. PROMOTE_TO_MULTI handles the geometry-type split in the loader rather than in your code, and SPATIAL_INDEX=GIST builds the index as part of table creation.

The trade-off is real: ogr2ogr is faster and knows more about formats, but you cannot inspect or clean the data on the way through. Use it when the files are trustworthy, and the Python route when they need cleaning — which, with supplier data, is most of the time.

A folder load is a schema negotiation

A folder load is a schema negotiation, and that is what makes it harder than it looks. Every shapefile carries its own implicit schema — field names, types, CRS, geometry type — and the database needs one explicit schema per table. Loading one file lets the file decide. Loading 340 means reconciling 340 opinions, and the reconciliation has no default.

The shapefile format makes this worse in specific, well-known ways. Field names are limited to ten characters, so population_density arrives as populatio — truncated by the format long before Python sees it, and unrecoverable without the supplier's documentation. The CRS lives in a separate .prj file that is routinely lost in transit, giving crs = None. There is no distinction between Polygon and MultiPolygon at the file level, so a single file can hold both. And there is no NULL: empty strings and zeros stand in for missing values. Every one of these becomes a database problem the moment you load without checking. GIS vector file formats compared covers why GeoPackage avoids most of them.

Typed geometry columns are where the format's laxity meets the database's strictness. PostGIS's geometry(MultiPolygon, 27700) is a constraint, and constraints reject data. Confirmed against PostGIS 3.4: a single Polygon inserted into a geometry(MultiPolygon, …) column is accepted and promoted, while a MultiPolygon into a geometry(Polygon, …) column is rejected with Geometry type (MultiPolygon) does not match column type (Polygon). The asymmetry is why promoting everything to multi is the safe direction, and why -nlt PROMOTE_TO_MULTI exists.

Know which tool builds the index. GeoPandas builds the spatial index when it creates the table; ogr2ogr's PostgreSQL driver builds one too unless told SPATIAL_INDEX=NONE. Building a second by hand doubles the write cost for nothing, and checking pg_indexes after a load is cheaper than assuming.

Finally, treat this as a batch job rather than a script. It has the same three requirements as any batch GIS job: per-file error isolation so one bad file does not sink the run, resumability so a failure at file 212 does not discard 211 successes, and a report that distinguishes "loaded", "loaded with problems" and "not loaded". The database part is the easy half.

Truncated names and missing .prj files

• Shapefile field names are truncated to 10 characters by the format. Nothing downstream can recover them; check the supplier's documentation.
• A missing .prj means crs = None. Do not guess — assigning a CRS relabels coordinates without moving them.
• Encoding matters. Shapefiles have no reliable encoding declaration; pass encoding="latin-1" or check the .cpg file. See garbled attribute text.
• if_exists="replace" drops indexes and constraints along with the table.
• to_postgis names the geometry column geometry, while ogr2ogr defaults to wkb_geometry unless you pass GEOMETRY_NAME=geom. Pick one convention.
• Reserved words — a table or column called order, user or default needs quoting forever. Sanitise those too.
• 63 bytes is the identifier limit. Longer names are silently truncated, which can create collisions.
• Loading into a schema needs schema="raw" on to_postgis and matching qualification in your index DDL.
• PG_USE_COPY=YES makes ogr2ogr load with COPY rather than row-by-row INSERT.
• Wrap per-file work in its own transaction so a failure rolls back one table, not the whole run.

Internal links

• How to write a GeoDataFrame to PostGIS — the single-table case in detail
• PostGIS spatial indexes explained — why the index goes last
• PostGIS write fails on SRID or geometry type — the errors this guide avoids
• How to build an inventory of a GIS data folder in Python — the audit step, generalised
• How to build a resumable batch GIS job in Python — surviving a failure at file 212
• How to batch process a folder of GIS files in Python — the pattern this follows
• GIS vector file formats compared — why shapefiles cause most of these problems
• How to update a PostGIS table from a GeoDataFrame without duplicates — the next load, not the first

FAQ

Why does my table name need quotes in every query?

It was created with capitals or spaces. PostgreSQL folds unquoted identifiers to lower case, so "Cheshire East" can only be referenced with quotes. Sanitise names before loading.

Why does the load fail with "Geometry type does not match column type"?

The table has a typed column such as geometry(Polygon, 27700) and the file contains MultiPolygon rows. Promote everything to multi before loading, or use -nlt PROMOTE_TO_MULTI.

Should I create the spatial index before or after loading?

After. if_exists="replace" drops it anyway, and maintaining an index through a bulk insert costs far more than building it once. Follow with ANALYZE.

How do I make the load resumable?

Check whether the target table already exists and skip it. SELECT to_regclass('public.tablename') IS NOT NULL is a cheap test that turns a restart into a resume.

Is ogr2ogr faster than GeoPandas?

Yes, substantially — it streams and can use COPY. Use it when the files need no inspection, and Python when they need cleaning or per-file logic.

What if the files have different CRS?

Reproject each to a single target SRID as it loads. Tables in different SRIDs cannot be joined without a per-query transform, and PostGIS raises rather than returning nothing.

Why are my column names truncated to ten characters?

The shapefile format truncates them on write. The full names never reached Python, and no code can recover them — check the supplier's documentation.