allenday’s gists

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 / 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 / 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 / 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 / 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 / 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 / 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 / global-cities.csv

Last active October 22, 2019 06:06

Global Cities

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 / 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')

Allen Day allenday