Webinar: Load PostGIS with osm2pgsql (2/6)
Below is the recording of my Load PostGIS with osm2pgsql webinar from January 14, 2020. This is the second in a series of six covering how I work with PostGIS and OpenStreetMap.
I had a great time presenting this session, I hope having this recording available is helpful. Below the video is a list the links showing throughout the slides.
Recording
Series complete
This webinar series is completed. See the intro page for the schedule and links to other videos in the series.
Links
Links from the slides in the order they appeared. Sorry for the lack of context in this list right now, I will try to come back to this and provide more context in the future.
- https://wiki.openstreetmap.org/wiki/Osm2pgsql/schema
- https://wiki.openstreetmap.org/wiki/PBF_Format
- https://blog.rustprooflabs.com/2019/10/osm2pgsql-scaling
- https://github.com/openstreetmap/osm2pgsql#building
- https://github.com/openstreetmap/osm2pgsql/issues/1045
- https://www.postgresql-archive.org/BUG-16183-PREPARED-STATEMENT-slowed-down-by-jit-td6117538.html#none
- https://blog.rustprooflabs.com/2020/01/postgis-osm-load-2020
- https://github.com/openstreetmap/osm2pgsql/blob/master/docs/usage.md
- https://github.com/openstreetmap/osm2pgsql/issues/940
- https://blog.rustprooflabs.com/2016/07/psql-unlogged-table
Helper Bash Script
#!/bin/bash
########################################################
#
# Where CLI parameters are: <sub-region> <cache> <processes>
#
# Quickest to load, helpful for testing:
# ./load_osm.sh colorado 1000 1 &> load_co_osm.log &
#
########################################################
SECONDS=0
echo "Start on `hostname` at `date`"
echo "Running Sub-Region: $1"
echo "Creating pgosm DB..."
psql -d postgres -c "CREATE DATABASE IF NOT EXISTS pgosm;"
echo "Creating PostGIS and HSTORE extensions..."
psql -d pgosm -c "CREATE EXTENSION IF NOT EXISTS postgis; "
psql -d pgosm -c "CREATE EXTENSION IF NOT EXISTS hstore; "
echo "Ensuring ~/tmp exists..."
mkdir ~/tmp || true
echo "Downloading OSM file"
wget https://download.geofabrik.de/north-america/us/$1-latest.osm.pbf -O ~/tmp/$1-latest.osm.pbf
echo "Starting osm2pgsql for $1: `date`"
echo "cache: $2"
echo "Num processes: $3"
osm2pgsql --create --slim --drop \
--cache $2 \
--number-processes $3 \
--hstore \
--multi-geometry \
-d pgosm ~/tmp/$1-latest.osm.pbf
echo "osm2pgsql completed for $1 on host `hostname` at: `date`"
duration=$SECONDS
echo "Total Duration: $(($duration / 60)) minutes and $(($duration % 60)) seconds elapsed."
Summary
Need help with your PostgreSQL servers or databases? Want a speaker on the technologies and data I am excited about? Contact us to start the conversation!
Mastodon