How to create a world-wide PostgreSQL database of administrative regions

The GADM database contains geographical data for administrative regions, e.g. countries, regions and municipalities. As always, once you have the data in the right format, it is easy to import it into a database. The data is available from GADM in several formats. All data has the coordinate reference system in longitude/latitude and theWGS84 datum.

Step-by-step:

  1. Download data for the whole world or by country. For a change, I will use the GeoPackage format.
  2. Create a PostgreSQL database (assumed to exist)
  3. Import the data with ogr2ogr (see instructions below)

Import data instructions

Download data (example for Denmark):

wget http://biogeo.ucdavis.edu/data/gadm2.8/gpkg/DNK_adm_gpkg.zip
unzip DNK_adm_gpkg.zip

Next, create a database called "gadm" on my local PostgreSQL server; of course you can use another name if you prefer. Install the PostGIS extension:

create extension postgis

Finally, use ogr2ogr with the GPKG (GeoPackage) driver to import the data:

ogr2ogr -f PostgreSQL "PG:dbname=gadm" DNK_adm.gpkg

Now the data is imported an ready to be queried.

As a test, we can query the adm2 table (municipalities) with a coordinate inside the municipality of Copenhagen, Denmark.

SELECT name_2, ST_AsText(wkb_geometry)
FROM dnk_adm2
WHERE ST_Intersects(ST_SetSRID(ST_Point(12.563585, 55.690628), 4326), wkb_geometry)
-- AND ST_Point(12.563585, 55.690628) && wkb_geometry

You can view the selected well-known string geometry (WKT) in an online viewer, such as openstreetmap-wkt-playground. Other viewers are listed on stackexchange.

Alternative sources

For this post I really wanted a dataset of populated/urban areas. However, the GADM data I downloaded only contains adm0-adm2, which is a tessellation of the land area, i.e. cannot be used to discriminate between urban and rural areas.

Other data sources are listed below:

- http://www.naturalearthdata.com/downloads/
- https://data.humdata.org
- https://freegisdata.rtwilson.com/

From the rtwilson list, here are some specific datasets that indicate population density and urbanism:

- http://sedac.ciesin.columbia.edu/data/collection/gpw-v4/sets/browse
- https://www.eea.europa.eu/data-and-maps/data/urban-morphological-zones-2000-2
- http://www.worldpop.org.uk/ (does not cover Europe and North America)
- https://nordpil.com/resources/world-database-of-large-cities/

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.