Steps related to creating a census tract map using the Diversity Data Kids API, PostGIS, and Mapbox

census-tract-exploration.sql

# ...retrieve data from current and previous data sets - http://data.diversitydatakids.org [great API for census level data]
# ...and match current w/ previous census tract ids

# Drop table and view before starting
DROP 
  VIEW IF EXISTS clipped_census_tracts_with_all_data;
DROP 
  TABLE IF EXISTS census_tract_with_all_data_points;
  
# Create a new table with all data points - current and previous
CREATE TABLE census_tract_with_all_data_points (
  geoid TEXT, 
  total_est INT, 
  previous_total_est INT, 
  percent_change NUMERIC, 
  name TEXT
);

# Insert all the formatted values into the table
# Each value is formatted as [
#   '40001',
#   10,
#   20,
#   .125,
#   'Census Tract Name',
# ]
INSERT INTO census_tract_with_all_data_points (
  geoid, 
  total_est, 
  previous_total_est, 
  percent_change, 
  name
) 
VALUES 
  % L;
  
# Create a census tracts view, clipped by state boundaries, with all data points - for export to GeoJSON
CREATE VIEW clipped_census_tracts_with_all_data AS 
SELECT 
  tracts.geoid, 
  tracts.geom, 
  points.total_est, 
  points.previous_total_est, 
  points.percent_change, 
  points.name 
FROM 
  clipped_census_tracts AS tracts
  LEFT JOIN census_tract_with_all_data_points points ON tracts.geoid = points.geoid;

census-tract-upload.sh

# Retrieve all data points from clipped census tract data table and store as GeoJSON
# using https://gdal.org/programs/ogr2ogr.html
# Note: This comes out of the box if you install Postgres on a Mac with [PostgresApp](https://postgresapp.com/)
ogr2ogr \
  -f "GeoJSON" \
  tracts-clipped-by-state-with-all-data.geojson \
  -sql "SELECT geoid, geom, total_est, previous_total_est, percent_change, name FROM clipped_census_tracts_with_all_data" \
  PG:"host=localhost user=postgres dbname=gis_db port=5432" \
  -progress

# Take the GeoJSON data file and create a Mapbox Tileset
# using https://github.com/mapbox/tippecanoe
tippecanoe -zg \
  -o tracts_2019_clipped_w_all_data.mbtiles \
  --coalesce-densest-as-needed \
  --extend-zooms-if-still-dropping -aI \
  --use-attribute-for-id=geoid tracts-clipped-by-state-with-all-data.geojson

# ... drag and drop the .mbtiles file into Mapbox Studio