Skip to content

Instantly share code, notes, and snippets.

@allenday
allenday / profanity.sh
Created Jul 13, 2021
run profanity for x sec to generate a vanity key, then exit
View profanity.sh
( cmdpid=$BASHPID; (sleep 3600; kill $cmdpid) & exec ./profanity.x64 --matching 888888XXXXXXXXXXXXXXXXXXXXXXXXXXXX888888 | grep --line-buffered Score >> keys.txt )
@allenday
allenday / geohash-precipitation-noaa_gsod.sql
Created Jul 3, 2021
get total precipitation within a date range for a geohash polygon
View geohash-precipitation-noaa_gsod.sql
#standardSQL
CREATE TEMPORARY FUNCTION geohashDecode(geohash STRING)
RETURNS STRUCT<bbox ARRAY<FLOAT64>, lat FLOAT64, lng FLOAT64>
LANGUAGE js AS """
if (!geohash) return null;
var base32 = '0123456789bcdefghjkmnpqrstuvwxyz';
geohash = geohash.toLowerCase();
var evenBit = true;
var latMin = -90, latMax = 90;
var lonMin = -180, lonMax = 180;
@allenday
allenday / bq-geohash-udf.sql
Created Jul 2, 2021 — forked from vladaman/bq-geohash-udf.sql
Google BigQuery standardSQL UDF function to decode geohash string into bounding box and coordinates
View bq-geohash-udf.sql
#standardSQL
CREATE TEMPORARY FUNCTION geohashDecode(geohash STRING)
RETURNS STRUCT<bbox ARRAY<FLOAT64>, lat FLOAT64, lng FLOAT64>
LANGUAGE js AS """
if (!geohash) return null;
var base32 = '0123456789bcdefghjkmnpqrstuvwxyz';
geohash = geohash.toLowerCase();
var evenBit = true;
var latMin = -90, latMax = 90;
@allenday
allenday / download-worldpop.sh
Created Mar 27, 2021
earth-engine-to-bigquery
View download-worldpop.sh
#!/bin/sh
CREDENTIALS="credentials.json"
COLLECTION="projects/earthengine-public/assets/WorldPop/GP/100m/pop"
YEAR=2020
export "GOOGLE_APPLICATION_CREDENTIALS=$CREDENTIALS"
IMAGES=$(ogrinfo -ro -al "EEDA:" -oo "COLLECTION=$COLLECTION" -where "year=$YEAR" | grep 'gdal_dataset (String) = ' | cut -d '=' -f2 | tr -d ' ')
for IMAGE in $IMAGES
do
View geojson-to-jsonl.py
#!/usr/bin/env python3
# See: https://medium.com/@lakshmanok/how-to-load-geojson-files-into-bigquery-gis-9dc009802fb4
# Processes output from: https://code.earthengine.google.com/49115c966c4c83a3ca7ac52bd5aba4f6
import json
from geolib import geohash
# check the accuracy
# PostGIS check from https://postgis.net/docs/ST_GeoHash.html
View healthcare_min_distance.sql
WITH nearby_health AS (
SELECT pop.geo_id, pop.population, CAST(ST_DISTANCE(ST_CENTROID(pop.geog),layer.geometry)/1000 AS INT64) AS distance
FROM
`bigquery-public-data.worldpop.population_grid_1km` AS pop,
`bigquery-public-data.geo_openstreetmap.planet_layers` AS layer
WHERE TRUE
AND pop.country_name = 'Singapore'
AND layer_name IN ('hospital','doctors')
AND ST_INTERSECTSBOX(layer.geometry, longitude_centroid-5, latitude_centroid-5, longitude_centroid+5, latitude_centroid+5)
AND longitude_centroid > -175 AND longitude_centroid < 175 AND latitude_centroid > -85 AND latitude_centroid < 85
View fcpxml-structure.md

overall structure

  • resources (multiple audio files, video files, and other resources referenced in the sequence)
  • project
    • sequence (a group of multiple assets that compose a single timeline)
      • spine' (a timeline. this is the OUTER spine in which everything else is contained)
        • marker
          • complete="0" is a TODO marker
        • video (ignore these)
        • asset-clip' (a segment of a video or audio file)
          • spine (this is a floating timeline that contains one or more of...)
View osm-covid-exploration.sql
WITH all_edits AS (
SELECT
CAST(osm_timestamp AS DATE) AS d, 0 AS code, COUNT(*) AS count
FROM `gcp-pdp-osm-dev.osm_to_bq_history.history_layers`
GROUP BY d
),
-- HEALTH
health_creates AS (
SELECT
View axie_query
CREATE TEMP FUNCTION hex_to_binary(x STRING) AS
(
REPLACE(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(x, '0','0000'), '1','0001'),'2','0010'),'3','0011'),'4','0100'),'5','0101'),'6','0110'),'7','0111'),'8','1000'),'9','1001'),'A','1010'),'B','1011'),'C','1100'),'D','1101'),'E','1110'),'F','1111')
);
WITH axie AS (
SELECT block_timestamp, input, to_address, hex_to_binary(UPPER(SUBSTR(output,3,63))) AS genome
FROM `bigquery-public-data.crypto_ethereum.traces`
WHERE TRUE
AND input LIKE '0xa6472906%' --getAxie()