🔧 Fix DuckDB & analytic SQL Files & formats DuckDB for Spatial Data · step 17 Notebook By · Updated

Fixing ST_Read Failures on Spatial Files in DuckDB

On this page

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 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 on Natural Earth's populated places, all 7,342 rows came back with the geometry intact and every one of the 137 attributes silently absent. And one failure is loud for a reason nobody expects: a Latin-1 shapefile whose .cpg says 1252 does not read at all.

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")
    print(path)

    virtual = path.startswith(("/vsi", "http://", "https://", "s3://"))
    if not virtual and not os.path.exists(path):
        print(f"1. path does not exist (cwd is {os.path.basename(os.getcwd())}/)")
        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()
        layers = [layer["name"] for layer in meta["layers"][0]]
        print(f"3. GDAL opened it with {meta['driver_short_name'][0]}: layers {layers}")
    except Exception as exc:
        print(f"3. GDAL cannot open it: {str(exc).splitlines()[0][:90]}")
        return

    try:
        n, t = con.execute(f"select count(*), any_value(typeof(geom)) "
                           f"from st_read('{path}')").fetchone()
        cols = len(con.execute(f"select * from st_read('{path}') limit 0").description) - 2
                                                    # minus OGC_FID and geom
    except duckdb.InvalidInputException as exc:
        print(f"4. read failed: {str(exc).splitlines()[0][:70]} — the text encoding")
        return
    print(f"4. {n:,} features, {cols} attribute columns, geometry type {t}")
    if t == "GEOMETRY":
        print("   ! no CRS — a missing .prj, or the source never had one")


for path in ("data/missing.shp", "noprj/ne_10m_populated_places.shp",
             "nodbf/ne_10m_populated_places.shp", "latin/towns.shp"):
    diagnose_st_read(path)
data/missing.shp
1. path does not exist (cwd is v_stread/)
noprj/ne_10m_populated_places.shp
2. .shx: present — required
2. .dbf: present — attributes
2. .prj: MISSING — the CRS
2. .cpg: present — the encoding
3. GDAL opened it with ESRI Shapefile: layers ['ne_10m_populated_places']
4. 7,342 features, 137 attribute columns, geometry type GEOMETRY
   ! no CRS — a missing .prj, or the source never had one
nodbf/ne_10m_populated_places.shp
2. .shx: present — required
2. .dbf: MISSING — attributes
2. .prj: present — the CRS
2. .cpg: present — the encoding
3. GDAL opened it with ESRI Shapefile: layers ['ne_10m_populated_places']
4. 7,342 features, 0 attribute columns, geometry type GEOMETRY('EPSG:4326')
latin/towns.shp
2. .shx: present — required
2. .dbf: present — attributes
2. .prj: present — the CRS
2. .cpg: present — the encoding
3. GDAL opened it with ESRI Shapefile: layers ['towns']
4. 3 features, 1 attribute columns, geometry type GEOMETRY('EPSG:4326')
Diagnosing an ST_Read failure from the outside in. the message says nothing — so test the parts1does the path exist?and from which working directory2are the sidecars present?.shx .dbf .prj .cpg3does GDAL have the driver?select * from st_drivers()4is it a multi-layer source?st_read_meta first5is it inside an archive?/vsizip/ /vsicurl/GDAL’s detailed diagnostics never reach DuckDB, so each step tests one thing it declined to explain spatialworkflow.io
One message covers a missing file, a missing sidecar, a missing driver and an archive.

Path, sidecars, drivers, layers and virtual files

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 fails, naming the missing .shx and the SHAPE_RESTORE_SHX option
.dbf no, in practice reads fine, every attribute silently gone
.prj no reads fine, no CRS, no warning
.cpg no UTF-8 text reads correctly; anything else fails the whole read

The .dbf and .prj 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.

The .cpg row is specific to the GDAL bundled with DuckDB. Without a .cpg, text is passed through as it is, which is right for a UTF-8 file and fatal for a Latin-1 one: Invalid Input Error: Invalid unicode (byte sequence mismatch). With one, only some values are understood — ISO-8859-1 and 88591 worked, while LATIN1 and 1252, the value Esri software usually writes, failed the same way. GeoPandas read all five variants. Name the encoding yourself:

select * from st_read('towns.shp', open_options = ['ENCODING=ISO-8859-1']);

ISO-8859-1 is close to, not the same as, Windows-1252: the two differ in 0x80–0x9F, where 1252 keeps curly quotes and the euro sign. For a 1252 file that uses them, convert with GeoPandas instead (Example 3).

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. In DuckDB 1.5.5 it has OpenFileGDB for Esri file geodatabases, but no Parquet or Arrow driver and only a few built-in text encodings. 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. Example 4 writes a GeoPackage with towns and regions layers; ST_Read without layer returned the towns:

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('/vsigzip/data/file.geojson.gz');
select count(*) from st_read('https://example.org/data.geojson');   -- DuckDB's own HTTP reader

