Skip to content

Instantly share code, notes, and snippets.

Avatar
🛰️
🌲 🔥 🎄 🔥

Stuart Illson Sillson

🛰️
🌲 🔥 🎄 🔥
View GitHub Profile
@Sillson
Sillson / StateBoundaries.sql
Created Nov 3, 2019 — forked from jakebathman/StateBoundaries.sql
The approximate max/min latitude and longitude for all states and major territories
View StateBoundaries.sql
-- 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 Jun 28, 2019
Slippy Map Tile Composite
View tile-composite.R
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 Sep 19, 2018
capitalize function in bigquery
View capitalize.js
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 Jul 24, 2018 — forked from jacquestardie/modern-geospatial-python.md
Modern remote sensing image processing with Python
View jsonb_to_pg_array.sql
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 Jan 16, 2018
Drop all Postgres Constraints that match
View constraint_dropper.sql
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) || ';';
View cred_finder.rb
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 Apr 7, 2017
drop foreign schema and reimport
View drop_foreign_tables_and_reimport.sql
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;
View psql_drop_foreign_tables.sql
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;
View active_locks_view.sql
CREATE OR REPLACE VIEW public.active_locks AS
SELECT t.schemaname,
t.relname,
l.locktype,
l.page,
l.virtualtransaction,
l.pid,
l.mode,
l.granted
FROM pg_locks l