⚙️ How-To DuckDB & analytic SQL Getting started DuckDB for Spatial Data · step 3 of 20 Updated

How to Install and Load the DuckDB Spatial Extension

Problem statement

pip install duckdb gives you the engine. It does not give you geometry types, ST_Read, or a single spatial function:

>>> duckdb.connect().execute("select st_point(0, 0)")
CatalogException: Catalog Error: Scalar Function with name "st_point" is not in
the catalog, but it exists in the spatial extension.

The spatial extension is a separate binary, downloaded on demand from DuckDB's extension repository and cached in your home directory. That design is convenient on a laptop and the source of most spatial-extension failures elsewhere: containers with no outbound network, CI runners with a fresh home directory every job, and locked-down environments where the download is blocked.

Quick answer

Install once per machine, load once per connection:

import duckdb

con = duckdb.connect()
con.execute("install spatial")     # downloads; needs network; persists to ~/.duckdb
con.execute("load spatial")        # every new connection

print(con.execute("""
    select extension_name, extension_version, installed, loaded
    from duckdb_extensions() where extension_name = 'spatial'
""").fetchall())
[('spatial', 'eb1e57c', True, True)]

install writes into ~/.duckdb/extensions/<duckdb version>/<platform>/. The path includes the DuckDB version, so upgrading DuckDB requires re-installing the extension.

Flow from install through the extension directory to load and availability.
"It worked in the notebook and not in the script" is almost always the missing load.

Step-by-step solution

1. Check what you have before installing anything 

import duckdb

con = duckdb.connect()
print(f"duckdb {duckdb.__version__}")
print(con.execute("""
    select extension_name, installed, loaded, install_mode
    from duckdb_extensions()
    where extension_name in ('spatial', 'httpfs')
""").df())

install_mode tells you where it came from — REPOSITORY for a download, STATICALLY_LINKED for a build that includes it.

2. Install and load, and know which is which

  • install spatial downloads the binary once per DuckDB version per platform. It needs network access.
  • load spatial makes it available in the current connection. It needs no network but does need the file to be present.

A fresh connection in the same process still needs load. That is the most common cause of "it worked in the notebook and not in the script".

3. Understand the two failure messages 

IOException: IO Error: Extension "/home/me/.duckdb/extensions/v1.5.5/linux_amd64/
spatial.duckdb_extension" not found.

The extension is not installed for this DuckDB version and platform. Either it was never installed, or DuckDB was upgraded.

CatalogException: Catalog Error: Table Function with name "st_read" is not in the
catalog, but it exists in the spatial extension.

The extension exists but is not loaded in this connection. Add load spatial.

Two different messages, two different fixes, and the first is the one that appears in CI.

4. Make it work offline

Environments without outbound network need the extension present before the query runs. Three approaches:

Bake it into the image. Run install spatial at build time so the file is in the image:

RUN python -c "import duckdb; duckdb.connect().execute('install spatial; install httpfs;')"

Ship the extension directory. Copy ~/.duckdb/extensions into the image or the runner's cache. The path includes the DuckDB version, so pin the version.

Point at a local repository. DuckDB can install from a path or a custom URL:

con.execute("set custom_extension_repository = 'https://internal.example/duckdb'")
con.execute("install spatial")

5. Cache the extension in CI

A CI job with a fresh home directory downloads the extension on every run. Cache the directory, keyed on the DuckDB version:

- uses: actions/cache@v4
  with:
    path: ~/.duckdb
    key: duckdb-ext-${{ runner.os }}-${{ hashFiles('requirements.txt') }}

6. Verify the installation actually works

Loading successfully is not the same as working. A three-line smoke test catches a mismatched or partial installation:

con.execute("load spatial")
assert con.execute("select st_astext(st_point(1, 2))").fetchone()[0] == "POINT (1 2)"
assert con.execute("select st_area(st_geomfromtext('POLYGON((0 0,1 0,1 1,0 1,0 0))'))"
                   ).fetchone()[0] == 1.0
print("spatial extension is working")
Triage table of four DuckDB extension error messages and their fixes.
The catalog error is the friendliest in the system: it names the extension you need.

Code examples

Example 1 — a connection factory that handles all of it 

import duckdb


def spatial_connect(database=":memory:", extensions=("spatial",),
                    offline=False, memory_limit=None, threads=None):
    """Open a connection with the extensions loaded, and say clearly what failed."""
    con = duckdb.connect(database)
    con.execute("set enable_progress_bar = false")

    for name in extensions:
        try:
            con.execute(f"load {name}")
        except duckdb.IOException:
            if offline:
                raise RuntimeError(
                    f"extension '{name}' is not installed for duckdb "
                    f"{duckdb.__version__} and this environment is offline. "
                    f"Bake it into the image with: "
                    f"python -c \"import duckdb; duckdb.connect()"
                    f".execute('install {name}')\"") from None
            con.execute(f"install {name}")
            con.execute(f"load {name}")

    if memory_limit:
        con.execute(f"set memory_limit = '{memory_limit}'")
    if threads:
        con.execute(f"set threads = {threads}")
    return con

