1INSTALL spatial; LOAD spatial;
2INSTALL httpfs; LOAD httpfs;
3SET s3_endpoint='s3-west.nrp-nautilus.io';
4SET s3_url_style='path';
5SET s3_use_ssl=false;
6
7-- Top states by dam removals
8SELECT State, COUNT(*) AS removals
9FROM read_parquet('s3://public-rivers/american-rivers/dam-removal.parquet')
10GROUP BY State ORDER BY removals DESC LIMIT 10;
11
12-- Dams removed for ecology since 2000
13SELECT Dam_Name, River, State, Year_Removed, Miles_Restored_US
14FROM read_parquet('s3://public-rivers/american-rivers/dam-removal.parquet')
15WHERE Reason_for_removal ILIKE '%ecology%'
16 AND TRY_CAST(Year_Removed AS INTEGER) >= 2000
17ORDER BY TRY_CAST(Year_Removed AS INTEGER) DESC
18LIMIT 20;