bluegreencity-ki

Spatial Database Technology Comparison: SedonaDB vs DuckDB vs PostGIS

Purpose: This document evaluates SedonaDB and DuckDB as potential additions or alternatives to our current PostGIS-based geospatial stack for the BlueGreenCity-KI urban tree drought risk assessment framework.

Date: 2025-11-15
Status: Evaluation Document
Related: ADR-002: Spatial Database Technology Evaluation

Table of Contents

Executive Summary

Current State: BlueGreenCity-KI uses PostGIS as the primary geodatabase for multi-user access, with GeoPandas for in-memory processing.

Evaluation: This document analyzes SedonaDB (Apache Sedona) and DuckDB as complementary technologies that could enhance our geospatial processing capabilities in specific scenarios.

Key Findings

Aspect PostGIS SedonaDB DuckDB
Best For Multi-user OLTP, persistent storage Spatial analytics on single machine Fast analytical queries, embedded
Performance Good for transactional workloads Excellent for analytical spatial queries Excellent for non-spatial analytics
Ease of Use Moderate (SQL, admin overhead) Simple (embedded, zero-config) Simple (embedded, zero-config)
Multi-user ✅ Excellent ❌ Limited (file-based) ❌ Limited (file-based)
Infrastructure Requires server None (embedded) None (embedded)
Python Integration Good (psycopg2, GeoAlchemy2) Excellent (SedonaDB Python API) Excellent (DuckDB Python API)
GeoPandas Interop Good (via GeoAlchemy2) ✅ Excellent (native support) ✅ Excellent (native support)
Spatial Functions ✅ Very extensive ✅ Good (growing) ⚠️ Basic (via spatial extension)

Recommendation Summary

For BlueGreenCity-KI:

  1. Keep PostGIS as primary database for production multi-user operations
  2. Evaluate SedonaDB vs DuckDB for analytical workflows - both are lightweight, embedded alternatives
    • SedonaDB: Better for spatial-heavy analytics (spatial joins, geometric operations)
    • DuckDB: Better for general analytics with some spatial needs
  3. Consider hybrid approach: SedonaDB for spatial analytics, DuckDB for non-spatial analytics

Technology Overview

PostGIS (Current)

Description: PostgreSQL extension for spatial data management

Architecture:

Current Role in BlueGreenCity-KI:

Strengths:

Limitations:

SedonaDB

Description: Lightweight geospatial SQL database engine that runs on single computers without requiring Apache Spark infrastructure

Important Distinction:

Architecture:

Key Features:

Strengths:

Limitations:

DuckDB

Description: Embedded analytical database (SQLite for analytics)

Architecture:

Key Features:

Strengths:

Limitations:

Performance Comparison

Benchmark Summary (from forrest.nyc analysis)

Test Dataset: NYC taxi trips with spatial operations

Operation PostGIS SedonaDB DuckDB Winner
Load 1M points 45s 12s 8s DuckDB
Spatial join (1M points) 180s 35s 42s SedonaDB
Aggregation query 22s 8s 3s DuckDB
Range query 5s 4s 2s DuckDB
Complex spatial join 320s 45s 98s SedonaDB

Key Insights:

  1. DuckDB wins for non-spatial analytical queries and aggregations
  2. SedonaDB wins for spatial joins and spatial-heavy analytics
  3. PostGIS performs well for transactional workloads but lags in analytics

Performance Characteristics by Scale

Small Datasets (<1M features):

Medium Datasets (1M-10M features):

Large Datasets (>10M features):

Note: For truly massive datasets (>100GB, >100M features), SedonaDB can migrate to distributed Apache Sedona framework, but this is not needed for typical urban tree inventories.

BlueGreenCity-KI Dataset Context

Current Scale:

Projected Growth:

Performance Assessment:

Use Cases for BlueGreenCity-KI

Use Case 1: Rapid Analytical Exploration (DuckDB ⭐⭐⭐)

Scenario: Data scientist wants to quickly explore relationships between tree risk scores and environmental factors

With DuckDB:

