Skip to content

Instantly share code, notes, and snippets.

Allen Day allenday

Block or report user

Report or block allenday

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@allenday
allenday / OSM-NYC-highways-to-buildings.sql
Created Oct 9, 2019
Highway length to building count ratio for NYC
View OSM-NYC-highways-to-buildings.sql
WITH country AS (
SELECT
osm_layers.name as osm_name,
osm_layers.all_tags AS osm_tags,
(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'admin_level') as admin_level,
osm_layers.geometry AS geometry
FROM `openstreetmap-public-data-dev.osm_planet.osm_layers` AS osm_layers
WHERE osm_layers.name='boundary-administrative'
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'name' and tags.value = 'United States')
),
@allenday
allenday / OSM-NYC-differential-eatery-density.sql
Created Sep 25, 2019
Eatery density difference between two datasets
View OSM-NYC-differential-eatery-density.sql
WITH city AS (
SELECT
layers.name as osm_name,
layers.all_tags AS osm_tags,
(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'admin_level') as admin_level,
layers.geometry AS geometry
FROM `openstreetmap-public-data-dev.osm_planet.osm_layers_partitions` AS layers
WHERE layers.partnum = `openstreetmap-public-data-dev.osm_planet.name2partnum`('boundary-administrative')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'name' and tags.value='New York')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'place' and tags.value='city')
View OSM-LON-eatery-density.sql
WITH city AS (
SELECT
layers.name as osm_name,
layers.all_tags AS osm_tags,
(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'admin_level') as admin_level,
layers.geometry AS geometry
FROM `openstreetmap-public-data-dev.osm_planet.osm_layers_partitions` AS layers
WHERE layers.partnum = `openstreetmap-public-data-dev.osm_planet.name2partnum`('boundary-administrative')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'name' and tags.value='London')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'place' and tags.value='city')
@allenday
allenday / OSM-NYC-Central_Park-adjacent.sql
Last active Sep 25, 2019
How may buildings are within 100m of Central Park, New York?
View OSM-NYC-Central_Park-adjacent.sql
WITH city AS (
SELECT
layers.name as osm_name,
layers.all_tags AS osm_tags,
(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'admin_level') as admin_level,
layers.geometry AS geometry
FROM `bigquery-public-data.geo_openstreetmap.layers` AS layers
WHERE layers.partnum = `bigquery-public-data.geo_openstreetmap.name2partnum`('boundary-administrative')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'name' and tags.value='New York')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'place' and tags.value='city')
@allenday
allenday / OSM-layer-counts.sql
Created Sep 25, 2019
How many objects are in each OSM layer?
View OSM-layer-counts.sql
SELECT name, COUNT(name) AS c
FROM `bigquery-public-data.geo_openstreetmap.layers`
GROUP BY name ORDER BY c DESC
View Kiss-Me-Lola-Jane.csv
Measure Chord T Section
-8 0:00 Intro close embrace
-7 close embrace
-6 close embrace
-5 close embrace
-4 close embrace
-3 close embrace
-2 close embrace
-1 close hold
0 0:31 Verse 1 (You and I) rotate L 180
View Bachata-Rosa-Chords.txt
G---D---e---b---C---G---a---D---
G---D---e---b---C---G---A-G-D---
C---b-e-C---b-e-C---b-e-A---a-D-
C---b-e-C---b-e-C---b-e-a-A-a-D-
C---b-e-C---b-e-C---b-e-a-----D-
C---b-e-C---b-e-C---b-e-a-----D-
D-------a-G-D-----------a-D-----
@allenday
allenday / tokenize.py
Last active Aug 19, 2019
tokenize bottlenecks
View tokenize.py
#docker run -d -p 8080:8080 -v /root:/data gcr.io/deeplearning-platform-release/tf-cpu.1-13
import os
import sys
import tempfile
import shutil
import numpy as np
np.set_printoptions(threshold=np.inf)
from keras.preprocessing.image import ImageDataGenerator, img_to_array, load_img
from keras.models import Sequential
from keras.layers import Dropout, Flatten, Dense
@allenday
allenday / tx.sql
Last active Jun 7, 2019
recent anomalous token transfers
View tx.sql
WITH
z AS -- TODO better to use percentiles
(
SELECT
tok.address,
COUNT(tok.address) AS n,
AVG(SAFE_CAST(tx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC))) AS mu,
STDDEV(SAFE_CAST(tx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC))) AS sigma
FROM `crypto-etl-ethereum-dev.crypto_ethereum.tokens` AS tok,
`crypto-etl-ethereum-dev.crypto_ethereum.token_transfers` AS tx
@allenday
allenday / gas-price-by-date.sql
Created May 26, 2019
Ethereum gas price by date from BigQuery
View gas-price-by-date.sql
SELECT CAST('2019-04-01' AS DATE) AS d, AVG(gas_used) AS gas
FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE TRUE
AND timestamp >= TIMESTAMP('2019-04-01')
AND timestamp <= TIMESTAMP(DATE_ADD(CAST('2019-04-01' AS DATE), INTERVAL 1 DAY))
GROUP BY d
You can’t perform that action at this time.