Skip to article frontmatterSkip to article content
Site not loading correctly?

This may be due to an incorrect BASE_URL configuration. See the MyST Documentation for reference.

High-Performance Geospatial Analytics with DuckDB

image image

Introduction

What Makes DuckDB Special for Geospatial Work?

Learning Objectives

Installation and Setup

Installing Required Packages

Library Import and Configuration

Understanding DuckDB Connections

Installing and Loading Extensions

SQL Basics for Spatial Analysis

Understanding SQL for Geospatial Work

Sample Datasets

Reading Data Directly from URLs

Creating Persistent Tables

Viewing Your Data

Essential SQL Commands

Selecting Data

Choosing Specific Columns

Finding Unique Values

Counting and Aggregation

Filtering and Sorting Data

Filtering with WHERE Clauses

Sorting Your Results

Grouping and Aggregating Data

Python API Integration

Understanding Result Formats

Seamless DataFrame Integration

Result Conversion and Export

Persistent Storage

Data Import

Understanding Data Formats in Geospatial Work

Downloading Sample Data

Working with CSV Files

JSON Files

DataFrames

Parquet Files

Spatial Data Formats

Data Export

Sample Spatial Data Setup

Export to DataFrames

Export to Files

Export to JSON

Export to Excel

Export to Parquet

Export Spatial Formats

Working with Geometries

Understanding Spatial Data Types

Sample Data Setup

Creating and Understanding Geometries

Working with Points

Working with LineStrings

Working with Polygons

Spatial Relationships

Understanding Spatial Predicates

Working with Real Spatial Data

Testing Spatial Equality

Point-in-Polygon Analysis

Proximity Analysis with Distance

Spatial Joins

Understanding Spatial vs. Regular Joins

Exploring Our Data

Your First Spatial Join

Advanced Spatial Analysis

Distance-Based Analysis

Advanced Multi-Table Joins

Large-Scale Data Analysis

Analyzing the National Wetlands Inventory

Scaling Up: Nationwide Wetland Analysis

Count the Total Number of Wetlands

Count Wetlands by State

Mapping Wetland Counts by State

Wetland Distribution Charts

Pie Chart of Wetlands by State

Bar Chart of Wetlands by State

Wetland Area Analysis

Total Wetland Area in the U.S.

Wetland Area by State

Pie Chart of Wetland Area by State

Bar Chart of Wetland Area by State

Key Takeaways

Fundamental Concepts You’ve Learned

Why This Approach is Powerful

Building Your Spatial Analysis Skills

Exercises

Exercise 1: Creating Tables

Exercise 2: Column Filtering

Exercise 3: Row Filtering

Exercise 4: Sorting Results

Exercise 5: Unique Values

Exercise 6: Counting Rows

Exercise 7: Aggregating Data

Exercise 8: Joining Tables

Exercise 9: String Manipulation

Exercise 10: Filtering with Multiple Conditions

Exercise 11: Basic Setup and Data Loading

Exercise 12: Spatial Relationships Analysis

Exercise 13: Advanced Spatial Joins

Exercise 14: Data Import and Export

Exercise 15: Large-Scale Analysis