Skip to content

Instantly share code, notes, and snippets.

View pnorman's full-sized avatar

Paul Norman pnorman

126 followers · 4 following
View GitHub Profile
@pnorman
pnorman / working_rel_ref.sql
Created July 4, 2014 03:05
(
SELECT
w.way, string_agg(COALESCE(r.ref, w.ref),';') AS ref,
w.highway, char_length(string_agg(COALESCE(r.ref, w.ref),';')) AS length
FROM (
SELECT
ctid, way, highway, ref
FROM planet_osm_roads r
WHERE r.highway in ('motorway','trunk','primary','secondary')
AND r.way && !bbox!) AS w
@pnorman
pnorman / geotag.sh
Last active August 29, 2015 14:03
Toolchain for geotagging photos
# Copy images into suitable named directory indicating date and trip
export GEM_HOME=/home/pnorman/osm/gpx2exif
# Sharpen and fix colours
mogrify -unsharp 8x1.7 -normalize *-*/IMG_*.JPG
# Adjust times. Reverse sign from JOSM. My camera drifts so that I need a positive time in JOSM, so this tends to be -= for me.
exiftool "-DateTimeOriginal-=0:0:2" "-CreateDate-=0:0:2" *-*/*.JPG
# Geotag
@pnorman
pnorman / routerelation.sql
Created June 27, 2014 09:06
Finds route relations and ways where refs disagree on usage of -
SELECT r.id as rid, w.id as wid, r.tags->'ref' as rref, w.tags->'ref' as wref
FROM relations r JOIN relation_members rm on (r.id=rm.relation_id) JOIN ways w on (rm.member_id = w.id and rm.member_type='W')
WHERE r.tags@>hstore('type','route')
AND r.tags@>hstore('route','road')
AND r.tags?'ref'
AND (r.tags->'ref') LIKE '%-%'
AND w.tags?'ref'
AND w.tags?'highway'
AND NOT (w.tags->'ref') LIKE '%-%'
@pnorman
pnorman / arizona.geojson
Last active August 29, 2015 14:03
States with IRs cut
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@pnorman
pnorman / install.sh
Created June 20, 2014 21:58
Shell commands for postgres on 12.04
sudo -i
cat > /etc/apt/sources.list.d/pgdg.list <<EOF
deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main
#deb-src http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main
EOF
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get --no-install-recommends install -y postgresql-9.3-postgis-2.1 postgresql-contrib-9.3 proj-bin
@pnorman
pnorman / distinct outer tags.sql
Last active August 29, 2015 14:02
SQL queries used for determining old-style multipolygon member information
SELECT count(*), n_tags
FROM (SELECT count(DISTINCT w.tags) AS n_tags
FROM relations r
JOIN relation_members rm ON (r.id=rm.relation_id)
JOIN ways w ON (rm.member_type='W' AND rm.member_role='outer' AND rm.member_id=w.id)
WHERE r.tags=hstore('type','multipolygon')
GROUP BY r.id) AS s
GROUP BY n_tags
ORDER BY n_tags;
@pnorman
pnorman / index.html
Last active August 29, 2015 14:02
crossroad-names demo
<!DOCTYPE html>
<html>
<head>
<script src='http://api.tiles.mapbox.com/mapbox.js/v0.6.7/mapbox.js'></script>
<link href='http://api.tiles.mapbox.com/mapbox.js/v0.6.7/mapbox.css' rel='stylesheet' />
<style>
body { margin:0; padding:0; }
#map { position:absolute; top:0; bottom:0; width:100%; }
</style>
</head>
@pnorman
pnorman / index.html
Last active May 18, 2016 07:35
preview
<!DOCTYPE html>
<html>
<head>
<script src='http://api.tiles.mapbox.com/mapbox.js/v0.6.7/mapbox.js'></script>
<link href='http://api.tiles.mapbox.com/mapbox.js/v0.6.7/mapbox.css' rel='stylesheet' />
<style>
body { margin:0; padding:0; }
#map { position:absolute; top:0; bottom:0; width:100%; }
</style>
</head>
@pnorman
pnorman / index.html
Last active August 29, 2015 14:01 — forked from tyrasd/index.html
<!DOCTYPE html>
<html>
<head>
<script src='http://api.tiles.mapbox.com/mapbox.js/v0.6.7/mapbox.js'></script>
<link href='http://api.tiles.mapbox.com/mapbox.js/v0.6.7/mapbox.css' rel='stylesheet' />
<style>
body { margin:0; padding:0; }
#map { position:absolute; top:0; bottom:0; width:100%; }
</style>
</head>
@pnorman
pnorman / placenames_medium.mss
Created May 16, 2014 05:33
#placenames-medium {
[capital = 'yes'] {
[zoom >= 5][zoom < 15] {
text-name: "[name]";
text-size: 10;
text-fill: @placenames;
text-face-name: @book-fonts;
text-halo-radius: 1.5;
text-min-distance: 10;
[zoom >= 6] {