Spatial Queries and Relationships

Contents

7. Spatial Queries and Relationships#

7.1. Introduction#

7.2. Learning Objectives#

7.3. Sample Datasets#

7.4. Installation and Setup#

7.4.1. Library Import and Configuration#

7.4.2. Downloading the Sample Database#

7.5. Connecting to DuckDB and Loading Extensions#

7.5.1. Installing and Loading the Spatial Extension#

7.5.2. Exploring the Database Structure#

7.6. Understanding Spatial Relationships#

7.7. Testing Geometric Identity#

7.7.1. Finding an Exact Match#

7.7.2. Practical Considerations for ST_Equals#

7.8. Topological Relationships#

7.8.1. Testing Spatial Intersection#

7.8.2. Testing Spatial Separation#

7.8.3. Testing Containment#

7.8.4. When to Use ST_Contains vs ST_Within#

7.8.5. Important Distinction from ST_Intersects#

7.8.6. Testing Boundary Contact#

7.8.7. Understanding Boundary vs Interior Contact#

7.8.8. When to Use ST_Touches#

7.8.9. ST_Touches vs ST_Intersects#

7.8.10. Detecting Linear Crossings#

7.8.11. Testing Partial Overlap#

7.8.12. Practical Applications with NYC Data#

7.9. Distance-Based Relationships#

7.9.1. Understanding Distance Calculation#

7.9.2. Basic Distance Example#

7.9.3. Distance with Real Data#

7.9.4. Critical: Coordinate System Units Matter#

7.9.5. When to Use ST_Distance#

7.10. Filtering by Proximity Threshold#

7.10.1. Finding Features Within a Distance#

7.10.2. When to Use ST_DWithin#

7.11. Nearest Neighbor Queries#

7.12. Key Takeaways#

7.13. Exercises#

7.13.1. Exercise 1: Testing Geometric Equality#

7.13.2. Exercise 2: Point-in-Polygon Analysis#

7.13.3. Exercise 3: Containment vs. Intersection#

7.13.4. Exercise 4: Distance Calculations#

7.13.5. Exercise 5: Proximity Filtering#

7.13.6. Exercise 6: Combining Multiple Spatial Criteria#

Contents