Import OSM features into a PostGIS table

Importing spatial data from OpenStreetMap into a spatial database like PostGIS is a convenient way to leverage the 10 billion labelled geometries in OSM's database.

This tutorial quickly covers how one can download all items of a particular feature from OpenStreetMap (without downloading the planet file!) and load it into a PostGIS table for analysis.

You'll need credentials to your PostGIS installation, GDAL installed (check which ogr2ogr), and a free API key, you can get here.

Identifying the feature you want

You can use TagInfo and the OSM Wiki to identify the spatial features you want in your table. Ideally this will be a semantically homogeneous group of features stored in a table each: for example, you'd want one table restaurants to contain named restaurants (tagged amenity=restaurant&name=*), and a separate table commercial_landuse to cover landuse=commercial.

Note that TagInfo contains the count of features: you'll want to make sure you have enough space in your database to store everything. Budget at least 1KB per feature.

Downloading OSM data as GeoJSON

We can use our API to download OpenStreetMap as GeoJSON to pull features filtered on the tag above and load it into your PostGIS table. This API avoids the limitations (like throttling or query-took-too-long errors) that Overpass API often results in. You also get to avoid downloading the 67GB planet file and extracting it on a massive server on AWS!

You can find the API documentation for this endpoint here and can generate a free API key here.

In this tutorial, we're going to download every lake (water=lake) in Australia (bbox=111.533203,-44.590467,155.742188,-9.449062).

curl --get 'https://osm.buntinglabs.com/v1/osm/extract' \
     --data "tags=water%3Dlake" \
     --data "api_key=YOUR_API_KEY_HERE" \
     --data "bbox=111.533203,-44.590467,155.742188,-9.449062" \
     -o australian_lakes.geojson

This downloads about 10,000 lake geometries from Australia to australian_lakes.geojson locally using curl. We can validate that this is right by taking a look at the first lake using jq '.features[0]':

{
  "type": "Feature",
  "properties": {
    "natural": "water",
    "water": "lake"
  },
  "geometry": {
    "type": "MultiPolygon",
    "coordinates": [[[
      [ 132.3371674, -11.1236879 ],
      [ 132.3368751, -11.1236932 ],
      [ 132.33665779999998, -11.1237221 ],
      [ 132.33658, -11.1237721 ],
      [ 132.3365907, -11.1238616 ],
      [ 132.33688039999998, -11.1239485 ],
      [ 132.3370655, -11.1239248 ],
      [ 132.3372023, -11.123859 ],
      [ 132.3372774, -11.12383 ],
      [ 132.3372801, -11.1237379 ],
      [ 132.3372801, -11.1236906 ],
      [ 132.3371674, -11.1236879 ]
    ]]]
  }
}

Importing GeoJSON into PostGIS

We can upload this GeoJSON file directly into PostGIS by using GDAL / ogr2ogr.

You can pass your credentials directly as an argument to ogr2ogr:

ogr2ogr -f "PostgreSQL" \
  PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'" \
  "australian_lakes.json" \
  -append -nln YOUR_TABLE_NAME

Documentation for the PostGIS GDAL driver can be found here.

Recommended refresh rates

OpenStreetMap is a living database, with community editors continually adding new data and updating old features.

We recommend refreshing the data once every month to make sure you get the benefits of new data imports and fresh, more accurate edits.