Skip to content

Instantly share code, notes, and snippets.

@lakshmanok

lakshmanok/us_counties_populated_pixels.sql

Created July 6, 2020 23:49
Show Gist options
  • Save lakshmanok/e9ca7a03a8ceabed16845ac4fbd2935e to your computer and use it in GitHub Desktop.
Save lakshmanok/e9ca7a03a8ceabed16845ac4fbd2935e to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. lakshmanok created this gist Jul 6, 2020.
    25 changes: 25 additions & 0 deletions us_counties_populated_pixels.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    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