This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH Sequences AS (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers, [10, 51, 17, 28, 31, 55] AS some_more_numbers | |
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers, [231, 43, 8, 1, 77] AS some_more_numbers | |
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers, [5555, 13515] AS some_more_numbers) | |
SELECT id, flattened_numbers, more_flattened_numbers | |
FROM Sequences, | |
UNNEST(some_numbers) AS flattened_numbers, | |
UNNEST(some_more_numbers) AS more_flattened_numbers; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH Sequences AS | |
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers | |
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers | |
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers) | |
SELECT id, flattened_numbers | |
FROM Sequences | |
CROSS JOIN UNNEST(Sequences.some_numbers) AS flattened_numbers; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
NN AS ( | |
SELECT | |
a.osm_id, | |
-- Select the closest ID | |
ARRAY_AGG(STRUCT(b.osm_id, | |
ST_DISTANCE(a.geom, b.geom) AS dist) | |
ORDER BY | |
ST_DISTANCE(a.geom, b.geom) | |
LIMIT |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
osm_id, | |
ST_CENTROID(geometry) AS geom, | |
(SELECT value FROM UNNEST(all_tags) WHERE key = 'name' ) AS name, | |
FROM | |
bigquery-public-data.geo_openstreetmap.planet_features | |
WHERE | |
('man_made', 'lighthouse') IN ( | |
SELECT | |
(key, value) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE TABLE | |
`dataset.north_south_divide` | |
CLUSTER BY | |
h3 AS( | |
WITH | |
england_grid AS ( | |
SELECT | |
geoid AS h3, | |
population | |
FROM |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
osm_id, | |
geometry, | |
-- Here we are going to extract the station name from the all_tags array: | |
( | |
SELECT | |
value | |
FROM | |
UNNEST(all_tags) | |
WHERE |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION | |
demo.ST_LineLocatePoint(line GEOGRAPHY, | |
point GEOGRAPHY) | |
RETURNS FLOAT64 AS (ST_DISTANCE(ST_STARTPOINT(line),ST_CLOSESTPOINT(line, point) )/ST_LENGTH(line) ); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE | |
bbox GEOGRAPHY; | |
SET | |
bbox = ( | |
SELECT | |
geom | |
FROM | |
`global_boundaries_dataset` | |
WHERE | |
NAME_EN = 'United Kingdom'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
hurricane_geometry AS ( | |
SELECT | |
* EXCEPT (longitude, latitude), -- Select everything except lat & lon | |
ST_GEOGPOINT(longitude, latitude) AS geom, -- As we create a geometry here | |
MAX(usa_wind) OVER (PARTITION BY sid) AS max_wind_speed -- Calculate the max wind speed per storm | |
FROM | |
`bigquery-public-data.noaa_hurricanes.hurricanes` | |
WHERE | |
basin = 'NA' -- Select only storms in the North American basin |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
osm_id, | |
feature_type, | |
osm_timestamp, | |
geometry, | |
-- Here we are going to extract a couple of attributes from the all_tags array: | |
(SELECT value FROM UNNEST(all_tags) WHERE key = 'name' ) AS name, | |
(SELECT value FROM UNNEST(all_tags) WHERE key = 'addr:city') AS city | |
FROM | |
bigquery-public-data.geo_openstreetmap.planet_features |
NewerOlder