Essential SQL for Spatial Analysis

Contents

2. Essential SQL for Spatial Analysis#

2.1. Introduction#

2.2. Learning Objectives#

2.3. Sample Datasets#

2.4. Setting Up the Environment#

2.5. Connecting To DuckDB#

2.6. Install Extensions#

2.7. Reading CSV Files from URLs#

2.8. Creating Tables for Better Performance#

2.9. The SQL SELECT Statement#

2.9.1. Limiting Results with LIMIT#

2.9.2. Selecting Specific Columns#

2.9.3. Finding Unique Values with DISTINCT#

2.9.4. Basic Aggregate Functions#

2.9.4.1. Counting Features#

2.9.4.2. Counting Unique Categories#

2.9.4.3. Finding Extremes#

2.9.4.4. Calculating Totals#

2.9.4.5. Computing Averages#

2.9.5. Sorting Results with ORDER BY#

2.9.5.1. Basic Sorting#

2.9.5.2. Multi-Level Sorting#

2.9.5.3. Spatial Sorting Applications#

2.9.6. Calculated Columns and Expressions#

2.9.6.1. Basic Calculations#

2.9.6.2. Geographic Calculations#

2.10. Filtering Data with the WHERE Clause#

2.11. Pattern Matching with LIKE#

2.12. The IN Operator#

2.13. The BETWEEN Operator#

2.14. Combining Data with SQL Joins#

2.14.1. SQL Inner Join#

2.14.2. SQL Left Join#

2.14.3. SQL Right Join#

2.14.4. SQL Full Join#

2.14.5. SQL Union#

2.15. Query Plans and Performance#

2.16. Aggregating Data for Summary Statistics#

2.16.1. Understanding GROUP BY#

2.16.2. Filtering Aggregated Results with HAVING#

2.17. Conditional Statements#

2.18. Saving Results#

2.19. Advanced SQL Features for Spatial Analysis#

2.19.1. Window Functions#

2.19.1.1. Ranking Cities by Population#

2.19.2. Common Table Expressions (CTEs)#

2.19.2.1. Analyzing Urban Hierarchies#

2.19.3. Subqueries for Spatial Comparisons#

2.19.3.1. Finding Cities Above Country Average#

2.19.4. String Functions for Spatial Data#

2.19.4.1. Cleaning and Standardizing Place Names#

2.19.5. Date and Time Functions#

2.20. SQL Comments and Documentation#

2.20.1. Single-Line Comments#

2.20.2. Multi-Line Comments#

2.21. Key Takeaways#

2.22. Exercises#

2.22.1. Exercise 1: Loading Remote Data and Creating Tables#

2.22.2. Exercise 2: Basic Column Selection#

2.22.3. Exercise 3: Filtering Rows with WHERE#

2.22.4. Exercise 4: Sorting Results with ORDER BY#

2.22.5. Exercise 5: Finding Unique Values with DISTINCT#

2.22.6. Exercise 6: Counting and Grouping Data#

2.22.7. Exercise 7: Sorting Aggregated Results#

2.22.9. Exercise 9: Pattern Matching with LIKE#

2.22.10. Exercise 10: Multiple Conditions with AND#

Contents