/vsizip/ and /vsigzip/ are GDAL prefixes, and they are the correct way to read a zipped shapefile without extracting it. Remote files are different in DuckDB. A plain https:// URL works, because DuckDB hands GDAL its own file system. /vsicurl/… — GDAL's remote reader — failed with the usual "Could not open GDAL dataset", and so did /vsizip//vsicurl/…, a shapefile inside a remote zip. The bundled GDAL has curl; what it lacked was a list of trusted certificates, so every HTTPS connection failed and the message said nothing about it. With GDAL_CURL_CA_BUNDLE pointing at a CA file, both worked.

That makes the failure depend on what else the process has done. Importing pyogrio — which GeoPandas does — sets GDAL_CURL_CA_BUNDLE to certifi's bundle for the whole process, so the same ST_Read works in a notebook that has touched GeoPandas and fails in a script that has not. Set it yourself, before the first remote read:

import os, certifi
os.environ.setdefault("GDAL_CURL_CA_BUNDLE", certifi.where())

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.

What each shapefile sidecar does and what happens without it. the two dangerous rows are not errorsrequiredwithout it.shp✓yesnothing opens.shx✓yesfails, naming the .shx.dbf!no✕reads fine, no attributes.prj!no✕reads fine, no CRS.cpgno!only UTF-8 text readsMeasured: .shp plus .shx alone returned all 7,342 features with every attribute silently gone spatialworkflow.io
A missing .prj is worse than a missing .shp: one fails, the other produces wrong results.

Preflight checks on Natural Earth's places

The data used on this page

Natural Earth's 1:10m populated places — 7,342 points, 137 attributes, a .cpg saying UTF-8 — and a set of damaged copies, each missing one sidecar. latin/towns.shp is three of those towns written in Latin-1 with the .cpg that Esri software usually writes, 1252. Every failure described above is reproduced from these files in Example 4.

import os
import shutil
import zipfile

import geopandas as gpd
import requests

URL = "https://naciscdn.org/naturalearth/10m/cultural/ne_10m_populated_places.zip"
STEM = "ne_10m_populated_places"
if not os.path.exists("pp.zip"):
    open("pp.zip", "wb").write(requests.get(URL, timeout=120,
                                            headers={"User-Agent": "spatialworkflow.io guide"}).content)
zipfile.ZipFile("pp.zip").extractall("data")

for missing in (".dbf", ".prj", ".shx", ".cpg"):          # one damaged copy per sidecar
    folder = "no" + missing[1:]
    os.makedirs(folder, exist_ok=True)
    for ext in (".shp", ".shx", ".dbf", ".prj", ".cpg"):
        if ext != missing:
            shutil.copy(f"data/{STEM}{ext}", f"{folder}/{STEM}{ext}")

os.makedirs("latin", exist_ok=True)
towns = gpd.read_file(f"data/{STEM}.shp", columns=["NAME"])
towns[towns["NAME"].isin(["Zürich", "Montréal", "São Paulo"])].to_file(
    "latin/towns.shp", encoding="latin1")
open("latin/towns.cpg", "w").write("1252")
print(sorted(os.listdir("data")))
['ne_10m_populated_places.README.html', 'ne_10m_populated_places.VERSION.txt', 'ne_10m_populated_places.cpg', 'ne_10m_populated_places.dbf', 'ne_10m_populated_places.prj', 'ne_10m_populated_places.shp', 'ne_10m_populated_places.shx']

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


for path in ("data/ne_10m_populated_places.shp", "noprj/ne_10m_populated_places.shp",
             "noshx/ne_10m_populated_places.shp"):
    print(path)
    try:
        check_shapefile(path)
    except FileNotFoundError as error:
        print("  FileNotFoundError:", str(error).splitlines()[1].strip())
data/ne_10m_populated_places.shp
noprj/ne_10m_populated_places.shp
  ! .prj is missing — the CRS will be unknown
noshx/ne_10m_populated_places.shp
  FileNotFoundError: .shx is missing and is required

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})")


con = duckdb.connect()
con.execute("load spatial")
read_with_crs(con, "data/ne_10m_populated_places.shp", expected_crs="EPSG:4326")
print("data/: EPSG:4326 as expected")
try:
    read_with_crs(con, "noprj/ne_10m_populated_places.shp", expected_crs="EPSG:4326")
except ValueError as error:
    print("ValueError:", str(error)[:90], "…")
