DB Quacks SQL Tutorial · Chapter 7
Finding Nemo: A Spatial Odyssey
Deep-sea explorer duck surfaces in scuba gear. Dude! Im Nemos cousin, mapping the data ocean floor! Duck locations are latitude/longitude coordinates stored in our duck_locations.csv file. Find which ducks live within 10km of Central Pond using spatial extension!
Your task
Install spatial extension and find ducks within 10km of Central Pond using the 'data/duck_locations.csv' file. The pond location coordinates are provided. You will be performing geographical distance calculations.
Hint
You need to install an extension first, then use spatial functions to calculate distances between geographical points.
Expected result
A table showing duck names within 10km of Central Pond (pond_id: 1).
Tables
duck_diaries (10 rows)
| column | type |
|---|---|
diary_id | BIGINT |
duck_id | BIGINT |
entry_date | DATE |
diary_text | VARCHAR |
mood | VARCHAR |
duck_surveys (5000 rows)
| column | type |
|---|---|
survey_id | BIGINT |
duck_id | BIGINT |
age | BIGINT |
favorite_food | VARCHAR |
favorite_pond | BIGINT |
swimming_rating | BIGINT |
migration_preference | VARCHAR |
social_behavior | VARCHAR |
health_status | VARCHAR |
activity_level | VARCHAR |
survey_date | DATE |
survey_location | BIGINT |
ducks (15 rows)
| column | type |
|---|---|
id | BIGINT |
name | VARCHAR |
age | BIGINT |
species | VARCHAR |
birth_date | DATE |
arrival_date | DATE |
ponds (3 rows)
| column | type |
|---|---|
pond_id | BIGINT |
pond_name | VARCHAR |
location_lat | DOUBLE |
location_lng | DOUBLE |
capacity | BIGINT |
features | VARCHAR[] |
amenities | VARCHAR[] |
Files
data/duck_locations.csv (15 rows)
| column | type |
|---|---|
duck_id | BIGINT |
current_date | DATE |
duck_latitude | DOUBLE |
duck_longitude | DOUBLE |
last_seen | TIMESTAMP |
Database: https://dbquacks.com/data/databases/tutorial.duckdb
This page as markdown: https://dbquacks.com/tutorial/36.md