🔧 Fix DuckDB & analytic SQL Files & formats DuckDB for Spatial Data · step 17 of 20 Updated

Fixing ST_Read Failures on Spatial Files in DuckDB

Problem statement

ST_Read hands the file to GDAL, so its failures are GDAL's failures wearing a DuckDB message. The most common one says almost nothing:

IOException: IO Error: Could not open GDAL dataset at: /data/provinces.shp

That single message covers a missing file, a missing sidecar, a format GDAL was not built with, a permissions problem, an unreadable remote URL and a corrupt archive.

Worse are the failures that are not failures. A shapefile with no .prj reads perfectly and yields geometry with no CRS and no warning. A shapefile with no .dbf also reads — measured, 7,342 rows came back with the geometry intact and every attribute silently absent.

Quick answer

Work down the list, cheapest first:

import os
import duckdb


def diagnose_st_read(path, con=None):
    con = con or duckdb.connect()
    con.execute("load spatial")

    if not os.path.exists(path.split("/")[0] if path.startswith("/vsi") else path):
        print(f"1. path does not exist: {path}")
        return

    if path.lower().endswith(".shp"):
        stem = path[:-4]
        for suffix, needed in ((".shx", "required"), (".dbf", "attributes"),
                               (".prj", "the CRS"), (".cpg", "the encoding")):
            present = os.path.exists(stem + suffix)
            print(f"2. {suffix}: {'present' if present else 'MISSING'} — {needed}")

    try:
        meta = con.execute(f"select * from st_read_meta('{path}')").df()
        print(f"3. GDAL opened it: {len(meta)} layer group(s)")
    except Exception as exc:
        print(f"3. GDAL cannot open it: {str(exc).splitlines()[0]}")
        print("   check the driver list: select * from st_drivers()")
        return

    n = con.execute(f"select count(*) from st_read('{path}')").fetchone()[0]
    t = con.execute(f"select typeof(geom) from st_read('{path}') limit 1").fetchone()[0]
    print(f"4. {n:,} features, geometry type {t}")
    if t == "GEOMETRY":
        print("   ! no CRS — a missing .prj, or the source never had one")
Five ordered diagnostic steps for an ST_Read failure.
One message covers a missing file, a missing sidecar, a missing driver and an archive.

Step-by-step solution

1. Confirm the path, from DuckDB's point of view

Relative paths resolve against the process's working directory, which in a notebook, a scheduler or a container is frequently not where you think.

import os
print(os.getcwd())
print(os.path.abspath(path), os.path.exists(path))

Use absolute paths in anything scheduled. It is the single most common cause of this error and the least interesting.

2. Check the shapefile's sidecars

A shapefile is a set of files, and different ones are missing in different ways:

File Required What happens without it
.shp yes nothing opens
.shx yes GDAL may fail or attempt a rebuild
.dbf no, in practice reads fine, every attribute silently gone
.prj no reads fine, no CRS, no warning
.cpg no encoding is guessed; non-ASCII text may be mangled

The two middle rows are the dangerous ones because they are not errors. Measured, a .shp plus .shx alone returned all 7,342 features with the geometry correct and no attributes at all.

3. Ask GDAL what it can open 

select short_name, long_name, can_create from st_drivers() order by short_name;

The bundled GDAL build is not the same as a system GDAL. Formats you use elsewhere — FileGDB, some database drivers, specific raster formats — may simply not be in the list. If the driver is absent, no amount of path fixing helps; convert the file with another tool first.

4. Name the layer for multi-layer sources

A GeoPackage or a GDB usually holds several layers, and ST_Read without a layer name reads the first one — which is rarely the one you want:

select * from st_read_meta('data.gpkg');                 -- list the layers
select count(*) from st_read('data.gpkg', layer = 'roads');

5. Use GDAL's virtual filesystems for archives and remote files 

select count(*) from st_read('/vsizip/data/archive.zip/inner/file.shp');
select count(*) from st_read('/vsicurl/https://example.org/data.geojson');
select count(*) from st_read('/vsigzip/data/file.geojson.gz');

These are GDAL prefixes, not DuckDB ones, and they are the correct way to read a zipped shapefile without extracting it. Note that /vsicurl/ is GDAL's remote reader, not DuckDB's httpfs — the two are separate mechanisms with different behaviour.

6. Check the CRS, because nothing else will 

select typeof(geom) from st_read('file.shp') limit 1;
-- GEOMETRY('EPSG:4326')   <- has a CRS
-- GEOMETRY                <- does not

A bare GEOMETRY means the source declared no CRS. The file still reads, joins still run, and a join against data in a different system returns zero rows with no error. Assert on this in any pipeline that reads files it did not create.

Grid of five shapefile sidecar files, whether each is required, and the effect of its absence.
A missing .prj is worse than a missing .shp: one fails, the other produces wrong results.

Code examples

Example 1 — a preflight check for a shapefile 

import os


REQUIRED = {".shp", ".shx"}
IMPORTANT = {".dbf": "attributes will be missing",
             ".prj": "the CRS will be unknown",
             ".cpg": "the text encoding will be guessed"}


def check_shapefile(path):
    stem, ext = os.path.splitext(path)
    if ext.lower() != ".shp":
        return True

    problems, warnings = [], []
    for suffix in REQUIRED:
        if not os.path.exists(stem + suffix):
            problems.append(f"{suffix} is missing and is required")
    for suffix, consequence in IMPORTANT.items():
        if not os.path.exists(stem + suffix):
            warnings.append(f"{suffix} is missing — {consequence}")

    for warning in warnings:
        print(f"  ! {warning}")
    if problems:
        raise FileNotFoundError(f"{path}:\n  " + "\n  ".join(problems))
    return True