data/: EPSG:4326 as expected
ValueError: noprj/ne_10m_populated_places.shp: no CRS. A shapefile needs its .prj; a join against data …

Example 3 — falling back to a conversion when the bundled GDAL cannot read it

import duckdb


def read_or_convert(con, path, layer=None):
    """If the bundled GDAL cannot read it, convert with pyogrio and retry."""
    try:
        return con.execute(f"select * from st_read('{path}')").df()
    except (duckdb.Error, SystemError) as exc:        # see below for SystemError
        print(f"st_read failed: {type(exc).__name__}")

        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 Parquet ({len(gdf):,} features)")
        return con.execute(f"select * from read_parquet('{target}')").df()


frame = read_or_convert(con, "latin/towns.shp")
print(sorted(frame["NAME"]))
st_read failed: SystemError
  bundled GDAL has 54 drivers
  converted via geopandas to Parquet (3 features)
['Montréal', 'São Paulo', 'Zürich']

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 supports formats, encodings and remote access the bundled one does not. An encoding failure is not an IOException, which is all the first version of this function caught — and what it is depends on how the rows are fetched. fetchall() raised InvalidInputException: Invalid unicode. .df() raised a bare SystemError, or, in a process that had not yet imported pandas, an InvalidInputException claiming that pandas is not installed. Catch duckdb.Error and SystemError together.

Example 4 — every failure on this page, reproduced

import gzip

one = lambda sql: con.execute(sql).fetchone()


def attempt(label, sql):
    try:
        print(f"{label:34} {one(sql)}")
    except Exception as exc:
        print(f"{label:34} {type(exc).__name__}: {str(exc).splitlines()[0][:70]}")


S = STEM + ".shp"
attempt("missing file", "select count(*) from st_read('data/missing.shp')")
attempt("no .dbf: rows, columns", f"select count(*), (select count(*) from (describe "
        f"select * from st_read('nodbf/{S}'))) from st_read('nodbf/{S}')")
attempt("no .prj: geometry type", f"select any_value(typeof(geom)) from st_read('noprj/{S}')")
attempt("no .shx", f"select count(*) from st_read('noshx/{S}')")
attempt("no .cpg, UTF-8 text", f"select list(NAME order by NAME) from st_read('nocpg/{S}') "
        f"where NAME in ('Zürich', 'Montréal', 'São Paulo')")
for cpg in ("1252", "LATIN1", "ISO-8859-1", "88591", None):
    if cpg:
        open("latin/towns.cpg", "w").write(cpg)
    elif os.path.exists("latin/towns.cpg"):
        os.remove("latin/towns.cpg")
    attempt(f"Latin-1 text, .cpg {cpg}", "select list(NAME order by NAME) from st_read('latin/towns.shp')")
attempt("Latin-1, open_options ENCODING", "select list(NAME order by NAME) from "
        "st_read('latin/towns.shp', open_options = ['ENCODING=ISO-8859-1'])")

attempt("/vsizip/ on a local zip", f"select count(*) from st_read('/vsizip/pp.zip/{S}')")
con.execute(f"copy (select NAME, geom from st_read('data/{S}') limit 50) "
            "to 'fifty.geojson' with (format gdal, driver 'GeoJSON')")
open("fifty.geojson.gz", "wb").write(gzip.compress(open("fifty.geojson", "rb").read()))
attempt("/vsigzip/", "select count(*) from st_read('/vsigzip/fifty.geojson.gz')")
# remote reads, each in a fresh process with a controlled environment
import subprocess
import sys

import certifi

GEOJSON = ("https://raw.githubusercontent.com/nvkelso/natural-earth-vector/v5.1.2/"
           "geojson/ne_110m_populated_places_simple.geojson")
ZIP = ("https://naciscdn.org/naturalearth/110m/cultural/ne_110m_populated_places.zip"
       "/ne_110m_populated_places.shp")
PROBE = """
import duckdb, sys
con = duckdb.connect(); con.load_extension("spatial")
try:
    print(con.execute(f"select count(*) from st_read('{sys.argv[1]}')").fetchone()[0], "rows")
except Exception as exc:
    print(type(exc).__name__)
"""
clean = {k: v for k, v in os.environ.items() if "CA_BUNDLE" not in k}
for label, path in (("https://", GEOJSON), ("/vsicurl/", "/vsicurl/" + GEOJSON),
                    ("/vsizip//vsicurl/ remote zip", "/vsizip//vsicurl/" + ZIP)):
    for bundle in (None, certifi.where()):
        env = clean if bundle is None else dict(clean, GDAL_CURL_CA_BUNDLE=bundle)
        result = subprocess.run([sys.executable, "-c", PROBE, path], env=env,
                                capture_output=True, text=True).stdout.strip()
        print(f"{label:28} {'GDAL_CURL_CA_BUNDLE set' if bundle else 'no CA bundle':23} {result}")
