nyc_blockgroups_withtreedatacalc.sql

CREATE TABLE
  `cartodb-gcp-solutions-eng-team.kyle_data.nyc_blockgroups_withtreedatacalc` AS
WITH
  nycbgs AS (
  SELECT
    a.blockgroup_geom AS the_geom,
    a.geo_id,
    b.total_pop,
    b.median_income,
    b.income_per_capita,
    b.housing_units -- Some of these columns aren't totally necessary but I might use them for analysis later
  FROM
    `bigquery-public-data.geo_census_blockgroups.us_blockgroups_national` a
  JOIN
    `bigquery-public-data.census_bureau_acs.blockgroup_2018_5yr` b
  ON
    a.geo_id = b.geo_id
  JOIN
    `bigquery-public-data.geo_us_boundaries.counties` c
  ON
    st_contains(c.county_geom,
      a.blockgroup_geom)
    -- NYC County FIPS Codes
  WHERE
    c.county_fips_code IN ('36005',
      '36061',
      '36081',
      '36047',
      '36085') ),
-- Calculate the number (COUNT) and size (average diameter at breast height) 
-- ... for each tree that falls within 10 meters st_dwithin of the blockgroup.
  count_by_bg AS (
  SELECT
    a.geo_id,
    COUNT(b.tree_id) AS tree_count,
    -- number of trees
    AVG(b.tree_dbh) AS avg_tree_diameter -- average tree diameter
  FROM
    nycbgs a
  JOIN
    `bigquery-public-data.new_york_trees.tree_census_2015` b
  ON
    st_dwithin(a.the_geom,
      ST_GEOGPOINT(b.longitude,
        b.latitude), -- used to create a geography with a numeric lat/long columns
      10)
  GROUP BY
    a.geo_id )
-- Join back to the count_by_bg CTE and normalize for square area
SELECT
  a.*,
  b.avg_tree_diameter,
  safe_divide(b.tree_count,
    a.total_pop) AS trees_per_capita,
  st_area(a.the_geom) AS sq_meters,
  b.tree_count,
  safe_divide(b.tree_count,
    st_area(a.the_geom) ) * 1000 AS trees_per_1000squaremeters, -- normalize by square area
FROM
  nycbgs a
JOIN
  count_by_bg b
USING
  (geo_id)