fitnr

"""
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.'''

fitnr

/*
 * 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

fitnr

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[]))

fitnr

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 = {}

fitnr

#!/bin/bash
set -e

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

export DIR

downsample() {

fitnr

fitnr

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

fitnr

fitnr

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

fitnr

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