Skip to content

Instantly share code, notes, and snippets.

@fitnr
fitnr / snd_parse.py
Last active June 20, 2024 01:53
The New York City Street Name Dictionary is in an insane fixed-width format. This python script parses it into a CSV.
from csv import writer
from collections import OrderedDict
'''
The New York City Street Name Dictionary is in an insane fixed-width format.
This python script parses it into a CSV.
http://www.nyc.gov/html/dcp/html/bytes/applbyte.shtml
'''
FIELDS_S = OrderedDict([
('_', 1),
@fitnr
fitnr / county-epsg.csv
Last active April 10, 2024 21:33
List of State Plane coordinate systems and their various ID codes.
COUNTYFIPS NAME STATEPLANEFIPS EPSG
16079 Shoshone County 1103 26970
16073 Owyhee County 1103 26970
16071 Oneida County 1101 26968
16077 Power County 1101 26968
16075 Payette County 1103 26970
06115 Yuba County 0402 26942
06111 Ventura County 0405 26945
06113 Yolo County 0402 26942
31177 Washington County 2600 32104
@fitnr
fitnr / fips2states.sh
Last active July 31, 2022 01:01
Replace US states with their FIPS code or postal code or vis/versa
#!/bin/bash
# Replace FIPS codes with US states/^territory names
# usage: sh fips2state2.sh inputfile.txt > outputfile.txt
# or just copy everything but the '$1' and run on the command line
sed -e 's/^54/West Virginia/' -e 's/^02/Alaska/' -e 's/^01/Alabama/' -e 's/^05/Arkansas/' -e 's/^60/American Samoa/' \
-e 's/^04/Arizona/' -e 's/^06/California/' -e 's/^08/Colorado/' -e 's/^09/Connecticut/' -e 's/^11/District of Columbia/' \
-e 's/^10/Delaware/' -e 's/^12/Florida/' -e 's/^13/Georgia/' -e 's/^66/Guam/' -e 's/^15/Hawaii/' \
-e 's/^19/Iowa/' -e 's/^16/Idaho/' -e 's/^17/Illinois/' -e 's/^18/Indiana/' -e 's/^20/Kansas/' \
-e 's/^21/Kentucky/' -e 's/^22/Louisiana/' -e 's/^25/Massachusetts/' -e 's/^24/Maryland/' -e 's/^23/Maine/' \
-e 's/^26/Michigan/' -e 's/^27/Minnesota/' -e 's/^29/Missouri/' -e 's/^28/Mississippi/' -e 's/^30/Montana/' \
@fitnr
fitnr / geotransform.py
Last active December 17, 2021 14:11
Convert between pixel/geo values with a gdal geotransform
"""
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
fitnr / mv_depend.sql
Created November 12, 2021 19:06
recursively get dependent matviews
/*
* 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
fitnr / table_size.sql
Created March 28, 2021 15:19
get table and index sizes in postgresql
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
fitnr / address-splitter.js
Last active February 6, 2021 01:45
Split an address into number and street parts
// http://stackoverflow.com/questions/18082/validate-numbers-in-javascript-isnumeric/1830844#1830844
function isNumber(n) { return !isNaN(parseFloat(n)) && isFinite(n); }
// Check if character is a fraction, e.g. ¼
function isFractionalChar(n) {
c = n.charCodeAt();
return (c >= 188 && c <= 190) || (c >= 8531 && c <= 8542);
}
// return the first fractional character in a string
@fitnr
fitnr / buildingclasscode.csv
Last active January 8, 2021 14:47
NYC Pluto building class codes and land uses. From: http://www.nyc.gov/html/dcp/pdf/bytes/pluto_datadictionary.pdf
id name
A0 ONE FAMILY DWELLINGS: Cape Cod
A1 ONE FAMILY DWELLINGS: Two Stories Detached (Small or Moderate Size, With or Without Attic)
A2 ONE FAMILY DWELLINGS: One Story (Permanent Living Quarters)
A3 ONE FAMILY DWELLINGS: Large Suburban Residence
A4 ONE FAMILY DWELLINGS: City Residence
A5 ONE FAMILY DWELLINGS: Attached or Semi-Detached
A6 ONE FAMILY DWELLINGS: Summer Cottages/Mobile Homes/Trailers
A7 ONE FAMILY DWELLINGS: Mansion Type
A8 ONE FAMILY DWELLINGS: Bungalow Colony/Land Coop Owned
@fitnr
fitnr / git-ics
Created December 28, 2015 23:23
git tool for converting log to ICS file
# git-ics
# Place in your $PATH (e.g. ~/bin)
# Accepts any options that can be passed to git log
# usage: git ics [opts] > cal.ics
echo BEGIN:VCALENDAR
echo METHOD:PUBLISH
echo VERSION:2.0
echo PRODID:-//git-ics//git-ics//EN
echo CALSCALE:GREGORIAN
@fitnr
fitnr / hexbin.sql
Last active June 27, 2020 07:33
create hex bins in postgis
-- 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 $$