Skip to content

Instantly share code, notes, and snippets.

View Sillson's full-sized avatar
🛰️
🌲 🔥 🎄 🔥

Stuart Illson Sillson

🛰️
🌲 🔥 🎄 🔥
View GitHub Profile
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@Sillson
Sillson / StateBoundaries.sql
Created November 3, 2019 20:24 — forked from jakebathman/StateBoundaries.sql
The approximate max/min latitude and longitude for all states and major territories
-- Create the table
CREATE TABLE IF NOT EXISTS `StateBoundaries` (
`State` varchar(10) DEFAULT NULL,
`Name` varchar(255) DEFAULT NULL,
`MinLat` varchar(50) DEFAULT NULL,
`MaxLat` varchar(50) DEFAULT NULL,
`MinLon` varchar(50) DEFAULT NULL,
`MaxLon` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@Sillson
Sillson / tile-composite.R
Created June 28, 2019 16:32
Slippy Map Tile Composite
library(slippymath)
library(sf)
library(purrr)
library(curl)
library(glue)
library(raster)
library(rgdal)
# pass map.bounds() & zoom from leaflet
# xmin <- map.getBounds()["_southWest"]['lat']
@Sillson
Sillson / capitalize.js
Created September 19, 2018 23:13
capitalize function in bigquery
CREATE TEMPORARY FUNCTION capitalize(str STRING)
RETURNS STRING
LANGUAGE js AS """
return str.replace(
/\\w\\S*/g,
function(txt) {
return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
}
);
""";
@Sillson
Sillson / modern-geospatial-python.md
Created July 24, 2018 21:07 — forked from jqtrde/modern-geospatial-python.md
Modern remote sensing image processing with Python
@Sillson
Sillson / jsonb_to_pg_array.sql
Created June 26, 2018 03:36
jsonb_to_pg_array
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(
p_input jsonb
) RETURNS TEXT[] AS $BODY$
DECLARE v_output text[];
BEGIN
SELECT array_agg(ary)::text[]
INTO v_output
@Sillson
Sillson / constraint_dropper.sql
Created January 16, 2018 19:15
Drop all Postgres Constraints that match
BEGIN transaction;
DO $$DECLARE r record;
BEGIN
FOR r IN SELECT table_name,constraint_name
FROM information_schema.constraint_table_usage
WHERE table_name IN ('table_1', 'table_2', 'table_3') /* here be the tables to select. refactor if you want to select all */
AND constraint_name ~'\d$' /* pattern matcher */
LOOP
EXECUTE 'ALTER TABLE ' || quote_ident(r.table_name)|| ' DROP CONSTRAINT '|| quote_ident(r.constraint_name) || ';';
@Sillson
Sillson / cred_finder.rb
Created April 21, 2017 20:44
heroku pg creds
class CredFinder
attr_accessor :cred_hsh
def initialize(urn)
puts "***** \nSapSucking Creds For -- \n#{urn} \n*****"
@cms_urn = urn
@cred_hsh = {'dbname'=>'','host'=>'','port'=>'','user'=>'', 'password'=>''}
build_creds
end
def build_creds
@Sillson
Sillson / drop_foreign_tables_and_reimport.sql
Created April 7, 2017 16:19
drop foreign schema and reimport
do
$$
declare
l_rec record;
begin
for l_rec in (select foreign_table_schema, foreign_table_name
from information_schema.foreign_tables) loop
execute format('drop foreign table %I.%I cascade', l_rec.foreign_table_schema, l_rec.foreign_table_name);
end loop;
end;