import duckdb
import geopandas as gpd

# Connect to DuckDB (in-memory or file-based)
con = duckdb.connect('analysis.duckdb')

# Install and load spatial extension
con.execute("INSTALL spatial; LOAD spatial;")

# Load tree data directly from GeoParquet (no import step!)
con.execute("""
    CREATE VIEW trees AS 
    SELECT * FROM read_parquet('data/trees.parquet')
""")

# Fast analytical queries
result = con.execute("""
    SELECT 
        vitality,
        COUNT(*) as count,
        AVG(overall_drought_risk) as avg_risk,
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY overall_drought_risk) as p90_risk
    FROM trees
    WHERE age_score > 0.5
    GROUP BY vitality
    ORDER BY avg_risk DESC
""").df()

# Convert to GeoPandas for mapping
high_risk_trees = con.execute("""
    SELECT * FROM trees 
    WHERE overall_drought_risk > 0.8
""").fetchdf()
gdf = gpd.GeoDataFrame(high_risk_trees, geometry='geometry')

Benefits:

Current Alternative: Load into Pandas/GeoPandas → slower for large aggregations

Use Case 2: Fast Spatial Joins (SedonaDB ⭐⭐⭐)

Scenario: Perform spatial join between trees and climate grid cells - operation that’s slow in PostGIS for large datasets

With SedonaDB:

import sedonadb

# Connect to SedonaDB (embedded, no server needed)
con = sedonadb.connect()

# Load trees and climate data from GeoParquet
con.execute("""
    CREATE TABLE trees AS 
    SELECT * FROM read_parquet('data/all_cities_trees.parquet')
""")

con.execute("""
    CREATE TABLE climate_grid AS 
    SELECT * FROM read_parquet('data/climate_grid.parquet')
""")

# Fast spatial join using SedonaDB's optimized spatial indexing
result = con.execute("""
    SELECT 
        t.city,
        t.tree_id,
        t.geometry AS tree_geom,
        c.temperature,
        c.precipitation
    FROM trees t
    JOIN climate_grid c
    ON ST_Contains(c.geometry, t.geometry)
""").df()

# Export results
result.to_parquet("output/trees_with_climate.parquet")

Benefits:

Current Alternative: GeoPandas (limited by memory) or PostGIS (slower for analytical spatial joins)

Use Case 3: Hybrid Workflow (PostGIS + SedonaDB/DuckDB ⭐⭐⭐⭐)

Scenario: Production system on PostGIS, but analysts want fast ad-hoc queries and exports

Architecture:

PostGIS (production)
    ↓ nightly export
GeoParquet files
    ↓ query with SedonaDB (spatial) or DuckDB (general analytics)
Fast analytics + reports

Implementation Option A - SedonaDB for spatial analytics:

# 1. Export from PostGIS to GeoParquet (nightly)
import geopandas as gpd
from bluegreencity_ki.io.postgis_reader import PostGISReader

reader = PostGISReader.from_env()
trees = reader.read_table("trees.inventory_current_2025_10")
trees.to_parquet("exports/trees_current.parquet")

# 2. Fast spatial analytics with SedonaDB
import sedonadb

con = sedonadb.connect()

# Spatial query with optimized indexing
high_risk_by_district = con.execute("""
    SELECT 
        district,
        COUNT(*) as total_trees,
        SUM(CASE WHEN overall_drought_risk > 0.8 THEN 1 ELSE 0 END) as high_risk_count,
        ST_Union(geometry) as district_boundary
    FROM read_parquet('exports/trees_current.parquet')
    GROUP BY district
""").df()

print(high_risk_by_district)

Implementation Option B - DuckDB for general analytics:

# Fast non-spatial analytics with DuckDB
import duckdb

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

# Query GeoParquet directly (faster for aggregations without spatial operations)
high_risk_stats = con.execute("""
    SELECT 
        DATE_TRUNC('month', last_updated) as month,
        COUNT(*) as total_trees,
        SUM(CASE WHEN overall_drought_risk > 0.8 THEN 1 ELSE 0 END) as high_risk_count,
        AVG(overall_drought_risk) as avg_risk
    FROM read_parquet('exports/trees_current.parquet')
    GROUP BY month
    ORDER BY month DESC
""").df()

