Last active
October 26, 2020 21:15
-
-
Save nautilytics/5dcccd8514ed243372f083e3110a2fa9 to your computer and use it in GitHub Desktop.
An automated approach to retrieving Census Place shape-files for every state and inserting into a Postgres database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
cd ~/Downloads | |
# Go through each state FIPs code and get their places SHP file | |
StateFIPS=( | |
"01" "02" "04" "05" "06" "08" "09" "10" | |
"11" "12" "13" "15" "16" "17" "18" "19" | |
"20" "21" "22" "23" "24" "25" "26" "27" | |
"28" "29" "30" "31" "32" "33" "34" "35" | |
"36" "37" "38" "39" "40" "41" "42" "44" | |
"45" "46" "47" "48" "49" "50" "51" "53" | |
"54" "55" "56" | |
) | |
counter=1 | |
for f in ${StateFIPS[@]} | |
do | |
curl https://www2.census.gov/geo/tiger/TIGER2019/PLACE/tl_2019_${f}_place.zip --output tl_2019_${f}_place.zip | |
mkdir tl_2019_${f}_place | |
unzip tl_2019_${f}_place.zip -d tl_2019_${f}_place | |
if [ $counter == 1 ]; then | |
shp2pgsql -d -I tl_2019_${f}_place/tl_2019_${f}_place.shp tl_2019_places | psql -d gis_db | |
else | |
shp2pgsql -a tl_2019_${f}_place/tl_2019_${f}_place.shp tl_2019_places | psql -d gis_db | |
fi | |
cd ~/Downloads | |
rm tl_2019_${f}_place.zip | |
rm -rf tl_2019_${f}_place | |
counter=$((counter+1)) | |
done |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT tracts.geoid, | |
(St_dump(St_intersection(states.geom, tracts.geom))).geom geom | |
FROM cb_2018_us_state_500k states | |
inner join tl_2019_tracts tracts | |
ON st_intersects(states.geom, tracts.geom) | |
WHERE tracts.statefp = '25'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Retrieve as GeoJSON | |
ogr2ogr -f "GeoJSON" tracts.geojson -sql "select geoid, geom from tl_2019_tracts" PG:"host=localhost user=postgres dbname=gis_db port=5432" -progress | |
# Retrieve as GeoJSON clipped by state | |
ogr2ogr -f "GeoJSON" tracts-clipped-by-state.geojson -sql "select tracts.geoid, (ST_Dump(ST_Intersection(states.geom, tracts.geom))).geom geom from cb_2018_us_state_500k states inner join tl_2019_tracts tracts on ST_Intersects(states.geom, tracts.geom)" PG:"host=localhost user=postgres dbname=gis_db port=5432" -progress | |
# Create Mapbox tiles from state-clipped GeoJSON file | |
tippecanoe -zg -o tracts_2019_clipped.mbtiles --coalesce-densest-as-needed --extend-zooms-if-still-dropping -aI --use-attribute-for-id=geoid tracts-clipped-by-state.geojson | |
# Drag and drop *.mbtiles file as a new TileSet on Mapbox | |
# Add a new style for this TileSet in Mapbox so that we can use mouse events on the tracts |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment