Intersection queries for zip codes and counties within congressional districts

intersection_queries.sql

COPY (
SELECT JSON_AGG(f) FROM (
SELECT s.stusps || '-' || cd.cd116fp AS id, ARRAY_AGG(z.zcta5ce10) AS zip_codes
  FROM cb_2018_us_cd116_500k cd
  INNER JOIN cb_2018_us_zcta510_500k z
  ON ST_Intersects(cd.geom,z.geom)
  JOIN cb_2018_us_state_500k s ON cd.statefp = s.statefp
  GROUP BY s.stusps || '-' || cd.cd116fp
  ) f)
  TO '/Users/christopherlanoue/Documents/congressional_zip_code_lookup.json';

  COPY (
  SELECT JSON_AGG(f) FROM (
  SELECT s.stusps || '-' || cd.cd116fp AS id, ARRAY_AGG(cty.geoid) AS counties
    FROM cb_2018_us_cd116_500k cd
    INNER JOIN cb_2018_us_county_500k cty
    ON ST_Intersects(cd.geom,cty.geom)
    JOIN cb_2018_us_state_500k s ON cd.statefp = s.statefp
    GROUP BY s.stusps || '-' || cd.cd116fp
    ) f)
    TO '/Users/christopherlanoue/Documents/congressional_county_lookup.json';