Skip to content

Instantly share code, notes, and snippets.

View marklit's full-sized avatar

Mark Litwintschik marklit

View GitHub Profile
@marklit
marklit / sqlite3_insert.sh
Created May 18, 2016 20:21
Insert compressed CSV data into an SQLite3 table
$ (echo -e "PRAGMA synchronous=OFF; PRAGMA count_changes=OFF;\n.mode csv\n.separator ,\n.import /dev/stdin trips";
gunzip -c ~/taxi-data/*.csv.gz) | \
sqlite3 trips.db
@marklit
marklit / es-dsl.md
Created May 11, 2016 07:28
Elasticsearch DSL queries generated by SQL plugin

Query 1

SELECT cab_type,
       count(*)
FROM trips
GROUP BY cab_type
@marklit
marklit / mappings.txt
Created April 8, 2016 14:19
Taxi Data Mapping w/ ES-compatible data types
trip_id integer
vendor_id string
pickup_datetime TIMESTAMP
dropoff_datetime TIMESTAMP
store_and_fwd_flag string
rate_code_id integer
pickup_longitude float
pickup_latitude float
dropoff_longitude float
dropoff_latitude float
@marklit
marklit / brin.sql
Last active August 20, 2018 03:56
Postgres 9.5 on Ubuntu 15
-- From: http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-brin-indexes/
CREATE TABLE brin_example AS SELECT generate_series(1,100000000) AS id;
CREATE INDEX btree_index ON brin_example(id);
-- By default, the block size is 128 pages. This resolution can be increased or decreased using the pages_per_range
CREATE INDEX brin_index ON brin_example USING brin(id);
CREATE INDEX brin_index_64 ON brin_example USING brin(id) WITH (pages_per_range = 64);
CREATE INDEX brin_index_256 ON brin_example USING brin(id) WITH (pages_per_range = 256);
CREATE INDEX brin_index_512 ON brin_example USING brin(id) WITH (pages_per_range = 512);
-- The lower the pages per block, the more space the index will occupy, but the less lossy the index will be, i.e. it will need to discard fewer rows.
// See: https://imgur.com/6o6DP4Q
package com.example.mark.myapplication;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
// See: https://imgur.com/gafDVW5
package com.example.mark.myapplication;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
@marklit
marklit / .psqlrc
Created October 28, 2015 08:25
Postgres psql customisations
\set PROMPT1 '%[%033[1;33m%]%M:%>%[%033[1;32m%] %n@%/%R%[%033[0m%]%# '
\set PROMPT2 ' %R > '
\x auto
\timing
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\pset null '¤'
@marklit
marklit / passenger_counts.csv
Last active August 29, 2015 14:27
Commercial Airline Passenger Counts between airports (for latest year reported, usually 2013)
ABE CLT 49130
ABE DTW 31350
ABE MYR 7670
ABE PHL 34670
ABE PIE 24270
ABX SYD 225400
ABZ BHX 125060
ABZ DUB 47295
ABZ KOI 49091
ABZ LCY 63163
@marklit
marklit / passengers.svg
Last active August 29, 2015 14:27
Commercial Airline Passenger Counts between airports (for latest year reported, usually 2013)
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@marklit
marklit / passengers.json
Last active August 29, 2015 14:27
Commercial Airline passengers per year
{
"ACE-BFS": {
"airports": {
"ACE": {
"airport_name": "Lanzarote Airport",
"iata": "ACE",
"latitude": 28.94556,
"longitude": -13.60528
},
"BFS": {