Skip to content

Instantly share code, notes, and snippets.

View pnorman's full-sized avatar

Paul Norman pnorman

View GitHub Profile
@pnorman
pnorman / index_status.sql
Created July 9, 2022 21:13
Report index status
SELECT p.datname, relid::regclass as table, index_relid::regclass as index,
phase, lockers_done ||'/'|| lockers_total as lockers, blocks_done||'/'||blocks_total as blocks,
tuples_done||'/'||tuples_total as tuples, partitions_done||'/'||partitions_total as partitions, query
from pg_stat_progress_create_index p join pg_stat_activity a on (p.pid = a.pid);
CREATE INDEX CONCURRENTLY planet_osm_polygon_way_area_z10
ON planet_osm_polygon USING GIST (way)
WHERE way_area > 23300;
ALTER INDEX planet_osm_polygon_way_area_z6 RENAME TO planet_osm_polygon_way_area_z6_old;
CREATE INDEX CONCURRENTLY planet_osm_polygon_way_area_z6
ON planet_osm_polygon USING GIST (way)
WHERE way_area > 5980000;
DROP INDEX planet_osm_polygon_way_area_z6_old;
@pnorman
pnorman / tuning.sql
Created February 20, 2019 17:38
tuning
ALSTER SYSTEM SET work_mem = '128MB';
ALSTER SYSTEM SET maintenance_work_mem = '4GB';
ALSTER SYSTEM SET default_statistics_target = '10000';
ALSTER SYSTEM SET autovacuum_vacuum_scale_factor = '0.05';
ALSTER SYSTEM SET autovacuum_analyze_scale_factor = '0.02';
ALSTER SYSTEM SET shared_buffers = '8GB';
ALSTER SYSTEM SET max_wal_size = '4GB';
ALSTER SYSTEM SET min_wal_size = '1GB';
ALSTER SYSTEM SET checkpoint_completion_target = '0.9';
ALSTER SYSTEM SET max_worker_processes = '10';
e (external) 11291.733
m (main) 11256.321
x (extended) 11321.855
@pnorman
pnorman / LICENCE
Created June 6, 2018 05:55
open mouth emoji
This SVG is licensed CC0 by Paul Norman
<!DOCTYPE html>
<html style="height:100%;margin:0;padding:0;">
<title>Leaflet page with OSM render server selection</title>
<meta charset="utf-8">
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.3/dist/leaflet.css" />
<script src="https://unpkg.com/leaflet@1.3/dist/leaflet.js"></script>
<script src="https://unpkg.com/leaflet-hash@0.2.1/leaflet-hash.js"></script>
<style type="text/css">
.leaflet-tile-container { pointer-events: auto; }
</style>
@pnorman
pnorman / names.csv
Last active March 25, 2018 09:05
name data
2808870 name:en
1035545 name:ru
559728 name:ja
488167 name:de
464426 name:fr
410579 name:ar
352527 name:zh
328441 name:uk
277724 name:fi
190678 name:es
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Your server might stop updating and scripts stop working when
planet.openstreetmap.org goes HTTPS-only. If you are using osmosis, you
MUST apply the updates below. Other software and scripts MAY need updates.
operations is working on switching some services to HTTPS-only, including
planet.openstreetmap.org. This has revealed a critical bug in Osmosis where
it fails to follow HTTP redirects.
If using osmosis with --read-replication-interval, please immediately
adjust the baseUrl of your configuration.txt file to point at
We're looking at releasing osm2pgsql 0.94.0 soon and could use testing
of the 0.94.0-RC1 version. Testing from packagers is appreciated,
particularly if you are doing something different with libosmium.
Major changes
- Store unprojected coordinates in slim tables and use osmium dense file
array for flat nodes
This fixes a number of projection-related issues