How to import country administrative boundaries from OSM Planet to PostGIS Polygons

Question

I need to import all world countries as polygons or multipolygons into PostGIS. Specifically, I need the administrative boundaries that are like this orange bubble around the actual land borders.

I've already gone through all the motions that I thought would get me the result I wanted: a single polygon or multipolygon for each country. But once I get the data into PostGIS, the polygons just aren't there in the right places, and there are many more rows than I expected.

Here's how I download the planet-latest.pbf file, convert to o5m format, run osmfilter to get data with type=boundary and boundary=administrative and admin_level=2.

$ wget http://planet.openstreetmap.org/pbf/planet-latest.osm.pbf
$ osmconvert planet-latest.osm.pbf -o=planet-latest.o5m
$ time ./osmfilter planet-latest.o5m --keep="type=boundary and boundary=administrative and admin_level=2" -v -o=country_boundaries_3.o5m
    (ran 22m 14.162s)
$ time osmconvert country_boundaries_3.o5m --out-pbf >country_boundaries_3.pbf
    (ran 0m 26.276s)

Then I import into PostGIS:

# create database geocell_countries_3 with owner=geocell tablespace=postgres_huge;
# \c geocell_countries_3
# create extension postgis;
# create extension hstore;
# \q
$ time osm2pgsql --create --verbose --proj 4326 --hstore-all --database geocell_countries_3 /mnt/planet-latest/country_boundaries_3.pbf
    (ran: 2m 27.034s)

I copy the data into a new table:

$ psql geocell_countries_3
# create table country_boundaries (like planet_osm_polygon);
# insert into country_boundaries select * from planet_osm_polygon where admin_level = '2';
# alter table country_boundaries set schema g;
# select AddGeometryColumn ('g', 'country_boundaries', 'multipoly', 0, 'MULTIPOLYGON', 2);
# update g.country_boundaries set multipoly = ST_Multi(way);
# \q

Now there are 48 records just for USA but I just want one.

# select count(*) from g.country_boundaries where (tags -> 'ISO3166-1:alpha3') = 'USA';
 count
-------
    48
(1 row)

Answer 1

I was able to get this to work using the administrative boundaries dataset available here: https://mapzen.com/data/borders/

$ wget http://s3.amazonaws.com/osm-polygons.mapzen.com/planet_geojson.tgz
$ tar -zxvf planet_geojson.tgz

Since we want administrative level 2, use the file planet/admin_level_2.geojson for the next steps.

To import the GeoJSON file into PostGIS, use the ogr2ogr tool:

$ ogr2ogr -f "PostgreSQL" PG:"dbname=my_database user=postgres" "planet/admin_level_2.geojson" 

To specify the destination table use -nln destination_table. To append the data to an existing database use -append.

By default, the data is imported into a table called ogrgeojson, and the border geometry is contained in a field called wkb_geometry.

To see this, connect to your database using psql:

$ psql --host=<db host> --port=5432 --dbname=<db name> --username <username> --password

And run some queries:

gis_countries=> SELECT count(*) FROM ogrgeojson;
 count 
-------
   367
(1 row)

gis_countries=> SELECT "name:en" FROM ogrgeojson WHERE ST_Contains("wkb_geometry", ST_GeometryFromText('POINT(-74.003334 40.741431)', 4326));
         name:en          
--------------------------
 United States of America
(1 row)