wriglz

Convert a Shapefile to a Geopackage

In a terminal, use:

ogr2ogr -f GPKG your.gpkg firstfile.shp

You can then add a second shapefile to the same geopackage using:

ogr2ogr -append -f GPKG your.gpkg secondfile.shp

wriglz

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;

wriglz

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;

wriglz

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

wriglz

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)

wriglz

CREATE OR REPLACE TABLE
  `dataset.north_south_divide`
CLUSTER BY
  h3 AS(
  WITH
    england_grid AS (
    SELECT
      geoid AS h3,
      population
    FROM

wriglz

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

wriglz

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) );

wriglz

DECLARE
  bbox GEOGRAPHY;
SET
  bbox = (
  SELECT
    geom
  FROM
    `global_boundaries_dataset`
  WHERE
    NAME_EN = 'United Kingdom');

wriglz

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