Example 2 — reading with the CRS asserted 

import duckdb


def read_with_crs(con, path, expected_crs=None, layer=None):
    """Read, and refuse to continue if the CRS is unknown or unexpected."""
    args = f"'{path}'" + (f", layer='{layer}'" if layer else "")
    geom_type = con.execute(
        f"select typeof(geom) from st_read({args}) limit 1").fetchone()

    if geom_type is None:
        raise ValueError(f"{path}: opened, but contains no features")

    declared = geom_type[0]
    if declared == "GEOMETRY":
        message = (f"{path}: no CRS. A shapefile needs its .prj; a join against "
                   f"data in another CRS will return zero rows with no error.")
        if expected_crs:
            raise ValueError(message + f" Expected {expected_crs}.")
        print(f"  ! {message}")
    elif expected_crs and expected_crs not in declared:
        raise ValueError(f"{path}: CRS is {declared}, expected {expected_crs}")

    return con.execute(f"select * from st_read({args})")

Example 3 — falling back to a conversion when the driver is absent 

def read_or_convert(con, path, layer=None):
    """If the bundled GDAL cannot open it, convert with pyogrio and retry."""
    try:
        return con.execute(f"select * from st_read('{path}')").df()
    except duckdb.IOException as exc:
        print(f"st_read failed: {str(exc).splitlines()[0]}")

        drivers = {row[0] for row in con.execute(
            "select short_name from st_drivers()").fetchall()}
        print(f"  bundled GDAL has {len(drivers)} drivers")

        import tempfile
        import geopandas as gpd
        gdf = gpd.read_file(path, layer=layer)         # a different GDAL build
        with tempfile.NamedTemporaryFile(suffix=".parquet", delete=False) as handle:
            target = handle.name
        gdf.to_parquet(target)
        print(f"  converted via geopandas to {target} ({len(gdf):,} features)")
        return con.execute(f"select * from read_parquet('{target}')").df()

The point of the fallback is that GeoPandas' GDAL — installed through pyogrio — is a different build from the one bundled with the DuckDB extension, and frequently supports formats the bundled one does not.

Explanation

Why the error message is so unhelpful

ST_Read calls GDALOpenEx, which returns a null dataset on failure. GDAL's detailed diagnostics go to its own error handler, which the extension does not surface, so DuckDB can only report that the open failed.

That is why the diagnostic sequence in this article works from the outside in: existence, sidecars, drivers, layers. Each step tests one of the things GDAL declined to explain.

Why a missing .prj is worse than a missing .shp

A missing .shp is a hard failure at the first line of the script. A missing .prj produces a working pipeline whose spatial results are wrong in ways no test catches: a join returns zero rows, an area comes back in square degrees, a buffer is an ellipse.

DuckDB does not raise on a CRS mismatch, so the whole class of failure is silent. Asserting on typeof(geom) after every read of an external file is the cheapest defence available.

Why the bundled GDAL differs from your system GDAL

The DuckDB spatial extension ships a statically linked GDAL chosen for portability. It supports the common formats and omits ones with heavy dependencies or restrictive licences.

st_drivers() lists exactly what this build supports. When a format is missing, the fix is a conversion step in another tool — not an argument with DuckDB.

Why /vsizip/ beats extracting

Extracting a zipped shapefile creates temporary files that have to be cleaned up, doubles the disk usage and adds a step that can fail. GDAL's virtual filesystems read inside the archive directly.

They also compose: /vsizip//vsicurl/https://example.org/data.zip/file.shp reads a shapefile inside a zip file on a remote server without downloading the archive. That is occasionally exactly what you need.

Four GDAL virtual filesystem prefixes and what each reads.
Extraction creates temporary files that have to be cleaned up; these do not.

Edge cases or notes

  • Absolute paths in anything scheduled. Relative paths resolve against a working directory you did not choose.
  • .dbf missing reads fine with no attributes — measured on a real file.
  • .prj missing reads fine with no CRS and no warning of any kind.
  • st_read_meta() is the cheap probe — it lists layers without reading features.
  • Multi-layer sources need layer=, or you get the first layer.
  • /vsizip/, /vsicurl/, /vsigzip/ are GDAL prefixes, unrelated to httpfs.
  • Encoding comes from .cpg when present, and is guessed otherwise — expect mojibake on non-ASCII attributes.
  • Case sensitivity matters on Linux: FILE.SHP and file.shp are different files.

FAQ

What does "Could not open GDAL dataset" mean?

Only that GDAL declined to open the file. Check, in order: the path, the shapefile sidecars, whether the driver exists in st_drivers(), and whether the source is an archive needing a /vsizip/ prefix.

Why does my shapefile read but have no attributes?

The .dbf is missing. Measured, a .shp plus .shx returned all 7,342 features with correct geometry and no attribute columns at all.

Why does my geometry have no CRS?

The source declared none — usually a missing .prj. The file still reads, and a later join against data in another CRS will return zero rows without an error.

How do I read a specific layer from a GeoPackage?

st_read('data.gpkg', layer = 'roads'). Without the layer name you get the first one, which is rarely the one you want.

Can DuckDB read a zipped shapefile?

Yes, through GDAL's virtual filesystem: st_read('/vsizip/data/archive.zip/inner/file.shp'). No extraction needed.

Why can GeoPandas open a file DuckDB cannot?

They use different GDAL builds. The extension bundles a statically linked GDAL with a smaller driver set; st_drivers() lists exactly what it supports.