Skip to content

Instantly share code, notes, and snippets.

Avatar
💭
in the

Naoya Moritani n0531m

💭
in the
  • Singapore
View GitHub Profile
@n0531m
n0531m / bearing.sql
Last active May 24, 2020
UDF to calculate bearing in BigQuery
View bearing.sql
#
# https://en.wikipedia.org/wiki/Radian
# https://towardsdatascience.com/calculating-the-bearing-between-two-geospatial-coordinates-66203f57e4b4
CREATE TEMP FUNCTION
PI (degree float64) AS (ACOS(-1));
CREATE TEMP FUNCTION
RADIANS (degree FLOAT64) AS (degree * ACOS(-1) / 180);
CREATE TEMP FUNCTION
@n0531m
n0531m / covic19_publicdataset_locationname_fluctuation_check.sql
Last active Apr 4, 2020
covic19_publicdataset_locationname_fluctuation
View covic19_publicdataset_locationname_fluctuation_check.sql
#DECLARE # PRECISION INT64 DEFAULT 6;
DECLARE PRECISION_GEOHASH INT64 DEFAULT 2;
WITH # first table : only aggregating the rows with exact match on country_region, province_state, longitude, latitude
# also rounding lat/lng to a set precision. (defined in declard param)
# also adding a geohash value based point point
t1 AS
( SELECT CASE
WHEN country_region IN ("South Korea",
"Korea, South",
@n0531m
n0531m / listDlvmImages.sh
Created Mar 11, 2020
GCP : list deep learning VMs
View listDlvmImages.sh
#!/bin/bash
gcloud --project deeplearning-platform-release \
compute images list --no-standard-images
View getBqTempTableByJobid.sh
function getBqTempTableByJobid {
local JOBID=$1
bq show --format json -j ${JOBID} \
| jq -r '.configuration.query.destinationTable | .projectId +":"+.datasetId+"."+.tableId'
}
@n0531m
n0531m / date_range.sql
Last active Feb 5, 2020
BigQuery : Generate range of dates
View date_range.sql
SELECT day
FROM UNNEST(
GENERATE_DATE_ARRAY(DATE('2020-01-01'), DATE('2020-01-31'), INTERVAL 1 DAY)
) as day
@n0531m
n0531m / gist:3a163ae52c343731be1df8703177aa0f
Created Dec 2, 2019
show the actual rotation and not auto-rotate the image when in preview
View gist:3a163ae52c343731be1df8703177aa0f
defaults write com.apple.Preview PVImagePrintingAutoRotate 0
@n0531m
n0531m / pom.xml
Created Aug 19, 2019
postgres-with-cloud-sql-socket-factory
View pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<!--
# full credit to one of the comments here https://stackoverflow.com/questions/55880595/cant-connect-cloud-data-fusion-with-google-cloud-sql-for-postgresql
# just updated the dependency and mvn plugin versions
$ mvn clean package
-->
<modelVersion>4.0.0</modelVersion>
<groupId>com.altostrat.moritani</groupId>
@n0531m
n0531m / list_gcp_zones_skylake.sh
Created May 29, 2019
list GCP zones with Skylake
View list_gcp_zones_skylake.sh
#!/bin/bash
gcloud --format json compute zones list | jq -r '.[] | select((.availableCpuPlatforms[]) | contains("Intel Skylake")) | .name'
@n0531m
n0531m / check_project_quota.sh
Last active Apr 4, 2019
GCP : Check project quota
View check_project_quota.sh
#!/bin/bash
## lists project quota in use
## "jq" is required
## usage: ./checkquota.sh <PROJECTID>
PROJECT=$1
NOW=`date +%Y-%m-%d`
gcloud compute regions list --format json --project $PROJECT \
@n0531m
n0531m / bq_udf_voronoi.sql
Last active Aug 24, 2018
A query in BigQuery that leverages a Javascript library to compute a voronoi diagram.
View bq_udf_voronoi.sql
#standardSQL
CREATE TEMP FUNCTION
testVolonoi (data ARRAY<STRUCT<x float64, y float64, id string, name string>>, margin_ratio float64)
RETURNS ARRAY<STRUCT<id string, name string, wkt String>>
LANGUAGE js AS """
let xl,xr=data[0].x;
let yt,yb=data[0].y;
for(i=1;i<data.length;i++){
if(data[i].x < xl) xl=data[i].x ;
You can’t perform that action at this time.