Merging 2 countries with osm2pgsql

Question

I have some issues while i try to append new country (Ukraine) to already imported one (US).

First I created db with US OSM data:

osm2pgsql -l --create --database gis --username gis --prefix planet --slim --cache 2048 united_states.osm.bz2

Then I tried to append ukraine.osm.bz2:

osm2pgsql -l --append --database gis --username gis --prefix planet --slim --cache 2048 ukraine.osm.bz2

And received:

Going over pending relations
COPY_END for COPY planet_rels FROM STDIN;
 failed: ERROR:  duplicate key value violates unique constraint "planet_rels_pkey"
CONTEXT:  COPY planet_rels, line 61: "60189     1       481     {27503927,96165454,96165453,62055667,43761343,100453556,43764881,62055669,44097114,77755..."

Files with countries were taken from download.cloudmade.com

What is wrong in my actions and is there any way to import 2 countries information into DB?

Accepted Answer

5

The relation in your error message is the border of the Russian Federation:

http://www.openstreetmap.org/browse/relation/60189

which shares ways such as

http://www.openstreetmap.org/browse/way/50752242

with the border of the USA.

I am not an expert, but suspect you might need to use Osmosis to merge the two files first and then import using osm2pgsql in one go.

permanent link

answered 26 May '11, 13:49

EdLoach ♦
19.5k●16●156●280
accept rate: 22%

1

Thanks! It's really 1 common relation between 2 countries - 60189 id in planet_rels. I temporary dropped constraint before appending and all goes well

(26 May '11, 15:43) bestic2

Can both of you please share ways how to do this (@EdLoach how to use Osmosis for that, bestic2 how to temporarily drop the constraints)? I’ve got similar issues…

(24 Jan '15, 21:23) mirabilos

Answer 2

Use osmconvert.

You have 2 choices:

1) Merge new region file with existing map file:

osmconvert new_region.pbf --out-o5m | osmconvert.exe - already_loaded_map.pbf -o=map_with_new_region.pbf

and then load merged file to postgreSQL with cleaning database:

osm2pgsql --create ... map_with_new_region.pbf

2) Or separate 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

Merging 2 countries with osm2pgsql

Follow this question

Related questions