Jakob Miksch
en

Keep an OpenStreetMap database up-to-date with osm2pgsql

With osm2pgsql it is possible to load data from OpenStreetMap into a PostGIS database. The data can then be processed and analyzed with the full power of spatial SQL. Programs like QGIS, GeoServer or MapServer can read that database and render it on a map. A very impressive feature of osm2pgsql is to keep the database up-to-date with the latest changes from OpenStreetMap. This is done by the commandline tool osm2pgsql-replication (see docs). It checks from which source your initial data comes from and downloads the respective changesets.

This script below can be used to init the database and to update it regularly. This example uses the extract from Germany, Austria and Switzerland (also known als “DACH”).

#!/bin/bash

export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=postgres
export PGDATABASE=osm

DB_SCHEMA=dach
PREFIX=dach

OSM_FILE=/home/jakob/osm-data/dach.osm.pbf
STYLE_FILE=/home/jakob/osm-data/flex-config/dach.lua 

psql -c "CREATE SCHEMA IF NOT EXISTS ${DB_SCHEMA}";

EXTRA_ATTRIBUTES="\
  --style ${STYLE_FILE}\
  --output flex \
  --middle-schema=${DB_SCHEMA}"

# INIT THE DATABASE AND THE REPLICATION 
# THIS SHOULD BE DONE ONLY ONCE, AFTERWARDS THESE COMMANDS CAN BE COMMENTED (DEACTIVATED)

# osm2pgsql \
# --create \
# --slim \
# --verbose \
# --prefix=${PREFIX} \
# ${EXTRA_ATTRIBUTES} \
# ${OSM_FILE}

# osm2pgsql-replication init \
#  --verbose \
#  --prefix=${PREFIX} \
#  --osm-file=${OSM_FILE}

# Download the latest OSM data and load it into PostGIS
# SHOULD BE RUN REGULARLY 
osm2pgsql-replication update \
  --verbose \
  --prefix=${PREFIX} -- ${EXTRA_ATTRIBUTES}

The OpenStreetMap extract can be downloaded from providers like GeoFabrik or openstreetmap.fr.

The import requires a Lua script to define the structure. It is adapted from a Lua script example from the osm2pgsql source code repository. In a real world scenario you would probably use a more detailed Lua script with more tables and settings.

IMPORTANT: Make sure the schema name (in this example dach) matches in the script above and in the Lua file.

local dtable = osm2pgsql.define_way_table('data', {
        { column = 'tags',  type = 'jsonb' },
        { column = 'geom',  type = 'linestring' },
    }, { schema = 'dach' })

function osm2pgsql.process_way(object)
    dtable:insert({
        tags = object.tags,
        geom = object:as_linestring()
    })
end

In my setup I use a desktop computer with Debian to store my data. If the computer is switched on permanently the update script can be run regularly using cron. In my case the computer is only switched on from time to time. For this scenario anacron is the better fit. It ensures the script is run within a specific time period. On Debian/Ubuntu-based systems it can be installed with apt install anacron.

First make your update script executable with chmod +x /path/to/script. Then, the update script needs to be referenced in the file /etc/anacrontab. In my case it looks like this:

# /etc/anacrontab: configuration file for anacron

# See anacron(8) and anacrontab(5) for details.

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
HOME=/root
LOGNAME=root

# These replace cron's entries
1       5       cron.daily      run-parts --report /etc/cron.daily
7       10      cron.weekly     run-parts --report /etc/cron.weekly
@monthly        15      cron.monthly    run-parts --report /etc/cron.monthly
1 5       dach_osm /home/jakob/scripts/osm2pgsql_dach.sh >> /home/jakob/logs/osm2pgsql_dach.log 2>&1

This configuration runs the script once per day. After booting it waits for 5 minutes to actually start the script.

EmailGitHubMastodonTwitterLinkedInXING RSS
© 2022 Jakob Miksch • Imprint