ianthrive’s gists

ianthrive / orthodrome_km.sql

Created January 23, 2014 09:36

Orthodrome ("great circle") distance between two latitude/longitude points. This function uses the spherical law of cosines formula, rather than the seemingly more popular (probably because it's older) "haversine" formula. It is assumed the earth is a perfect sphere with a radius of 6,371km.

	CREATE OR REPLACE FUNCTION public.orthodrome_km(lat1 numeric, lon1 numeric, lat2 numeric, lon2 numeric)
	RETURNS double precision
	LANGUAGE plpgsql
	AS $function$
	BEGIN
	return acos(sin(radians(lat1))sin(radians(lat2))+cos(radians(lat1))cos(radians(lat2))cos(radians(lon2)-radians(lon1)))6371;
	END;
	$function$

ianthrive / createinstallmedia.sh

Created February 13, 2014 16:09

Create installer disk for Mac OS Mavericks

	sudo /Applications/Install\ OS\ X\ Mavericks.app/Contents/Resources/createinstallmedia \
	--volume /Volumes/Untitled \
	--applicationpath /Applications/Install\ OS\ X\ Mavericks.app \
	--nointeraction

ianthrive / trigger_update.sql

Created May 13, 2014 15:57

A PostgreSQL trigger function to updated a timestamp column whenever a row is updated.

	CREATE OR REPLACE FUNCTION trigger_updated RETURNS TRIGGER LANGUAGE plpgsql AS $$
	BEGIN
	NEW.updated = NOW();
	RETURN NEW;
	END;
	$$

	CREATE TRIGGER updated BEFORE UPDATE ON payment
	OR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*)
	EXECUTE PROCEDURE trigger_updated();

ianthrive / pgsql_audit.sql

Last active August 29, 2015 14:01

An audit changes on a PostgreSQL table.

	CREATE TABLE customers_audit AS (
	id SERIAL NOT NULL UNIQUE PRIMARY KEY,
	created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	before HSTORE,
	after HSTORE
	);

	CREATE FUNCTION trigger_customers_audit RETURNS TRIGGER LANUAGE plpgsql AS $$
	INSERT INTO customers_audit(before, after)
	SELECT hstore(OLD), hstore(NEW);

ianthrive / filter-select-with-field.js

Last active August 29, 2015 14:02

Filter a <select> list with a <input type="text">.

	// depends on jquery

	function filterSelectWithField(selectList, searchField) {
	selectList = $(selectList);
	searchField = $(searchField);
	options = selectList.find('option').clone();
	timer = null;

	function sanitize(str) {
	return $.trim(str).replace(/\s+/g, ' ').toLowerCase();

ianthrive / macos-hide-user.sh

Created June 16, 2014 08:45

Hide Mac OS user account.

sudo defaults write /Library/Preferences/com.apple.loginwindow HiddenUsersList -array-add dlt

ianthrive / nth_dow.sql

Last active August 29, 2015 14:06

Calculates and returns the Nth day of the week for the given date.

	CREATE OR REPLACE FUNCTION public.extract_nth_dow(d date) RETURNS integer
	AS $$
	-- Calculates and returns the Nth day of the week for the given date.
	SELECT (EXTRACT(day FROM d)::INTEGER - 1) / 7 + 1;
	$$ LANGUAGE sql;

ianthrive / is_leap_year.sql

Last active October 29, 2024 15:08

PostgreSQL functions to determine if a date is a leap year. Two different functions to demonstrate two different ways.

	CREATE OR REPLACE FUNCTION is_leap_year(year integer)
	RETURNS BOOLEAN AS $$
	SELECT ($1 % 4 = 0) AND (($1 % 100 <> 0) or ($1 % 400 = 0))
	$$ LANGUAGE sql IMMUTABLE STRICT;

	CREATE OR REPLACE FUNCTION is_leap_year(date date)
	RETURNS BOOLEAN AS $$
	SELECT DATE_PART('month', DATE_TRUNC('year', $1)+'1 months 28 days'::INTERVAL) = 2;
	$$ LANGUAGE sql IMMUTABLE STRICT;

ianthrive / array_subtraction.sql

Last active January 18, 2016 01:58

	create or replace function diff_elements(anyarray, anyarray)
	returns anyarray language sql immutable as $$
	select array(
	select unnest($2)
	except
	select unnest($1)
	);$$;

	create operator - (
	procedure = diff_elements,

ianthrive / clear_dns.sh

Last active August 29, 2015 14:20

Clear DNS cache.

	# OS X 10.10
	sudo discoveryutil udnsflushcaches; sudo discoveryutil mdnsflushcache

	# OS X 10.9
	dscacheutil -flushcache; sudo killall -HUP mDNSResponder

	# OS X 10.7-10.8
	sudo killall -HUP mDNSResponder

	# OS X 10.5-10.6

Ian Timothy ianthrive