How to add another OSM file via osm2pgsql?

[guyisra]

• What did you see? going through the process with a clean db and a specific osm.pbf file works and is able to generate tiles. Trying to run another osm2pgsql command with a different osm file (different region in the world) causes some corruption in the DB which gives errors when trying to pull tiles:

2018-06-17 16:39:00.371 IDT [2788] ERROR:  column "mz_label_placement" does not exist at character 2107

and in tileserver:

    raise DataFetchException(async_exceptions)
DataFetchException: column "mz_label_placement" does not exist
LINE 102:     mz_label_placement,
              ^
, column "mz_label_placement" does not exist
LINE 64:     mz_label_placement,
             ^
, column "mz_poi_min_zoom" does not exist
LINE 60:       WHEN mz_poi_min_zoom IS NOT NULL AND
                    ^
^C%

• What did you expect to see?

The osm files attempted are https://download.bbbike.org/osm/bbbike/NewYork/NewYork.osm.pbf and and http://download.geofabrik.de/europe/andorra-latest.osm.pbf

Loading each separately to a clean db works

Is there a different way to load new osm data to the db ?

[ImreSamu]

The osm files attempted are https://download.bbbike.org/osm/bbbike/NewYork/NewYork.osm.pbf and and http://download.geofabrik.de/europe/andorra-latest.osm.pbf

my best workaround is merging all osm input files into one.

• "osmium-merge - merge several sorted OSM files into one"

but be sure :

• they must be from the same data source
  • Merging "bbbike" and "geofabrik" sources probably not will work.
• and same "point in a time".

for example - merging same geofabrik(180601) versions should work:

• wget http://download.geofabrik.de/north-america/us/new-york-180601.osm.pbf
• wget http://download.geofabrik.de/europe/andorra-180601.osm.pbf
• osmium merge new-york-180601.osm.pbf andorra-180601.osm.pbf -o mytestdata-180601.osm.pbf

[guyisra]

Thanks Once this merged osm is in the DB, how would you update the db with new new information on a region?

[zerebubuth]

New data can be loaded into an existing Tilezen database using osm2pgsql's --append mode. Because the errors you're seeing are about missing column names, it looks like osm2pgsql was run in its default --create mode, which undid some of the schema changes from running ./perform-sql-updates.sh (and probably dropped any data that used to be in those tables anyway).

Append mode can be used to load additional data, although this is intended to be used with an osmChange file as input rather than a planet extract. One can turn a .osm.pbf into an osmChange file using osmium-tool although it might be quite slow for large files:

./osmium cat -f osc.gz -o output.osc.gz input.osm.pbf

It's also worth noting that append mode can be considerably slower than create mode, as osm2pgsql can't make use of certain optimisations when it knows it's loading into an empty table (e.g: COPY FROM).

There are a few extra things which need to be run when finishing off an "append". First, we keep a table of roads which need an extra update step to take account of relations they're part of, so one needs to run:

BEGIN;

UPDATE planet_osm_line
  SET mz_road_level = NULL
  WHERE osm_id IN (SELECT DISTINCT osm_id FROM mz_pending_path_major_route);

TRUNCATE mz_pending_path_major_route;

COMMIT;

We run this after osm2pgsql as part of our minutely update script (called :post_import in that script). Note that setting mz_road_level to null just forces the trigger to recalculate it post-update, now that any relations have been written or changed.

Having said all of that, just because one can use append mode doesn't mean that it's the most appropriate way in all circumstances. As noted, it can be much slower than a from-scratch load. My recommendations:

• As @ImreSamu said, if the extracts are all known ahead of time, it's much faster to merge them into one using Osmosis or osmium-tool before loading into the database.
• I often need to load several extracts for testing purposes, but I generally do these as separate databases - for example, I currently have databases called california, england and puertorico. This means I can load and drop each separately, and don't need to worry about sourcing the extracts from the same source at the same time. This might not be suitable if you want to generate one set of tiles which contains data from several extracts.
• If it's really necessary to add extracts one-by-one, then load the biggest one first as an .osm.pbf, followed by the smaller ones converted to .osc.gz.

Hope that helps!