print(f"{'remote zip, in GeoPandas':52} {len(gpd.read_file('/vsizip//vsicurl/' + ZIP))} rows")
print("set by importing pyogrio:", sorted(k for k in os.environ if "CA_BUNDLE" in k))

con.execute(f"copy (select NAME, geom from st_read('data/{S}') where ADM0NAME = 'Iceland') "
            "to 'two.gpkg' with (format gdal, driver 'GPKG', layer_name 'towns')")
gpd.read_file(f"data/{S}").query("ADM0NAME == 'Iceland'").dissolve().to_file("two.gpkg", layer="regions")
attempt("GeoPackage layers", "select list(l.name) from (select unnest(layers) as l "
        "from st_read_meta('two.gpkg'))")
attempt("st_read without layer=", "select count(*), any_value(NAME) from st_read('two.gpkg')")
missing file                       IOException: IO Error: Could not open GDAL dataset at: data/missing.shp
no .dbf: rows, columns             (7342, 2)
no .prj: geometry type             ('GEOMETRY',)
no .shx                            IOException: IO Error: Unable to open noshx/ne_10m_populated_places.shx or /vsiduck
no .cpg, UTF-8 text                (['Montréal', 'São Paulo', 'Zürich'],)
Latin-1 text, .cpg 1252            InvalidInputException: Invalid Input Error: Invalid unicode (byte sequence mismatch) detected
Latin-1 text, .cpg LATIN1          InvalidInputException: Invalid Input Error: Invalid unicode (byte sequence mismatch) detected
Latin-1 text, .cpg ISO-8859-1      (['Montréal', 'São Paulo', 'Zürich'],)
Latin-1 text, .cpg 88591           (['Montréal', 'São Paulo', 'Zürich'],)
Latin-1 text, .cpg None            InvalidInputException: Invalid Input Error: Invalid unicode (byte sequence mismatch) detected
Latin-1, open_options ENCODING     (['Montréal', 'São Paulo', 'Zürich'],)
/vsizip/ on a local zip            (7342,)
/vsigzip/                          (50,)
https://                     no CA bundle            243 rows
https://                     GDAL_CURL_CA_BUNDLE set 243 rows
/vsicurl/                    no CA bundle            IOException
/vsicurl/                    GDAL_CURL_CA_BUNDLE set 243 rows
/vsizip//vsicurl/ remote zip no CA bundle            IOException
/vsizip//vsicurl/ remote zip GDAL_CURL_CA_BUNDLE set 243 rows
remote zip, in GeoPandas                             243 rows
set by importing pyogrio: ['GDAL_CURL_CA_BUNDLE', 'PROJ_CURL_CA_BUNDLE']
GeoPackage layers                  (['towns', 'regions'],)
st_read without layer=             (9, 'Borgarnes')

Why GDAL says so little

Why the error message is so unhelpful

ST_Read calls GDALOpenEx, which returns a null dataset when no driver recognises the file. Then there is no detail to pass on, and DuckDB can only report that the open failed. When a driver does recognise the file and fails partway — the shapefile driver without its .shx — GDAL's own message comes through, and it is specific.

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. DuckDB does it too, once GDAL can find a CA bundle.

GDAL's virtual filesystem prefixes for archives and remote files. read inside archives without extracting/vsizip/inside a zipno extraction, no cleanup/vsigzip/a gzipped filestreamed/vsicurl/over HTTPneeds GDAL_CURL_CA_BUNDLEthey compose/vsizip//vsicurl/https://…a shapefile in a remote zipGDAL prefixes; a plain https:// URL goes through DuckDB’s own reader instead spatialworkflow.io
Extraction creates temporary files that have to be cleaned up; these do not.

Relative paths, missing sidecars and multi-layer files

  • 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.
  • /vsicurl/ needs GDAL_CURL_CA_BUNDLE in DuckDB — without it every HTTPS read fails with the generic message. Importing pyogrio sets it, which hides the problem in notebooks.
  • Encoding comes from .cpg, and the bundled GDAL understands few values: ISO-8859-1 works, 1252 and LATIN1 fail the whole read. Without a .cpg, only UTF-8 text reads. open_options = ['ENCODING=…'] overrides both.
  • 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.

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. A zip on a web server works as /vsizip//vsicurl/https://…/archive.zip/file.shp, once GDAL_CURL_CA_BUNDLE points at a CA file.

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 and few text encodings; st_drivers() lists the drivers it supports. A Latin-1 shapefile whose .cpg says 1252 is the common case: GeoPandas reads it, DuckDB refuses.