This is a static archive of our old OpenStreetMap Help Site. Please post any new questions and answers at community.osm.org.

Merging 2 countries with osm2pgsql

4
2

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?

asked 26 May '11, 12:57

bestic2's gravatar image

bestic2
66133
accept rate: 0%


2 Answers:

5

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

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

which shares ways such as

https://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.

answered 26 May '11, 13:49

EdLoach's gravatar image

EdLoach ♦
19.5k16156280
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

3

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

answered 20 Feb '17, 10:18

ReckyXXX's gravatar image

ReckyXXX
4611
accept rate: 0%

Source code available on GitHub .