bluegreencity-ki

ADR-002: Spatial Database Technology Evaluation (SedonaDB and DuckDB)

Status

Accepted (2025-11) - Recommending evaluation of SedonaDB (spatial-optimized) and/or DuckDB (general analytics) as complementary embedded analytics databases

Current state (Oct 2026): not implemented. No DuckDB/SedonaDB component was ever integrated (no dependency, no module), and the planned review (2026-05-15) did not change anything. PostGIS remains the sole database. This ADR is retained as a dated evaluation record, not as an active decision.

Context

BlueGreenCity-KI currently uses PostGIS as the primary geodatabase for multi-user access and persistent storage, with GeoPandas for in-memory processing. As the project scales and analytical demands increase, we evaluated two additional embedded database technologies to complement our existing stack:

SedonaDB - Lightweight embedded spatial database (single-machine, no Spark required)
DuckDB - Embedded analytical database with spatial extension

Important Clarification: SedonaDB is a standalone lightweight spatial database that runs on single computers. It does NOT require Apache Spark infrastructure. Apache Sedona is a separate distributed framework built on Spark that SedonaDB can optionally migrate to if extreme scale (>100GB) is needed.

Current Challenges

Performance Bottlenecks:

PostGIS analytical queries (aggregations, groupings) can be slow for large result sets
GeoPandas limited by single-machine memory for large datasets
Export operations to GeoPackage/CSV are time-consuming
Repetitive queries hit production database repeatedly

Developer Experience:

New developers need PostGIS setup before starting
Data exploration requires database connection
Analytical workflows are slower than necessary
Sharing intermediate results requires database dumps or large files

Future Scale Concerns:

Expanding to multiple cities (10+ cities, 500k+ trees)
High-resolution climate models (100GB+ datasets)
Machine learning workflows requiring distributed processing
Historical time-series analysis (10+ years of data)

Current Scale

Dataset Sizes:

Tree inventory: ~51,000 features (current single city)
OSM highways: ~10,000-50,000 features
Climate rasters: 1km-50m resolution (1,000-500,000 cells)
Total data: <1GB currently

Projected Growth:

Multi-city expansion: 5-10x increase
Historical analysis: 10 years of data
High-res climate models: 10-100x more cells
ML scenarios: 1,000+ simulation runs

Forces at Play

Technical Forces:

Need for faster analytical queries (aggregations, statistics)
Single-machine memory limitations with GeoPandas
Distributed processing requirements for large-scale scenarios
Integration with existing PostGIS infrastructure

Business Forces:

Researcher productivity and development velocity
Multi-city expansion roadmap
Machine learning integration for predictive modeling
Cost of infrastructure and maintenance

Team Forces:

Team familiarity with SQL and Python
Limited DevOps resources for complex infrastructure
Preference for open-source, well-supported technologies
Need for accessible, well-documented tools

Technology Evaluation

We evaluated three scenarios:

Status Quo: PostGIS + GeoPandas only
SedonaDB Integration: Add SedonaDB for spatial analytics (embedded, no Spark)
DuckDB Integration: Add DuckDB for general analytical workloads

Performance Benchmark Summary (from forrest.nyc analysis):

Operation	PostGIS	DuckDB	SedonaDB	Best For
Aggregations	22s	3s	8s	DuckDB (7x faster)
Load 1M points	45s	8s	12s	DuckDB (5x faster)
Spatial joins (1M)	180s	42s	35s	SedonaDB (5x faster)
Range queries	5s	2s	4s	DuckDB (2.5x faster)
Complex spatial joins	320s	98s	45s	SedonaDB (7x faster)

Key Insights:

DuckDB excels at general analytical queries and aggregations
SedonaDB excels at spatial joins and geometric operations (single-machine, embedded)
PostGIS best for transactional workloads and multi-user production
SedonaDB can optionally scale to Apache Sedona (Spark-based) for extreme datasets >100GB

Decision

We will adopt a hybrid multi-database architecture with PostGIS as the foundation and SedonaDB/DuckDB as complementary embedded analytical engines:

Short-Term (Immediate): Integrate SedonaDB and/or DuckDB

Decision: Evaluate both embedded databases - choose based on workload

SedonaDB - For spatial-heavy analytics:

Fast spatial joins and intersections
Geometric operations (buffers, unions, contains)
Spatial-heavy analytical workflows

DuckDB - For general analytics:

Fast aggregations, statistics, filtering
General analytical queries with light spatial needs
Larger ecosystem and community

Implementation:

Add SedonaDB and/or DuckDB to environment.yml dependencies
Create utility modules: bluegreencity_ki/io/sedonadb_utils.py and/or duckdb_utils.py
Implement PostGIS → GeoParquet export pipeline
Document hybrid workflow (PostGIS for production, SedonaDB/DuckDB for analytics)
Provide example notebooks and query templates

Use Cases:

Fast spatial joins and analysis (SedonaDB)
Fast aggregations and statistics (DuckDB)
Data exploration and prototyping
Report generation and exports
Developer onboarding (no PostGIS setup needed)

Architecture:

PostGIS (production)
    ↓ export to GeoParquet
