---
name: duckdb-spatial-ops
description: Fast geospatial data processing, GeoParquet conversion, and analytical queries using DuckDB Spatial.
---

# DuckDB Spatial Operations

Fast in-process spatial SQL engine for local GIS data processing, GeoParquet conversion, analytical queries, and PostGIS data pipelines.

## When to Use

- High-speed local processing of large vector GIS datasets (GeoJSON, Shapefile, FlatGeobuf, GeoParquet) without spinning up a heavy server database.
- Converting multi-gigabyte spatial files into cloud-optimized GeoParquet.
- Executing spatial analytics (joins, distance, buffers, bounding box clipping) on millions of features in seconds.
- Staging and preprocessing raw GIS data before importing into a production PostGIS instance.
- Direct spatial queries across remote files (S3, HTTP, Parquet) with predicate and projection pushdown.

---

## 1. Quick Start & Setup

### In Python

```bash
uv pip install duckdb geopandas pyarrow
```

```python
import duckdb

con = duckdb.connect()
# Install and load spatial extension
con.execute("INSTALL spatial;")
con.execute("LOAD spatial;")
```

### In DuckDB CLI

```sql
INSTALL spatial;
LOAD spatial;
```

---

## 2. Reading Vector Formats

DuckDB Spatial uses `ST_Read` (wrapping GDAL drivers) or native Parquet readers:

```sql
-- Read GeoJSON
CREATE TABLE buildings AS
SELECT * FROM ST_Read('data/buildings.geojson');

-- Read Shapefile
CREATE TABLE parcels AS
SELECT * FROM ST_Read('data/parcels.shp');

-- Read FlatGeobuf (extremely fast binary spatial format)
CREATE TABLE hiking_trails AS
SELECT * FROM ST_Read('data/trails.fgb');

-- Read GeoParquet (fastest columnar format, native multithreaded)
CREATE TABLE austria_parcels AS
SELECT * FROM read_parquet('data/parcels.parquet');
```

---

## 3. High-Performance GeoParquet Conversion

Convert huge, slow GeoJSON or Shapefiles into modern, compressed GeoParquet:

```sql
COPY (
    SELECT 
        id,
        name,
        category,
        ST_AsWKB(geom) AS geometry
    FROM ST_Read('large_dataset.geojson')
) TO 'output.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);
```

Or using native geometry:
```sql
COPY (
    SELECT * FROM ST_Read('raw_data.shp')
) TO 'optimized.parquet' (FORMAT PARQUET);
```

---

## 4. Key Spatial Analytics Patterns

### Spatial Join (Point in Polygon)
```sql
SELECT 
    p.poi_name,
    d.district_name
FROM ST_Read('points_of_interest.geojson') p
JOIN ST_Read('districts.geojson') d
  ON ST_Contains(d.geom, p.geom);
```

### Bounding Box Clipping & Filtering
```sql
SELECT *
FROM read_parquet('austria_buildings.parquet')
WHERE ST_Intersects(
    geom, 
    ST_MakeEnvelope(16.30, 48.18, 16.45, 48.25) -- Vienna bounding box
);
```

### Distance & Buffer Calculations
```sql
SELECT 
    t.trail_name,
    h.hut_name,
    ST_Distance(t.geom, h.geom) AS distance_meters
FROM trails t, huts h
WHERE ST_DWithin(t.geom, h.geom, 500.0);
```

---

## 5. PostGIS <-> DuckDB Pipeline Pattern

DuckDB is **not a replacement** for PostGIS, but its ultimate companion:

```mermaid
flowchart LR
    A["Raw Data (GeoJSON, PBF, Shapefile)"] --> B["DuckDB Spatial (Local ETL & Filtering)"]
    B --> C["Clean GeoParquet / Filtered Tables"]
    C --> D["PostGIS Database (Production Web API & QGIS)"]
```

### Exporting directly from DuckDB to PostGIS (via Python):
```python
import duckdb
import geopandas as gpd
from sqlalchemy import create_engine

con = duckdb.connect()
con.execute("INSTALL spatial; LOAD spatial;")

# Perform fast local aggregation in DuckDB
df = con.execute("""
    SELECT 
        district,
        COUNT(*) as building_count,
        ST_AsText(ST_Union_Agg(geom)) as merged_geom
    FROM read_parquet('large_buildings.parquet')
    GROUP BY district
""").df()

# Convert WKT to geometry and push to PostGIS
df['geom'] = gpd.GeoSeries.from_wkt(df['merged_geom'], crs="EPSG:4326")
gdf = gpd.GeoDataFrame(df.drop(columns=['merged_geom']), geometry='geom')

engine = create_engine("postgresql://postgres:password@192.168.1.183:5432/gisdb")
gdf.to_postgis("district_building_summary", engine, if_exists="replace")
```

---

## 6. Best Practices

- **Memory Limit:** Always specify memory limit on large ETL jobs:
  `SET max_memory = '8GB';`
- **Threads:** DuckDB defaults to all CPU cores:
  `SET threads = 8;`
- **SRID Transformation:** Transform coordinates with `ST_Transform(geom, 'EPSG:4326', 'EPSG:31287')`.
- **Use GeoParquet for Storage:** 10x smaller file sizes and 50x faster read speeds compared to GeoJSON or Shapefiles.