Skip to content

Instantly share code, notes, and snippets.

View allenday's full-sized avatar

Allen Day allenday

155 followers · 91 following
View GitHub Profile
@allenday
allenday / tensorflow_json_parser.py
Last active January 2, 2020 09:56 — forked from sameerg07/tensorflow_json_parser.py
converts the json file downloaded using image classifer tool of dataturks to dataset folder
#This script has been solely created under dataturks. Copyrights are reserved
#EXAMPLE USAGE
#python3 tensorflow_json_parser.py --json_file "flower.json" --dataset_path "Dataset5/"
import json
import glob
import urllib.request
@allenday
allenday / prolific-users.sql
Created December 27, 2019 05:32
Which users have contributed the most relations to the current version of OpenStreetMap?
SELECT username, COUNT(*) AS c
FROM
`bigquery-public-data.geo_openstreetmap.planet_relations` AS relations
GROUP BY username
ORDER BY c DESC
@allenday
allenday / fire-hydrants-globally-obsolete.sql
Last active December 27, 2019 05:28
Find all obsolete fire hydrants on the planet
SELECT *
FROM
`bigquery-public-data.geo_openstreetmap.history_nodes` AS node JOIN UNNEST(all_tags) AS tags
WHERE
(tags.key = 'emergency' AND tags.value = 'fire_hydrant')
AND id NOT IN (
SELECT id
FROM
`bigquery-public-data.geo_openstreetmap.planet_nodes` AS node JOIN UNNEST(all_tags) AS tags
WHERE
@allenday
allenday / changesets-in-singapore.sql
Created December 27, 2019 05:09
Find all OpenStreetMap changeset IDs that contain points in Singapore's geographical extent
WITH singapore AS (
SELECT ST_MAKEPOLYGON(ST_MAKELINE(
[ST_GEOGPOINT(103.6920359,1.1304753),ST_GEOGPOINT(104.0120359,1.1304753),
ST_GEOGPOINT(104.0120359,1.4504753),ST_GEOGPOINT(103.6920359,1.4504753)
]
)) AS boundingbox
)
SELECT DISTINCT changeset.id
FROM
@allenday
allenday / get_partitions.sql
Last active November 7, 2019 07:20
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"]
@allenday
allenday / top-osm-features.sql
Last active October 29, 2019 03:02
top OSM features
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 '%"%'
@allenday
allenday / junk.csv
Created October 23, 2019 05:01
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
@allenday
allenday / global-cities.csv
Last active October 22, 2019 06:06
Global Cities
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 October 9, 2019 03:19
Highway length to building count ratio for NYC
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 September 25, 2019 16:58
Eatery density difference between two datasets
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')