nyc_cabs.sql

# create a bounding area from the New York CBSA geometry

WITH
  bounding_area AS (
  SELECT
    geom
  FROM
    `carto-do-public-data.carto.geography_usa_cbsa_2019`
  WHERE
    do_label LIKE "%New York%"),
 
 # create H3 cell geometries across the NYC metro
 
  h3 AS (
  SELECT
    points_unnested,
    bqcarto.h3.ST_BOUNDARY(points_unnested) AS geom
  FROM (
    SELECT
      bqcarto.h3.ST_ASH3_POLYFILL(geom,
        8) AS points
    FROM
      bounding_area),
    UNNEST(points) AS points_unnested),
    
 # aggregate your data by getting the count of each trip within each H3 cell
 # make sure to limit your date range otherwise you could query lots of data!
 
  aggregate AS (
  SELECT
    COUNT(a.vendor_id),
    h3.points_unnested
  FROM
    h3
  JOIN
    `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015` a
  ON
  
# we use st_contains to see the points that fall within each H3 cell
  
    ST_Contains(h3.geom,
      ST_GeogPoint(a.pickup_longitude,
        a.pickup_latitude))
  WHERE
    a.pickup_datetime BETWEEN '2015-03-01T00:00:00'
    AND '2015-03-08T00:00:00'
    
# as we can't group by the geom field, we need one final CTE below

  GROUP BY
    h3.points_unnested)
    
# we join our aggregated data to our H3 geometries in the final step

SELECT
  aggregate.*,
  h3.geom
FROM
  aggregate
JOIN
  h3
USING
  (points_unnested)