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:
- Download data for the whole world or by country. For a change, I will use the GeoPackage format.
- Create a PostgreSQL database (assumed to exist)
- 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/