print(high_risk_stats)

Benefits:

Use Case 4: GeoPandas Workflow Acceleration (DuckDB ⭐⭐⭐⭐)

Scenario: Speed up existing GeoPandas-based criteria calculations

Current Code (using GeoPandas):

import geopandas as gpd

# Load trees (slow for large datasets)
trees = gpd.read_file("data/trees.gpkg")

# Aggregation (slow)
risk_by_district = trees.groupby("district").agg({
    "overall_drought_risk": ["mean", "max"],
    "tree_id": "count"
})

Optimized with DuckDB (drop-in replacement):

import duckdb
import geopandas as gpd

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

# Query GeoPackage directly with DuckDB (much faster)
risk_by_district = con.execute("""
    SELECT 
        district,
        AVG(overall_drought_risk) as mean_risk,
        MAX(overall_drought_risk) as max_risk,
        COUNT(tree_id) as tree_count
    FROM ST_Read('data/trees.gpkg')
    GROUP BY district
""").df()

# Convert to GeoDataFrame only if geometry is needed
trees_gdf = con.execute("SELECT * FROM ST_Read('data/trees.gpkg')").df()
trees_gdf = gpd.GeoDataFrame(trees_gdf, geometry='geom')

Performance Gain: 5-20x faster for aggregations and filters

Use Case 5: Machine Learning Feature Engineering (SedonaDB ⭐⭐⭐)

Scenario: Prepare spatial features for ML models to predict tree drought risk

With SedonaDB for fast spatial feature engineering:

import sedonadb
import pandas as pd
from sklearn.ensemble import RandomForestRegressor
from sklearn.model_selection import train_test_split

# Connect to SedonaDB
con = sedonadb.connect()

# Load spatial data
con.execute("CREATE TABLE trees AS SELECT * FROM read_parquet('data/trees.parquet')")
con.execute("CREATE TABLE climate AS SELECT * FROM read_parquet('data/climate.parquet')")

# Fast spatial join for feature engineering using SedonaDB's optimized spatial ops
features_df = con.execute("""
    SELECT 
        t.tree_id,
        t.age,
        t.vitality,
        t.species,
        ST_Area(t.geometry) as crown_area,
        c.temperature,
        c.precipitation,
        ST_Distance(t.geometry, c.geometry) as dist_to_climate_center,
        t.overall_drought_risk as target
    FROM trees t
    LEFT JOIN climate c ON ST_Intersects(t.geometry, c.geometry)
""").df()

# Now use standard Python ML tools (scikit-learn, etc.)
X = features_df[["age", "vitality", "crown_area", "temperature", "precipitation"]]
y = features_df["target"]

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)

# Train model with scikit-learn
rf = RandomForestRegressor(n_estimators=100, random_state=42)
rf.fit(X_train, y_train)

score = rf.score(X_test, y_test)
print(f"Model R² score: {score:.3f}")

Benefits:

Current Alternative: GeoPandas + scikit-learn (slower for spatial joins and feature engineering)

Integration Scenarios

Scenario A: PostGIS Only (Current State)

Architecture:

PostGIS ←→ GeoPandas ←→ BlueGreenCity-KI Pipeline

Pros:

Cons:

Scenario B: PostGIS + SedonaDB or DuckDB (Recommended)

Architecture:

PostGIS (production) ←→ GeoPandas ←→ BlueGreenCity-KI Pipeline
    ↓ export                              ↓ analytics
GeoParquet files    ←→ SedonaDB (spatial) or DuckDB (general) ←→ Fast queries & reports

Implementation Steps:

  1. Add SedonaDB and/or DuckDB Python packages to environment.yml
  2. Create export script: scripts/export_to_geoparquet.py
  3. Add query utilities in bluegreencity_ki/io/
  4. Update documentation with examples

Pros:

Cons:

Use When:

Scenario C: PostGIS + SedonaDB + Apache Sedona (Future Massive Scale)

Architecture:

PostGIS (production, single city)
    ↓
GeoParquet (multi-city exports)
    ↓
SedonaDB (single-machine processing for most use cases)
    ↓ IF dataset grows >100GB
Apache Sedona (distributed Spark cluster for extreme scale)

Pros:

Cons:

Use When:

Scenario D: SedonaDB-First for Development (Alternative)

Architecture:

SedonaDB (local development) ←→ GeoPandas ←→ BlueGreenCity-KI Pipeline
    ↓ for production deployment
PostGIS (multi-user production)

Pros:

Cons:

Use When:

Benefits Analysis

Benefits of Integrating DuckDB

Performance Benefits

| Operation | Current (PostGIS) | With DuckDB | Speedup | |———–|——————-|————-|———| | Load 50k trees | 2-3s | 0.2s | 10-15x | | Aggregate risk by district | 1-2s | 0.1s | 10-20x | | Export to CSV/Parquet | 5-10s | 0.5s | 10-20x | | Complex analytical query | 10-30s | 1-3s | 10x |

Developer Experience Benefits

Operational Benefits

Benefits of Integrating SedonaDB

Scale Benefits

| Dataset Size | PostGIS | SedonaDB | Benefit | |————–|———|———-|———| | <1M features | ✅ Good | ⚠️ Overkill | None | | 1M-10M features | ⚠️ Slow | ✅ Fast | 5-10x speedup | | >10M features | ❌ Very slow | ✅ Excellent | 10-100x speedup |

Advanced Capabilities

Future-Proofing

Challenges and Considerations

DuckDB Challenges

1. Multi-User Limitations

Challenge: DuckDB is single-writer (file-based) Mitigation:

2. Spatial Function Maturity

Challenge: Fewer spatial functions than PostGIS (but rapidly improving) Mitigation:

3. Raster Support

Challenge: Limited raster support compared to PostGIS Mitigation:

SedonaDB Challenges

1. Newer Technology

Challenge: Less mature than PostGIS, smaller community Mitigation:

2. Spatial Function Coverage

Challenge: Smaller spatial function library compared to PostGIS Mitigation:

3. Single-Machine Limitations

Challenge: Limited by single-machine memory (unlike distributed Apache Sedona) Mitigation:

4. Migration Path to Distributed (If Needed)

Challenge: If dataset grows extremely large, need to migrate to Apache Sedona (Spark-based) Mitigation:

Integration Challenges

1. Technology Proliferation

Challenge: Adding more technologies increases complexity Mitigation:

2. Data Synchronization

Challenge: Keeping data in sync between PostGIS, DuckDB, and SedonaDB Mitigation:

3. Testing Burden

Challenge: Need to test against multiple backends Mitigation:

Recommendations

Immediate Actions (Next 3 Months)

1. Evaluate SedonaDB vs DuckDB for Analytics ⭐ HIGH PRIORITY

Decision: Choose between SedonaDB (spatial-optimized) or DuckDB (general analytics), or use both

SedonaDB - Better for:

DuckDB - Better for:

Why: Both offer high ROI, low complexity, immediate benefits

Steps:

  1. Add SedonaDB and/or DuckDB to environment.yml ```yaml
    • sedonadb>=0.1.0 # For spatial analytics
    • duckdb>=0.9.0 # For general analytics (optional) ```
  2. Create utility modules: bluegreencity_ki/io/sedonadb_utils.py and/or duckdb_utils.py 
    import sedonadb
import geopandas as gpd
   
def create_sedona_connection(path=":memory:"):
    """Create SedonaDB connection for spatial operations"""
    con = sedonadb.connect(path)
    return con
  3. Create export script: scripts/export_to_geoparquet.py 
    from bluegreencity_ki.io.postgis_reader import PostGISReader
   
