emireadcode

WITH
  counties AS (
  SELECT
    a.total_pop,
    b.*
  FROM
    `bigquery-public-data.census_bureau_acs.county_2018_1yr` a
  JOIN
    `bigquery-public-data.geo_us_boundaries.counties` b
  USING

emireadcode

SELECT
  state_name,
  CASE
    WHEN state_name IN ('Minnesota', 'Wisconsin', 'Iowa', 'Illinois', 'Indiana', 'Ohio', 'Michigan', 'Missouri', 'Kansas', 'Nebraska', 'North Dakota', 'South Dakota') THEN 'Midwest'
  ELSE
  'Not Midwest'
END
  AS midwest
FROM
  `bigquery-public-data.geo_us_boundaries.states`

emireadcode

WITH
  b AS (
  SELECT
    st_union_agg(road_geom) AS road_geom,
    full_name
  FROM
    `bigquery-public-data.geo_us_roads.us_national_roads`
  GROUP BY
    full_name )
    

emireadcode

SELECT
  COUNT(b.full_name) AS interstates,
  a.state_name,
  ARRAY_AGG(b.full_name) AS roads
FROM
  `bigquery-public-data.geo_us_boundaries.states` a
JOIN
  `bigquery-public-data.geo_us_roads.us_national_roads` b
ON
  st_contains(a.state_geom,

emireadcode

# first, we will create a CTE to filter our NYC Taxi Data

WITH
  nyc AS (
  SELECT
    *,
    
# creating a geometry from our latitude and longitude columns
    
    st_geogpoint(pickup_longitude,

emireadcode

CREATE OR REPLACE TABLE
  `PROJECT.DATASET.nyc` AS
SELECT
  *,
  st_geogpoint(pickup_longitude,
    pickup_latitude) AS geom
FROM
  `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`
WHERE
  pickup_datetime BETWEEN '2015-06-01T00:00:00'

emireadcode

SELECT
  a.vendor_id,
  b.geo_id
FROM
  PROJECT.DATASET.nyc a
JOIN
  bigquery-public-data.geo_census_blockgroups.us_blockgroups_national b
ON
  st_intersects(a.geom,
    b.blockgroup_geom)

emireadcode

SELECT
  a.vendor_id,
  b.geo_id
FROM
  PROJECT.DATASET.nyc a
JOIN
  bigquery-public-data.geo_census_blockgroups.us_blockgroups_national b
ON
  st_intersects(a.geom,
    b.blockgroup_geom)

emireadcode

SELECT
  COUNT(a.vendor_id),
  b.geo_id
FROM
  PROJECT.DATASET.nyc a
JOIN
  bigquery-public-data.geo_census_blockgroups.us_blockgroups_national b
ON
  st_intersects(a.geom,
    b.blockgroup_geom)

emireadcode

WITH
  nyc AS (
  SELECT
    *
  FROM
    PROJECT.DATASET.nyc
  LIMIT
    10000)

SELECT