us_counties_populated_pixels.sql

CREATE OR REPLACE TABLE publicdata.us_tracts AS
WITH populated_areas AS (
  SELECT 
  bounds, population_density
  FROM `ai-analytics-solutions`.publicdata.popdensity_sedac_rectangles
  WHERE 
  year = 2020 AND
  (tile = 'gpw_v4_population_density_rev11_2020_30_sec_1.asc' OR
   tile = 'gpw_v4_population_density_rev11_2020_30_sec_2.asc'
  ) AND
  population_density > 10 -- persons per square kilometer
)
SELECT 
  state_fips_code, county_fips_code,
  ANY_VALUE(state) AS state_code,
  ANY_VALUE(county_name) AS county_name,
  SUM(population_density) AS population,
  ST_UNION(ARRAY_AGG(bounds)) AS populated_tract_geom,
  ANY_VALUE(county_geom) AS tract_geom
FROM `bigquery-public-data`.geo_us_boundaries.counties
JOIN `bigquery-public-data`.geo_us_boundaries.states USING(state_fips_code)
CROSS JOIN populated_areas
WHERE
  ST_CONTAINS(county_geom, bounds)
GROUP BY 1, 2