interstates.sql

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 )
    
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
  b
ON
  st_contains(a.state_geom,
    b.road_geom)
WHERE
  b.full_name LIKE 'I-%'
  AND st_LENGTH(b.road_geom) > 5000
GROUP BY
  a.state_name