Skip to content

Instantly share code, notes, and snippets.

View rhysallister's full-sized avatar

rhysallister rhysallister

3 followers · 0 following
View GitHub Profile
@rhysallister
rhysallister / pull_nhc_kmz_data.sql
Created August 9, 2020 00:31
-- Pull hurricane forecast data from NHC archive
DO $$
DECLARE
rec record;
new_name text;
BEGIN
CREATE SCHEMA IF NOT EXISTS cyclonic;
FOR rec IN
WITH response AS (
@rhysallister
rhysallister / layer_column_list.sql
Created October 28, 2018 19:09
list layers for schema
with one as (
select nspname, relname, json_build_object(attname, typname) column_info from pg_catalog.pg_class
join pg_catalog.pg_namespace on relnamespace = pg_namespace.oid
join pg_catalog.pg_attribute on attrelid = pg_class.oid
join pg_catalog.pg_type on atttypid = pg_type.oid
where nspname = 'cart2wheel2' and relkind = 'r' and attnum > 0
),
two as (
select nspname, relname, array_to_json(array_agg(column_info)) column_info from one
@rhysallister
rhysallister / push2db
Last active October 30, 2018 14:13
push an entire dataset to a given schema in a postgresql database using ogr2ogr
#!/bin/bash
# copies a geopackage or ESRI FileGDB to a given schema on a postgresql database
# ./push2db schema_name "PG:dbname=dbname host=host port=port user=user password=password" filename.gpkg
#
#./push2db cartwheel "PG:dbname=jpsgis host=localhost port=5432 user=rhys password=rhys" ~/Public/electricgis_nkredsqlentclu.gdb.zip
SCHEMA=$1
DB_URL=$2
@rhysallister
rhysallister / confounded.sql
Created January 21, 2018 01:37
array_agg and/or having true=ALL is confounding me
drop schema confounded cascade;
create schema confounded;
-- DROP TABLE confounded.dataset;
CREATE TABLE confounded.dataset
(
seq integer,
path_seq integer,
start_vid bigint,
@rhysallister
rhysallister / vertonghen.py
Last active January 2, 2018 18:26
outputs plain html given JSON from google docs comments
#!/usr/bin/env python3
# usage: vertonghen.py filename.json [-- outfile filename.html]
import argparse
import json
parser = argparse.ArgumentParser()
parser.add_argument('infile', help="JSON file to be vertonghen'd")
@rhysallister
rhysallister / pk.pk
Created July 15, 2017 16:30
pk
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDoQQwY69V6lrT9tT/d2j+Y/cySLySrahxWzo36OTQdID2cVK98c4MG2G9S7UqEd/zz/r/rLiU5qAf2ftrfiJboPs6mefbPffgHx5DGi8/vEa41f4UtNEyOmYLJAwkZ49OqbJqT5yW9HEV4Dme5M1MK+lCW/r/BHf6KhHZz/Uwsynns5b5XemecWJAHD9qJlyW828QTiuyuxB0efUDcDr6bZKU1AuTVHDgS+1WNjZFYjRVhai3pIW/dlTxhZjee3+vj5LznTb1xLHJGBA9gp5gD8hfwxcjZqOgiENx/aXx4TwRWXyDhqQ9svzYmvAndxXn+XkL4pbH2Gu91yh5JrkKJ rhys.stewart@gmail.com
@rhysallister
rhysallister / bulk_change.sql
Last active April 6, 2017 16:59
Bulk change ownership of foreign tables in a schema
do
$$
DECLARE
bret record;
fret text;
idx text;
BEGIN
@rhysallister
rhysallister / index.html
Created March 18, 2017 04:03
Number Saver: Riot.js Observable Example
<grozmite></grozmite>
<hr>
<groz-len></groz-len>
<script type="riot/tag">
<grozmite>
<button onclick={generate}>Generate a new number</button>
<h1 name="showhere" onclick="{clearMe}">{ arbitrary_number }</h1>
<button onclick={saveme}>Save this number</button>
this.generate = function() {
@rhysallister
rhysallister / schema_union.sql
Created December 13, 2016 16:06
union all tables in a schema
DO
$$
declare
vv text;
hh record;
qry text;
qar text[];
begin
qry = 'create table plus1.plus1 as ';
for hh in select * from pg_tables where schemaname = 'plus1' loop
@rhysallister
rhysallister / hurricanes.py
Last active August 22, 2021 21:41
Pull down all data from nhc website as a JSON dump
# Pulls down national Hurricane Centre data.
#
# Data are located in zipfiles. Each zipfile contains one or more shapfiles.
# A shapfile is really a collection of files, I'm interested in the .dbf file as
# this contains the tabular data as well as columns with geographic information.
# If the .dbf file did not have the location data in the table I would have to use
# a library like shapely or fiona to get the geographic data
#
import os