Skip to content

Instantly share code, notes, and snippets.

fitnr / table_size.sql
Created Mar 28, 2021
get table and index sizes in postgresql
View table_size.sql
to_char(round(reltuples::numeric, -2), '999,999,999,999,999') as est_row_count,
pg_size_pretty(pg_indexes_size(c.oid)) index_size,
pg_size_pretty(pg_relation_size(c.oid)) table_size
FROM pg_class c
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE (nspname <> ALL (ARRAY['pg_catalog', 'information_schema']::name[]))
import sys
import json
from io import BytesIO
import geomet.wkb
def bytes2dict(x):
'''Decode a dictionary encoded in bytes using the dicAsBytes method.'''
b = BytesIO(x)
count = read_int(b)
result = {}
set -e
TMP=$(mktemp -d)
export DIR
downsample() {
View selected-services.csv
station_id terminal_id route_id agency_name route_long_name
ACB EUS 176599 Serco Caledonian Sleeper CS train service from DEE to EUS
ACB EUS 192409 Virgin Trains VT train service from EUS to WBQ
ACB EUS 214187 Serco Caledonian Sleeper CS train service from ABD to EUS
ACB EUS 214314 Serco Caledonian Sleeper CS train service from EUS to ABD
ACB EUS 24722 Virgin Trains VT train service from LIV to EUS
ACB EUS 24780 Virgin Trains VT train service from BPN to EUS
ACB EUS 24808 Virgin Trains VT train service from EUS to LAN
ACB EUS 24825 Virgin Trains VT train service from EUS to LIV
ACB EUS 24895 Virgin Trains VT train service from GLC to EUS
fitnr / query.sql
Last active Jan 8, 2019
Find the number of rail stations in GB that have direct service to a London terminus
View query.sql
-- Load data from a UK-wide GTFS into postgres using
-- Used this GTFS file:
with terminals as ( -- ID codes for London terminal stations
values ('BFR'), ('CST'), ('CHX'), ('CTK'), ('EUS'), ('FST'), ('KGX'), ('LST'), ('LBG'), ('MYB'), ('MOG'), ('OLD'), ('PAD'), ('STP'), ('SPX'), ('VXH'), ('VIC'), ('WAT'), ('WAE')
, london_trips as ( -- select trips that include the above codes
select distinct feed_index, route_id, trip_id
from gtfs.stops
View county-nest.csv
statefp countyfp name population
48 179 Gray 23028
39 067 Harrison 15521
1 069 Houston 103891
48 013 Atascosa 47710
17 031 Cook 5227575
1 001 Autauga 55049
38 025 Dunn 4284
17 091 Kankakee 111493
49 001 Beaver 6437
fitnr / hexbin.sql
Last active Jun 27, 2020
create hex bins in postgis
View hexbin.sql
-- adapted from
-- usage: select * FROM hexbin(numeric, geometry)
-- cover a table with something like:
-- select row_number() over () id, geom
-- from hexbin(5280, st_transform(st_setsrid(st_expand(ST_EstimatedExtent('table', 'geom'), 0.1), 4269), 2255));
CREATE OR REPLACE FUNCTION hexbin (height numeric, minx numeric, miny numeric, maxx numeric, maxy numeric, srid integer)
RETURNS TABLE (geom geometry(polygon))
AS $$
fitnr /
Created Sep 26, 2018
Returns an s2 covering as ndgeojson
#!/usr/bin/env python3
import json
import argparse
import s2sphere as s2
def point2coord(point):
return s2.LatLng.longitude(point).degrees, s2.LatLng.latitude(point).degrees
fitnr / 2014-primary-gov.csv
Last active Sep 14, 2018
NYS democratic primary results for nyc
View 2014-primary-gov.csv
electdist county cuomo teachout credico
65001 New York 2 3 0
65002 New York 16 23 0
65003 New York 5 6 0
65004 New York 2 4 0
65005 New York 30 8 3
65006 New York 43 41 5
65007 New York 44 32 2
65008 New York 53 47 5
65009 New York 6 6 0
View mtfcc.csv
We can make this file beautiful and searchable if this error is corrected: Unclosed quoted field in line 7.
MTFCC,Feature Class,Superclass,Point,Linear,Areal,Description
C3022,Mountain Peak or Summit,Miscellaneous Topographic Features,Y,N,N,A prominent elevation rising above the surrounding level of the Earth's surface.
C3023,Island,Miscellaneous Topographic Features,Y,Y,Y,"An area of dry or relatively dry land surrounded by water or low wetland. [including archipelago, atoll, cay, hammock, hummock, isla, isle, key, moku and rock]"
C3024,Levee,Miscellaneous Topographic Features,N,Y,Y,An embankment flanking a stream or other flowing water feature to prevent overflow.
C3026,"Quarry (not water-filled), Open Pit Mine or Mine",Miscellaneous Topographic Features,Y,N,Y,An area from which commercial minerals are or were removed from the Earth; not including an oilfield or gas field.
C3027,Dam,Miscellaneous Topographic Features,Y,Y,Y,A barrier built across the course of a stream to impound water and/or control water flow.
C3061,Cul-de-sac,Miscellaneous Topographic Features,Y,N,N,"An expanded paved area at the end of a stree