High-Performance Geospatial Analytics with DuckDB

Contents

25. High-Performance Geospatial Analytics with DuckDB#

25.1. Introduction#

25.1.1. What Makes DuckDB Special for Geospatial Work?#

25.2. Learning Objectives#

25.3. Installation and Setup#

25.3.1. Installing Required Packages#

25.3.2. Library Import and Configuration#

25.3.3. Understanding DuckDB Connections#

25.3.4. Installing and Loading Extensions#

25.4. SQL Basics for Spatial Analysis#

25.4.1. Understanding SQL for Geospatial Work#

25.4.2. Sample Datasets#

25.4.3. Reading Data Directly from URLs#

25.4.4. Creating Persistent Tables#

25.4.5. Viewing Your Data#

25.4.6. Essential SQL Commands#

25.4.6.1. Selecting Data#

25.4.6.2. Choosing Specific Columns#

25.4.6.3. Finding Unique Values#

25.4.6.4. Counting and Aggregation#

25.4.7. Filtering and Sorting Data#

25.4.7.1. Filtering with WHERE Clauses#

25.4.7.2. Sorting Your Results#

25.4.7.3. Grouping and Aggregating Data#

25.5. Python API Integration#

25.5.1. Understanding Result Formats#

25.5.2. Seamless DataFrame Integration#

25.5.3. Result Conversion and Export#

25.5.4. Persistent Storage#

25.6. Data Import#

25.6.1. Understanding Data Formats in Geospatial Work#

25.6.2. Downloading Sample Data#

25.6.3. Working with CSV Files#

25.6.4. JSON Files#

25.6.5. DataFrames#

25.6.6. Parquet Files#

25.6.7. Spatial Data Formats#

25.7. Data Export#

25.7.1. Sample Spatial Data Setup#

25.7.2. Export to DataFrames#

25.7.3. Export to Files#

25.7.4. Export to JSON#

25.7.5. Export to Excel#

25.7.6. Export to Parquet#

25.7.7. Export Spatial Formats#

25.8. Working with Geometries#

25.8.1. Understanding Spatial Data Types#

25.8.2. Sample Data Setup#

25.8.3. Creating and Understanding Geometries#

25.8.4. Working with Points#

25.8.5. Working with LineStrings#

25.8.6. Working with Polygons#

25.9. Spatial Relationships#

25.9.1. Understanding Spatial Predicates#

25.9.2. Working with Real Spatial Data#

25.9.3. Testing Spatial Equality#

25.9.4. Point-in-Polygon Analysis#

25.9.5. Proximity Analysis with Distance#

25.10. Spatial Joins#

25.10.1. Understanding Spatial vs. Regular Joins#

25.10.2. Exploring Our Data#

25.10.3. Your First Spatial Join#

25.10.4. Advanced Spatial Analysis#

25.10.5. Distance-Based Analysis#

25.10.6. Advanced Multi-Table Joins#

25.11. Large-Scale Data Analysis#

25.11.1. Analyzing the National Wetlands Inventory#

25.11.2. Scaling Up: Nationwide Wetland Analysis#

25.11.2.1. Count the Total Number of Wetlands#

25.11.2.2. Count Wetlands by State#

25.11.3. Mapping Wetland Counts by State#

25.11.4. Wetland Distribution Charts#

25.11.4.1. Pie Chart of Wetlands by State#

25.11.4.2. Bar Chart of Wetlands by State#

25.11.5. Wetland Area Analysis#

25.11.5.1. Total Wetland Area in the U.S.#

25.11.5.2. Wetland Area by State#

25.11.5.3. Pie Chart of Wetland Area by State#

25.11.5.4. Bar Chart of Wetland Area by State#

25.12. Key Takeaways#

25.12.1. Fundamental Concepts You’ve Learned#

25.12.2. Why This Approach is Powerful#

25.12.3. Building Your Spatial Analysis Skills#

25.13. Exercises#

25.13.1. Exercise 1: Creating Tables#

25.13.2. Exercise 2: Column Filtering#

25.13.3. Exercise 3: Row Filtering#

25.13.4. Exercise 4: Sorting Results#

25.13.5. Exercise 5: Unique Values#

25.13.6. Exercise 6: Counting Rows#

25.13.7. Exercise 7: Aggregating Data#

25.13.8. Exercise 8: Joining Tables#

25.13.9. Exercise 9: String Manipulation#

25.13.10. Exercise 10: Filtering with Multiple Conditions#

25.13.11. Exercise 11: Basic Setup and Data Loading#

25.13.12. Exercise 12: Spatial Relationships Analysis#

25.13.13. Exercise 13: Advanced Spatial Joins#

25.13.14. Exercise 14: Data Import and Export#

25.13.15. Exercise 15: Large-Scale Analysis#

Contents