5

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.

enter image description here

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)
  • 2
    Natural Earth cleaned up OSM countries you might want to use Admin0 naturalearthdata.com/downloads/10m-cultural-vectors/… – Mapperz Apr 21 '15 at 17:27
  • 1
    I've tried the Natural Earth data as well and been able to work with the data. However, as far as I know the polygons in Natural Earth are exact coastlines and political borders instead of the administrative boundaries signified by the orange bubble in the image above. That's what I really need. – Justin Schier Apr 21 '15 at 20:21
  • Maybe you can try osm.wno-edv-service.de/boundaries – stephan75 May 6 '15 at 19:05
  • Could be related? "the countries are rendered correctly. But there are also al lot of boundaries from provinces and municipalities (admin_level > 2) rendered" help.openstreetmap.org/questions/47932/… – bcattle Jul 28 '17 at 22:16
  • Are there other columns in your table? What is the output from select * from g.country_boundaries where (tags -> 'ISO3166-1:alpha3') = 'USA';? And what do the USA boundaries look like on a map? – amball Jul 30 '17 at 20:56
3

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)
  • Unfortunately, "Mapzen discontinued Borders" in 2016. Is there an alternative source? – Ortwin Gentz Feb 14 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.