An automated approach to retrieving Census Place shape-files for every state and inserting into a Postgres database

README.md

Census Tracts - Clipped by State

Census Tracts - Extending to Ocean

import.sh

#!/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

index.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) 
WHERE      tracts.statefp = '25';

retrieve-as-geojson.sh

# 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