SedonaDB (spatial) or DuckDB (general) ←→ Fast analytics
    ↓ results
GeoPandas ←→ BlueGreenCity-KI Pipeline

Long-Term: Apache Sedona (If Needed)

What: Only consider distributed Apache Sedona if dataset grows to extreme scale

Trigger Conditions:

Dataset size >100GB (not >10GB)
Single-machine SedonaDB insufficient
Processing 50+ cities simultaneously
Need for distributed spatial joins (millions of features)
Complex spatial machine learning workflows

Evaluation Criteria:

Benchmark performance against PostGIS/DuckDB at target scale
Assess infrastructure requirements and costs
Evaluate team training needs
Calculate ROI vs. operational complexity

Decision Point: Re-evaluate in 6-12 months based on actual scale and requirements

Long-Term: Database Usage Guidelines

Use PostGIS when:

Multi-user concurrent access needed
Production data storage (ACID compliance)
QGIS visualization required
Long-term persistence and versioning
Transaction support needed

Use DuckDB when:

Fast analytical queries (aggregations, statistics)
Data exploration and prototyping
Single-user development workflow
Exporting reports and intermediate results
Querying PostGIS without loading full tables

Use SedonaDB when (future):

Dataset size >10GB
Distributed processing needed (multiple cities)
Large-scale spatial joins (millions of features)
Machine learning at scale

Use GeoPandas when:

In-memory processing (<1M features)
Spatial operations not available in SQL
Pipeline criteria calculations
Integration with Python ecosystem

Consequences

Positive Consequences

Performance Improvements:

✅ 10-100x faster analytical queries with DuckDB vs. PostGIS
✅ Reduced load on production database (offload analytics to DuckDB)
✅ Faster exports to GeoParquet (10x faster than GeoPackage)
✅ Instant data exploration without hitting production database

Developer Experience:

✅ Zero-configuration development - DuckDB is embedded, no server needed
✅ Faster onboarding - new developers can work without PostGIS setup
✅ Better iteration speed - instant feedback in Jupyter notebooks
✅ Easier collaboration - share GeoParquet files directly

Scalability & Future-Proofing:

✅ Ready for scale - clear path to SedonaDB if needed
✅ GeoParquet adoption - industry-standard format for geospatial data
✅ Flexible architecture - each tool used for its strengths
✅ Cloud-ready - GeoParquet works with S3, Azure Blob

Operational Benefits:

✅ Reduced operational complexity - DuckDB requires no server maintenance
✅ Lower costs - no additional database servers needed
✅ Easier testing - embedded database perfect for CI/CD
✅ Version control friendly - can commit small .duckdb files to git

Negative Consequences

Complexity:

⚠️ Multiple technologies to maintain - adds DuckDB to the stack
⚠️ Data synchronization - need to export PostGIS → GeoParquet
⚠️ Learning curve - team needs to learn DuckDB syntax and patterns
⚠️ Testing burden - need to validate against both PostGIS and DuckDB

Limitations:

⚠️ DuckDB multi-user constraints - single-writer limitation
⚠️ Spatial functions - fewer spatial functions than PostGIS (but improving)
⚠️ Raster support - limited compared to PostGIS (use Rasterio instead)
⚠️ Production use - not suitable for centralized multi-user production database

Migration Risks:

⚠️ Behavioral differences - subtle SQL differences between databases
⚠️ Vendor lock-in mitigation - need to abstract database-specific code
⚠️ Documentation overhead - need to document when to use each technology

Neutral Consequences

Architectural Changes:

📊 Hybrid architecture - multiple databases for different purposes
📊 GeoParquet as interchange format - standard for data exchange
📊 Export pipelines - automated PostGIS → GeoParquet exports

Operational Changes:

📊 Monitoring - need to track DuckDB performance and usage
📊 Backup strategy - GeoParquet files as analytical snapshots
📊 Documentation updates - extensive new documentation required

Alternatives Considered

Alternative 1: PostGIS Only (Status Quo)

Description: Continue using PostGIS as sole database, optimize queries and indexes

Pros:

✅ Simple, proven architecture
✅ No new technologies to learn
✅ Single source of truth
✅ Well-understood by team

Cons:

❌ Slower analytical queries remain a bottleneck
❌ GeoPandas memory limitations persist
❌ No clear path to scale for multi-city expansion
❌ Developer experience doesn’t improve

Reason for rejection:

Performance bottlenecks will worsen as dataset grows
Doesn’t address scalability concerns for multi-city expansion
Misses opportunity for significant performance gains with minimal cost

Alternative 2: DuckDB Only (Replace PostGIS)

Description: Migrate entirely to DuckDB, eliminate PostGIS

Pros:

✅ Simpler stack (one database instead of two)
✅ Fast analytical queries
✅ No server maintenance
✅ Easy deployment

Cons:

❌ Loses multi-user concurrent access
❌ No centralized production database
❌ QGIS integration more complex
❌ Requires migration of all existing data
❌ File-based approach less suitable for production

Reason for rejection:

Multi-user access is critical for team collaboration
PostGIS provides valuable features (QGIS, ACID compliance) that DuckDB doesn’t
Migration cost and risk not justified by benefits

