duplicate key error in slim mode about osm2pgsql HOT 10 CLOSED

Use osmconvert to merge multiple pbf files:

1. Download pbf files
2. Convert each pbf file to o5m file: osmconvert extract1.osm.pbf -o=extract1.o5m
3. Merge o5m files into one pbf file: osmconvert extract1.o5m extract2.o5m -o=merged.osm.pbf
4. Now you can import merged.osm.pbf with osm2pgsql in slim mode

from osm2pgsql.

You can import merged file as @iqqmuT says, but it will dropped already loaded map-data in PostgreDatabase and start new import process.

On the other hand, you can substract already existing data from new region file using:

osmconvert new_region.pbf -o=new_region.o5m
osmconvert already_loaded_map.pbf -o=already_loaded_map.o5m
osmconvert new_region.o5m --subtract already_loaded_map.o5m -o=new_region_cleaned.o5m
osmconvert new_region_cleaned.o5m -o=new_region_cleaned.pbf

And then you can just append new file to PostgreSQL using:
osm2pgsql --append ... new_region_cleaned.pbf
without duplicate key errors.

from osm2pgsql.

@mirabilos :

This doesn’t scale up to, say, five regions.

You can merge all new regions together in one file and then add it to postgresql using my method.
The idea is that you don't need to merge already loaded map file with them and wait for loading it anew.
Substracting with osmconvert is not very time consuming and using it with osm2pgsql --append instead of --create could save a lot of time.

from osm2pgsql.

By the way; this issue is with a self compiled osm2pgsql grabbed from git master. My system is debian wheezy

from osm2pgsql.

This is a well known issue and one that is likely not going to be addressed any time soon.

While the rendering tables allow multiple geometries per osm feature / id and therefore don't have a unique constraint on osm_id, the slim tables are used for update processing and thus need to do lookups based on osm_id, for which it has to be unique.

For performance reasons osm2pgsql uses the COPY command, that batches up inserts into a single command. As such, if there is a unique constraint (or duplicate Key) violation, the whole batch fails. As osm2pgsql does stream based processing, it does not have the ability to go back and reprocess that batch of nodes / ways / relations to properly deal with the duplicate entry. Therefore, it cannot recover from such a duplicate key failure and terminates.

Osm2pgsql therefore doesn't support importing multiple files into one database in slim mode if there are duplicate osm features. Instead, I would recommend merging the two files prior to importing with osm2pgsql. For example with osmosis that supports de-duplication on merge.

from osm2pgsql.

@apmon, can we close this, as importing multiple overlapping files in slim mode isn't something osm2pgsql does?

from osm2pgsql.

Yes, closing this, as it is not likely that we will support overlapping files in slim mode anytime soon. Given the duplicate key errors happen somewhere deep in the copy operations, osm2pgsql would have to catch them and then replay all statements in the failed copy operation. However, there is currently no concept to cache/buffer those statements to replay them, as everything is done in a memoryless streaming process.

from osm2pgsql.

@apmon can you point out some howto for:

• Import several extracts once (e.g. Belgium plus Nordrhein-Westfalen plus Rheinland-Pfalz plus Luxembourg)
• Keeping these up to date

Thanks! I’m trying to dig through the infos here, on the OSM wiki, on the switch2osm page, on Osmosis documentation, but I don’t find anything comprehensible to me for doing just that. I don’t think I’ve got the disc space needed for a full Europe extract right now.

By the way… what are the disc space requirements for those extracts from Geofabrik? I’m using up about 8 GiB for the entire program installation plus an imported Belgium extract already…

from osm2pgsql.

Ah, nice. Does that also work for the diffs?

from osm2pgsql.

from osm2pgsql.