valleys.sh

#!/usr/bin/env bash

wget https://geonames.usgs.gov/docs/stategaz/AllStates.zip
unzip AllStates.zip
wget https://geonames.usgs.gov/docs/stategaz/AllNames.zip
unzip AllNames.zip

sed $'1s/\xef\xbb\xbf//' AllNames_20210825.txt | csvformat -d '|' > allnames.csv
csvgrep -c FEATURE_NAME_OFFICIAL -m Y -i allnames.csv | csvcut -c 'FEATURE_ID,FEATURE_NAME,CITATION' | sed '1s/FEATURE_NAME/FEATURE_NAME_UNOFFICIAL/' > allnames_unofficial.csv

mkdir valleys
cd valleys/

# GNIS knows of no valleys in DE MH UM.
states=(AK AL AR AS AZ CA CO CT DC FL FM GA GU HI IA ID IL IN KS KY LA MA MD ME MI MN MO MP MS MT NC ND NE NH NJ NM NV NY OH OK OR PA PR PW RI SC SD TN TX UT VA VI VT WA WI WV WY)
for state in ${states[*]}; do
    echo "BUILDING ${state} CHALLENGE..."
    sed $'1s/\xef\xbb\xbf//' ../AllStates/${state}_Features_20210825.txt | csvgrep -d '|' --column=FEATURE_CLASS -m Valley > valleys_official_${state}.csv
    csvjoin --left -I -c FEATURE_ID valleys_official_${state}.csv ../allnames_unofficial.csv > valleys_alt_${state}.csv
    csvsql -I --query "SELECT FEATURE_ID, MIN(FEATURE_NAME) AS FEATURE_NAME, GROUP_CONCAT(FEATURE_NAME_UNOFFICIAL, \"<SEMICOLON>\") AS OTHER_FEATURE_NAMES, MIN(STATE_NUMERIC) AS STATE_NUMERIC, MIN(COUNTY_NUMERIC) AS COUNTY_NUMERIC, MIN(PRIM_LAT_DEC) AS PRIM_LAT_DEC, MIN(PRIM_LONG_DEC) AS PRIM_LONG_DEC, MIN(SOURCE_LAT_DEC) AS SOURCE_LAT_DEC, MIN(SOURCE_LONG_DEC) AS SOURCE_LONG_DEC, MIN(DATE_CREATED) AS DATE_CREATED, MIN(DATE_EDITED) AS DATE_EDITED FROM valleys_alt_${state} GROUP BY FEATURE_ID" valleys_alt_${state}.csv | sed 's/<SEMICOLON>/;/g' > valleys_${state}.csv
    csvjson -I valleys_${state}.csv | jq '{type: "FeatureCollection", features: map({type: "Feature", geometry: {type: "LineString", coordinates: [[(.["SOURCE_LONG_DEC"] | tonumber), (.["SOURCE_LAT_DEC"] | tonumber)], [(.["PRIM_LONG_DEC"] | tonumber), (.["PRIM_LAT_DEC"] | tonumber)]]}, id: (.["FEATURE_ID"] | tonumber), properties: {"alt_name": (.["OTHER_FEATURE_NAMES"] // ""), "gnis:county_id": .["COUNTY_NUMERIC"], "gnis:created": .["DATE_CREATED"], "gnis:edited": .["DATE_EDITED"], "gnis:feature_id": .["FEATURE_ID"], "gnis:state_id": .["STATE_NUMERIC"], name: .["FEATURE_NAME"], natural: "valley"}})}' > valleys_${state}.geojson
done