Alternative 3: Apache Sedona Only (Distributed Spark-Based)

Description: Skip SedonaDB and go directly to distributed Apache Sedona (Spark-based) for all spatial processing

Pros:

✅ Excellent performance at extreme scale (>100GB)
✅ Distributed processing capability
✅ ML integration via Spark

Cons:

❌ Massive overkill for current dataset size (<1GB)
❌ Requires Apache Spark infrastructure (high complexity)
❌ Steep learning curve (Spark concepts: RDD, DataFrame, executors)
❌ High operational overhead (cluster management, monitoring)
❌ Expensive infrastructure costs
❌ No multi-user transactional support

Reason for rejection:

Current dataset size doesn’t justify Spark cluster infrastructure
SedonaDB (single-machine) is sufficient and can scale to Apache Sedona later if needed
Operational complexity far exceeds benefits at current scale
Can adopt Apache Sedona when scale increases 100x (>100GB)

Alternative 4: Cloud Data Warehouse (BigQuery GIS, AWS Redshift Spatial)

Description: Use managed cloud data warehouse with spatial extensions

Pros:

✅ Managed infrastructure (no maintenance)
✅ Scales automatically
✅ Good performance for large datasets

Cons:

❌ Vendor lock-in
❌ Ongoing costs (query-based billing)
❌ Data sovereignty concerns (academic/municipal data)
❌ Network latency for local development
❌ Requires cloud infrastructure expertise
❌ Less suitable for on-premises workstation setup

Reason for rejection:

Project currently runs on-premises via Tailscale
Data sovereignty important for municipal partnership
Cost model doesn’t fit academic research budget
Cloud dependency conflicts with offline development scenarios

Alternative 5: DuckDB + SedonaDB (No PostGIS)

Description: Replace PostGIS entirely with DuckDB (analytics) + SedonaDB (scale)

Pros:

✅ Best performance for analytics and scale
✅ Modern, cloud-native architecture
✅ No database server to maintain

Cons:

❌ Loses multi-user transactional database
❌ QGIS integration more complex
❌ Very high complexity (two new technologies)
❌ No clear production multi-user solution
❌ Requires migration of all existing workflows

Reason for rejection:

Complexity too high (adopting two new technologies simultaneously)
Loses valuable PostGIS features for production
Not justified by current scale and requirements

Implementation Plan

Phase 1: DuckDB Integration (Weeks 1-4)

Week 1-2: Setup and Foundation

Add duckdb>=0.9.0 to environment.yml
Create bluegreencity_ki/io/duckdb_utils.py module
Implement create_spatial_connection() helper
Test DuckDB spatial extension installation

Week 3-4: Export Pipeline and Documentation

Create scripts/export_to_geoparquet.py script
Implement automated PostGIS → GeoParquet export
Create example notebook: notebooks/duckdb_examples.ipynb
Document hybrid workflow in docs/database_usage_guide.md
Add decision matrix to documentation

Phase 2: Integration and Testing (Weeks 5-8)

Week 5-6: Query Templates and Utilities

Create common query templates (risk statistics, aggregations)
Implement get_risk_statistics() utility function
Add DuckDB integration to report generation
Test postgres_scanner for direct PostGIS querying

Week 7-8: Benchmarking and Optimization

Create benchmark script: scripts/benchmark_queries.py
Measure performance gains (PostGIS vs. DuckDB)
Document performance characteristics
Optimize frequently-used queries

Phase 3: Team Adoption (Weeks 9-12)

Week 9-10: Training and Documentation

Update main README with DuckDB examples
Create tutorial for data exploration with DuckDB
Present DuckDB integration to team
Gather feedback and iterate

Week 11-12: Production Integration

Schedule nightly GeoParquet exports
Update CI/CD to test both PostGIS and DuckDB paths
Monitor DuckDB adoption and usage patterns
Collect performance metrics

Phase 4: Long-Term Monitoring (Months 3-12)

Track dataset growth and performance trends
Monitor query patterns and bottlenecks
Evaluate need for SedonaDB at scale milestones
Gather team feedback on hybrid workflow
Update documentation based on lessons learned

Success Metrics

Performance:

✅ 10x faster analytical queries (baseline: PostGIS aggregations)
✅ Reduced PostGIS load (measure query frequency)
✅ Faster data exports (measure time to generate reports)

Developer Experience:

✅ Positive team feedback (survey after 3 months)
✅ Faster onboarding for new developers
✅ Increased use of data exploration workflows

Adoption:

✅ DuckDB used in at least 50% of analytical workflows
✅ GeoParquet adoption as standard export format
✅ Reduced pressure on production PostGIS database

References

Performance Analysis

SedonaDB vs DuckDB vs PostGIS Performance Comparison

SedonaDB Documentation

DuckDB Documentation

Internal Documentation

Date: 2025-11-15
Author: @wanderfunke (with GitHub Copilot assistance)
Reviewers: Pending team review
Status: Accepted for DuckDB integration, SedonaDB evaluation deferred — never executed (see Status section, Oct 2026 note)
Next Review: 2026-05-15 (6 months) or when dataset size >10GB — passed without action