janbenetka

DECLARE i INT64 DEFAULT 0;
DECLARE DATES ARRAY<DATE>;
DECLARE event_date DATE;
  
SET DATES = GENERATE_DATE_ARRAY(DATE(2019,1,1), DATE(2019, 12, 31), INTERVAL 1 DAY);

LOOP
    SET i = i + 1;  

    IF i > ARRAY_LENGTH(DATES) THEN 

janbenetka

DECLARE HIST_STEPS INT64 DEFAULT 20;

WITH data AS ( 
    SELECT green_fraction as value
    FROM `uc-x-data.14_day_retention.green_counties`
)
, stats AS (
  SELECT min+step*i min, min+step*(i+1)max
  FROM (
    SELECT max-min diff, min, max, (max-min)/HIST_STEPS step, GENERATE_ARRAY(0, HIST_STEPS, 1) i

janbenetka

SELECT jslibs.h3.ST_GEOGPOINTFROMH3("u2ce02j")
SELECT jslibs.h3.ST_H3_BOUNDARY(jslibs.h3.ST_H3(ST_GEOGPOINT(13.377534960188237, 49.747300576525554), 11))
SELECT jslibs.h3.h3GetResolution("u2ce02j");


# lat/lon to hexagon
SELECT jslibs.h3.ST_H3(ST_GEOGPOINT(statistics.coordinate.longitude, statistics.coordinate.latitude), 10) AS dwell_hex_id,


WITH polygon AS (

janbenetka

SELECT * FROM subset 
WHERE RAND() < 1e6 / (SELECT COUNT(*) FROM subset)

janbenetka

from IPython.display import HTML, IFrame, display
display(HTML("<style>.container { width:1400px !important; }</style>"))

janbenetka

PREFIX dbpedia2: <http://dbpedia.org/property/> 
PREFIX dbo: <http://dbpedia.org/ontology/>
PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#>

SELECT *
WHERE {
  ?county rdf:type yago:County108546183 .
  ?county dbo:country dbr:United_States.
  ?county dbo:state ?state.
  ?county rdfs:label ?county_label.

janbenetka

CREATE OR REPLACE TABLE `uc-prox-core-dev.z_jan_retained.adjacent_tracts` AS

WITH adjacent_tracts AS (
  SELECT a.fips, b.fips as fips_adjacent, a.state_name, b.state_name as state_name_adjacent, a.county_name, b.county_name as county_name_adjacent, a.geog, b.geog as geog_adjacent,
  FROM `uc-atlas.maps_us.census_tracts` a, `uc-atlas.maps_us.census_tracts` b
  WHERE ST_INTERSECTS(ST_BUFFER(a.geog, 25), b.geog)
)

SELECT * FROM adjacent_tracts

janbenetka

from IPython.display import Javascript  # Restrict height of output cell.
display(Javascript('''google.colab.output.setIframeHeight(0, true, {maxHeight: 300})'''))

janbenetka

WITH osm AS (
  SELECT 
  layer_code, 
  layer_class, 
  layer_name, 
  gdal_type as geography_type, 
  osm_id, 
  CASE 
    WHEN (tags.key = 'name') THEN tags.value
    ELSE ''

janbenetka

WITH daily AS (
  SELECT 
    local_event_date, 
    venue_type,    
    SUM(device_count) device_count, 
    SUM(person_count) person_count,
  FROM `uc-prox-core-dev.international_metrics.traffic_trends` 
  GROUP BY local_event_date, venue_type
)