Neil Freeman fitnr

## geotransform.py
"""
Where transform is a geotransform array, e.g.
>>> from osgeo import gdal
>>> src = gdal.Open('example.tiff')
>>> transform = src.GetGeoTransform()
>>> (-82.254, 3.0858676207514586e-05, 0.0, 41.3163, 0.0, -3.086680761099182e-05)
>>> # [x origin (left), pixel width, x skew, y origin (top), y skew, y pixel height (negative because counting from top of image)]
"""
def px2geo(geotransform, coord):
    '''Convert pixel coordinates to projected geographic coordinates.'''

## mv_depend.sql
/*
 * Arguments:
 * :matview - schema-qualified name of materialized view
 */
WITH RECURSIVE vvv AS (
		SELECT DISTINCT ON (objid, refobjid) v.oid::regclass AS view, 1 AS level
		FROM pg_depend AS d
		  JOIN pg_rewrite AS r ON r.oid = d.objid
		  JOIN pg_class AS v ON v.oid = r.ev_class
		  JOIN pg_class as c ON c.oid = d.refobjid AND c.oid::regclass != v.oid::regclass

## table_size.sql
SELECT
  nspname,
  relname,
  relkind,
  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[]))

## decode_countyparcel.py
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 = {}

## downsample-polygonize.sh
#!/bin/bash
set -e

DIR=$1
SHP=$2
TMP=$(mktemp -d)

export DIR

downsample() {

## 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

## query.sql
-- Load data from a UK-wide GTFS into postgres using https://github.com/fitnr/gtfs-sql-importer
-- Used this GTFS file:
-- https://transitfeeds.com/p/association-of-train-operating-companies/284

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

## 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

## hexbin.sql
-- adapted from https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093

-- 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 $$

## s2cover.py
#!/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
	"""
	Where transform is a geotransform array, e.g.
	>>> from osgeo import gdal
	>>> src = gdal.Open('example.tiff')
	>>> transform = src.GetGeoTransform()
	>>> (-82.254, 3.0858676207514586e-05, 0.0, 41.3163, 0.0, -3.086680761099182e-05)
	>>> # [x origin (left), pixel width, x skew, y origin (top), y skew, y pixel height (negative because counting from top of image)]
	"""
	def px2geo(geotransform, coord):
	'''Convert pixel coordinates to projected geographic coordinates.'''
	/*
	* Arguments:
	* :matview - schema-qualified name of materialized view
	*/
	WITH RECURSIVE vvv AS (
	SELECT DISTINCT ON (objid, refobjid) v.oid::regclass AS view, 1 AS level
	FROM pg_depend AS d
	JOIN pg_rewrite AS r ON r.oid = d.objid
	JOIN pg_class AS v ON v.oid = r.ev_class
	JOIN pg_class as c ON c.oid = d.refobjid AND c.oid::regclass != v.oid::regclass
	SELECT
	nspname,
	relname,
	relkind,
	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 = {}
	#!/bin/bash
	set -e

	DIR=$1
	SHP=$2
	TMP=$(mktemp -d)

	export DIR

	downsample() {
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
	-- Load data from a UK-wide GTFS into postgres using https://github.com/fitnr/gtfs-sql-importer
	-- Used this GTFS file:
	-- https://transitfeeds.com/p/association-of-train-operating-companies/284

	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
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
	-- adapted from https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093

	-- 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 $$
	#!/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