Skip to content

Instantly share code, notes, and snippets.

@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()
@allenday
allenday / contract referrers
Created Aug 12, 2020
which address did user interact with in session right before target address 0x698ff47b84837d3971118a369c570172ee7e54c2 (or null)?
View contract referrers
WITH wanted AS (
SELECT
session_id,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY block_timestamp) AS pos,
CASE WHEN tx.to_address = "0x698ff47b84837d3971118a369c570172ee7e54c2" THEN TRUE ELSE NULL END AS ok,
tx.to_address
FROM `crypto-public-data.aux.materialized_sessions` AS sessions JOIN UNNEST(transactions) AS tx
WHERE TRUE
),
raw_referrers AS (
View sessionize-ethereum.pl
#!/usr/bin/perl
=pod
--root level tx
SELECT from_address AS u
, ARRAY_AGG(DISTINCT(UNIX_SECONDS(block_timestamp))) AS ss_array
FROM `bigquery-public-data.crypto_ethereum.traces`
WHERE trace_address IS NULL
GROUP BY u
=cut
use strict;
@allenday
allenday / Makefile
Last active Aug 6, 2020
coingecko-etl
View Makefile
%.ohlc.csv :
perl ./coingecko-csv.pl ${@:.ohlc.csv=} > $@
%.meta.jsonl :
perl ./coingecko-metadata.pl ${@:.meta.jsonl=} > $@
all.meta.jsonl :
cat `ls *.meta.jsonl | grep -v $@` | perl -ne 'print lc' | sort > $@
all.ohlc.csv :
cat `ls *.ohlc.csv | grep -v $@` > $@
all :