bigquery-gis

census_zip.sql

#standardsql
with zipcodes as (
SELECT
  zip_census.zipcode as zipcode,
  population,
  WKT as geometry,
  ST_CENTROID(ST_GeogFromText(WKT)) as centroid
FROM
  `bigquery-public-data.census_bureau_usa.population_by_zip_2010` AS zip_census
  join `bigquery-public-data-staging.zcta_test.2017` as zip_geom
  on zip_census.zipcode = zip_geom.Zcta5ce10
WHERE
   (gender not like 'male' and gender not like 'female') and
   minimum_age is NULL and
   maximum_age is NULL
)
SELECT
  zipcodes.*,
  state_code,
  city,
  county
FROM
  `bigquery-public-data.utility_us.zipcode_area` as zip_area
  join zipcodes on zip_area.zipcode = zipcodes.zipcode
where 
  ST_DWITHIN(centroid, ST_GeogPoint(-122.3321,47.6062), 10000)

wx_zip.sql

#standardsql
WITH params AS (
  SELECT 60626 AS zipcode,
         10    AS maxdist_km
),
zipcode AS (
   SELECT ST_GeogFromText(WKT) AS polygon 
   FROM `cloud-training-demos.demos.zipcode_polygon2017`, params 
   WHERE ZCTA5CE10 = params.zipcode
),
stations AS (
  SELECT
    id,
    name,
    ST_GeogPoint(longitude, latitude) AS loc,
    ST_Distance(ST_GeogPoint(longitude, latitude), zipcode.polygon) AS dist_meters
  FROM
    `bigquery-public-data.ghcn_d.ghcnd_stations`,
    params,
    zipcode
  WHERE ST_DWithin(ST_GeogPoint(longitude, latitude), zipcode.polygon, params.maxdist_km*1000)
)
SELECT * from stations
ORDER BY dist_meters ASC
LIMIT 100