Trying load before install is deliberate: it makes the common case — already installed — a single fast call, and it turns the offline case into a message that says what to do.

Example 2 — a diagnostic for when it will not load 

import os
import platform
import duckdb


def diagnose_extension(name="spatial"):
    print(f"duckdb        {duckdb.__version__}")
    print(f"platform      {platform.system().lower()}_{platform.machine()}")

    home = os.path.expanduser("~/.duckdb/extensions")
    print(f"extension dir {home}  {'exists' if os.path.isdir(home) else 'MISSING'}")
    if os.path.isdir(home):
        for version in sorted(os.listdir(home)):
            for target in sorted(os.listdir(os.path.join(home, version))):
                files = os.listdir(os.path.join(home, version, target))
                print(f"  {version}/{target}: {', '.join(sorted(files))}")

    con = duckdb.connect()
    rows = con.execute("""
        select extension_name, installed, loaded, install_mode, installed_from
        from duckdb_extensions() where extension_name = ?""", [name]).fetchall()
    print(f"\nduckdb_extensions(): {rows}")

    try:
        con.execute(f"load {name}")
        print(f"load {name}: ok")
    except Exception as exc:
        print(f"load {name}: {type(exc).__name__}: {str(exc).splitlines()[0]}")
        print("\n  -> 'not found' means install it (needs network)")
        print("  -> a version in the path that differs from the duckdb version "
              "above means duckdb was upgraded; re-install")

Example 3 — a pytest fixture that fails helpfully 

import pytest
import duckdb


@pytest.fixture(scope="session")
def spatial_con():
    con = duckdb.connect()
    con.execute("set enable_progress_bar = false")
    try:
        con.execute("load spatial")
    except duckdb.IOException:
        try:
            con.execute("install spatial; load spatial;")
        except Exception as exc:
            pytest.skip(f"duckdb spatial unavailable: {exc}")
    assert con.execute("select st_astext(st_point(1, 2))").fetchone()[0] == "POINT (1 2)"
    yield con
    con.close()

pytest.skip rather than a failure is the right call for a test suite that must run in environments without network. A failing test tells you the code is broken; a skipped one tells you the environment is.

Explanation

Why the extension is a separate download

DuckDB ships a small core and moves optional functionality — spatial, httpfs, full-text search, format readers — into extensions loaded on demand. That keeps the base install small and lets extensions be updated independently.

The spatial extension in particular bundles GDAL, GEOS and PROJ, which is a large amount of native code. Including it in every DuckDB install would multiply the download for everybody who never uses it.

Why the path includes the DuckDB version

Extensions are compiled against DuckDB's internal C++ API, which is not stable between versions. An extension built for 1.4 cannot be loaded by 1.5, so the cache path is namespaced by version and platform.

The practical consequence: pip install --upgrade duckdb invalidates the extension cache, and the next load spatial fails with "not found". Re-install; it takes seconds.

Why the two error messages point at different problems

IOException: Extension "..." not found is a filesystem statement — DuckDB looked at a specific path and there was nothing there. Network, version, platform or a wiped home directory.

CatalogException: ... not in the catalog, but it exists in the spatial extension is a query-planning statement — DuckDB knows the function belongs to an extension that is not loaded in this connection. It is the friendliest error in the system, because it names the extension you need.

Why containers need this handled explicitly

A container that installs the extension at run time downloads it on every start, needs outbound network in production, and fails at the worst moment if the repository is unreachable.

Installing at build time makes the image self-contained and the start-up deterministic. It is one RUN line, and it converts a run-time network dependency into a build-time one.

Two panels contrasting run-time and build-time extension installation.
It converts a run-time network dependency into a build step.

Edge cases or notes

  • load is per connection, not per process. Connection pools must load on each.
  • Upgrading DuckDB invalidates the cache. Re-install after every version bump.
  • httpfs is a separate extension and is needed for s3:// and https:// reads.
  • INSTALL ... FROM accepts a path for air-gapped installs from a local file.
  • The Python API auto-installs some extensions in recent versions; do not rely on it in production.
  • duckdb_extensions() is the source of truth for what is installed and loaded.
  • The extension bundles GDAL, so its format support and its bugs are GDAL's.
  • Pin the DuckDB version in requirements, or CI caches will thrash.

FAQ

Why does st_point not exist after installing duckdb?

The spatial functions live in a separate extension. Run install spatial once per machine and load spatial in every connection.

What is the difference between install and load?

install downloads the extension binary into ~/.duckdb and needs network. load makes it available in the current connection and needs only the file.

Why did it stop working after upgrading duckdb?

Extensions are compiled against a specific DuckDB version and cached under a version-specific path. Re-run install spatial after the upgrade.

How do I use it in a container with no network?

Install it at image build time: RUN python -c "import duckdb; duckdb.connect().execute('install spatial')". That turns a run-time network dependency into a build-time one.

Do I need to load it in every connection?

Yes. load is connection-scoped, which is why a script can fail where an interactive session succeeded.

How do I check what is installed?

select * from duckdb_extensions() lists every extension with its installed, loaded and install-mode status.