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
View get_partitions.sql
CREATE OR REPLACE FUNCTION
`osm_planet.get_partitions`(name STRING)
RETURNS ARRAY<DATE> AS (
CASE
WHEN name="place-city" THEN ARRAY<DATE>["1970-01-01"]
WHEN name="place-town" THEN ARRAY<DATE>["1970-01-02"]
WHEN name="place-village" THEN ARRAY<DATE>["1970-01-03"]
WHEN name="place-hamlet" THEN ARRAY<DATE>["1970-01-04"]
WHEN name="place-national_capital" THEN ARRAY<DATE>["1970-01-05"]
WHEN name="place-suburb" THEN ARRAY<DATE>["1970-01-06"]
View top-osm-features.sql
SELECT
LOWER(tags.key) AS k
,LOWER(tags.value) AS v
,COUNT(*) AS c
FROM `bigquery-public-data.geo_openstreetmap.features` JOIN UNNEST (all_tags) AS tags
WHERE LOWER(key) NOT IN (
'source','import','created_by','restriction','attribution','note','operator','mapper','province','objtype','name','ref','wikidata','alt_name','fixme','name_1','int_name','int_ref','hgv','old_ref','old_name','official_name','import_uuid','to','from','description','denotation','brand','email','opening_hours','notas','tipo','rcn_ref'
)
AND key NOT LIKE '%:%'
AND key NOT LIKE '%"%'
View junk.csv
http://tf-models.arilot.org/static-tf-models/img/Abstract-Patterned_Blouse/img_00000049.jpg blouse
http://tf-models.arilot.org/static-tf-models/img/Abstract-Stripe_Fuzzy_Sweater/img_00000011.jpg sweater
View global-cities.csv
Region City
Asean Bangkok
Asean Hanoi
Asean Ho Chi Minh City
Asean Jakarta
Asean Kuala Lumpur
Asean Manila
Asean Phnom Penh
Asean Singapore
Asia Ahmedabad
@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
@allenday
allenday / install-cuda.sh
Created Jun 7, 2017
Install CUDA debian package
View install-cuda.sh
#!/bin/bash
echo "Checking for CUDA and installing."
# Check for CUDA and try to install.
if ! dpkg-query -W cuda; then
# The 16.04 installer works with 16.10.
curl -O http://developer.download.nvidia.com/compute/cuda/repos/ubuntu1604/x86_64/cuda-repo-ubuntu1604_8.0.61-1_amd64.deb
dpkg -i ./cuda-repo-ubuntu1604_8.0.61-1_amd64.deb
apt-get update
apt-get install cuda -y
fi
You can’t perform that action at this time.