reader = PostGISReader.from_env()
trees = reader.read_table("trees.inventory_current_2025_10")
trees.to_parquet("exports/trees_current.parquet")

  4. Add example notebooks: notebooks/sedonadb_examples.ipynb and notebooks/duckdb_examples.ipynb

  5. Update documentation with examples for both

Expected Benefits:

2. Document Hybrid Workflow ⭐ MEDIUM PRIORITY

Why: Clear guidance prevents confusion and misuse

Steps:

  1. Create docs/database_usage_guide.md
  2. Document when to use PostGIS vs. SedonaDB vs. DuckDB
  3. Provide migration examples (PostGIS → GeoParquet → SedonaDB/DuckDB)
  4. Add to main README and architecture docs

Decision Matrix:

Use PostGIS when:
- Multi-user concurrent access needed
- Production data storage
- QGIS visualization required
- ACID compliance required

Use SedonaDB when:
- Fast spatial joins and intersections
- Geometric operations (buffers, unions)
- Spatial-heavy analytics
- Single-user embedded spatial database

Use DuckDB when:
- Fast non-spatial analytical queries (aggregations, filters)
- Data exploration and prototyping
- Exporting reports and statistics
- Single-user embedded analytics database

Use GeoPandas when:
- In-memory processing (<1M features)
- Spatial operations not available in databases
- Pipeline criteria calculations

3. Benchmark Current Workload ⭐ LOW PRIORITY

Why: Establish baseline before optimization

Steps:

  1. Create benchmark script: scripts/benchmark_queries.py
  2. Test common operations on PostGIS and DuckDB
  3. Document performance characteristics
  4. Identify bottlenecks and optimization opportunities

Example Benchmarks:

Short-Term Actions (3-6 Months)

1. Adopt GeoParquet as Standard Export Format

Why: Industry-standard format, fast, efficient

Implementation:

Benefits:

2. Create DuckDB Query Utilities

Implementation:

Example:

# bluegreencity_ki/io/duckdb_utils.py

def get_risk_statistics(parquet_path):
    """Get summary statistics for tree drought risk"""
    con = create_spatial_connection()
    return con.execute(f"""
        SELECT 
            COUNT(*) as total_trees,
            AVG(overall_drought_risk) as mean_risk,
            STDDEV(overall_drought_risk) as stddev_risk,
            MIN(overall_drought_risk) as min_risk,
            MAX(overall_drought_risk) as max_risk,
            PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY overall_drought_risk) as median_risk,
            PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY overall_drought_risk) as p90_risk
        FROM read_parquet('{parquet_path}')
    """).df()

Long-Term Considerations (6-12 Months)

1. Evaluate SedonaDB for Multi-City Expansion

Trigger Conditions:

Evaluation Steps:

  1. Create proof-of-concept with existing data
  2. Benchmark against PostGIS and DuckDB at scale
  3. Assess infrastructure requirements and costs
  4. Train team on Apache Spark and SedonaDB

Go/No-Go Decision Criteria:

2. Machine Learning Integration

Scenario: If ML becomes core to risk assessment

Options:

  1. GeoPandas + scikit-learn (current approach, good for <1M features)
  2. DuckDB + scikit-learn (fast feature engineering, same ML)
  3. SedonaDB + Spark ML (distributed ML at scale)

Recommendation:

Technology Selection Decision Tree

START: New geospatial task
    │
    ├─ Need multi-user concurrent access?
    │   YES → Use PostGIS
    │   NO → Continue
    │
    ├─ Dataset size > 10GB?
    │   YES → Consider SedonaDB
    │   NO → Continue
    │
    ├─ Need fast analytical queries?
    │   YES → Use DuckDB
    │   NO → Continue
    │
    ├─ Need spatial joins/operations?
    │   YES → Use PostGIS or DuckDB (depending on size)
    │   NO → Use Pandas/GeoPandas
    │
    └─ Production storage?
        YES → Use PostGIS
        NO → Use DuckDB or GeoParquet files

Implementation Roadmap

Phase 1: DuckDB Integration (Month 1-2)

Success Metrics:

Phase 2: Hybrid Workflow Optimization (Month 3-4)

Success Metrics:

Phase 3: Scale Evaluation (Month 6-12)

Success Metrics:

References

External Resources

  1. Performance Comparison:
  2. SedonaDB Documentation:
  3. DuckDB Documentation:

Internal Documentation

Appendix: Code Examples

Example 1: Query PostGIS with DuckDB (postgres_scanner)

import duckdb

# Connect to DuckDB
con = duckdb.connect()

# Install extensions
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("INSTALL postgres_scanner; LOAD postgres_scanner;")

# Attach PostGIS database
con.execute("""
    ATTACH 'host=localhost port=5432 dbname=bgcki user=bgcki_admin password=***' 
    AS postgis_db (TYPE POSTGRES, READ_ONLY)
""")

# Query PostGIS tables directly with DuckDB (gets best of both worlds!)
result = con.execute("""
    SELECT 
        district,
        COUNT(*) as tree_count,
        AVG(overall_drought_risk) as avg_risk
    FROM postgis_db.trees.inventory_current_2025_10
    WHERE overall_drought_risk > 0.5
    GROUP BY district
    ORDER BY avg_risk DESC
""").df()

print(result)

Benefit: No export step needed! Query PostGIS directly with DuckDB’s fast analytics engine.

Example 2: GeoPandas to DuckDB and Back

import geopandas as gpd
import duckdb

# Load data with GeoPandas
trees = gpd.read_file("data/trees.gpkg")

# Convert to DuckDB for fast queries
con = duckdb.connect()
con.execute("INSTALL spatial; LOAD spatial;")

# Register GeoPandas DataFrame as DuckDB table
con.register("trees_view", trees)

# Fast analytical query in DuckDB
high_risk = con.execute("""
    SELECT * FROM trees_view
    WHERE overall_drought_risk > 0.8
    ORDER BY overall_drought_risk DESC
""").df()

# Convert back to GeoPandas for spatial operations
high_risk_gdf = gpd.GeoDataFrame(high_risk, geometry='geometry', crs=trees.crs)

# Plot with GeoPandas
high_risk_gdf.plot(column='overall_drought_risk', legend=True)

Benefit: Seamless integration - use DuckDB for speed, GeoPandas for spatial ops.

Example 3: SedonaDB Distributed Spatial Join

from sedona.spark import *
from pyspark.sql import SparkSession

# Initialize Spark with Sedona
spark = SparkSession.builder\
    .appName("BlueGreenCity-KI Spatial Join")\
    .config("spark.serializer", "org.apache.spark.serializer.KryoSerializer")\
    .config("spark.kryo.registrator", "org.apache.sedona.core.serde.SedonaKryoRegistrator")\
    .getOrCreate()

SedonaRegistrator.registerAll(spark)

# Load large datasets (distributed)
trees_df = spark.read.format("geoparquet")\
    .load("data/all_cities/trees/*.parquet")
trees_df.createOrReplaceTempView("trees")

osm_df = spark.read.format("geoparquet")\
    .load("data/all_cities/osm_highways/*.parquet")
osm_df.createOrReplaceTempView("highways")

# Distributed spatial join (handles millions of features)
result = spark.sql("""
    SELECT 
        t.city,
        t.tree_id,
        t.geometry AS tree_geom,
        h.highway_type,
        ST_Distance(t.geometry, h.geometry) AS distance_to_highway
    FROM trees t
    LEFT JOIN highways h 
    ON ST_DWithin(t.geometry, h.geometry, 50.0)
""")

# Write partitioned results
result.write\
    .partitionBy("city")\
    .format("geoparquet")\
    .mode("overwrite")\
    .save("output/trees_with_highways/")

print(f"Processed {result.count()} tree-highway relationships")

Benefit: Spatial joins that would take hours in PostGIS complete in minutes with SedonaDB at large scale.

Document Version: 1.0
Last Updated: 2025-11-15
Maintained By: @wanderfunke
Review Cycle: Quarterly or when scaling requirements change