Creating a Local OSM Extract

Your own little OpenStreetMap database

Overview

• Introduction
• OSM Data
• Import Scenarios
• Keeping it Up to Date
• Using it
• Questions

OpenStreetMap Data

What is OSM?

“The Wikipedia of Maps”

OSM is a dataset, which is…

• global
• open
• used to map nearly anything
• one of my favorite things

Is anyone actually using it?

• Amazon Logistics
• Tesla Smart Summon feature
• Esri (basemaps, feature services)
• Pokemon Go
• Red Cross
• Kendall County!

The point here is that there’s clearly something of value in this dataset, if you can just get at it.

OpenStreetMap Data:
It’s weird!

The OSM dataset has:

• NO layers
• NO schema
• NO polygons

The Structure

OSM data is made up of:

1. Nodes
2. Ways (ordered series of nodes)
3. Relations (collection of other elements)

Let’s look at an example!

Looking at it: Nodes

{
  "elements": [
    {
      "type": "node",
      "id": 6650982353,
      "lat": 41.6620246,
      "lon": -88.5328721,
      "tags": {
        "direction": "forward",
        "highway": "stop"
      }
    },
    {
      "type": "node",
      "id": 235170089,
      "lat": 41.6630576,
      "lon": -88.5327984,
      "tags": {
        "railway": "crossing",
        "crossing:bell": "yes"
      }
    },
    {
      "type": "node",
      "id": 235170094,
      "lat": 41.6637334,
      "lon": -88.5327199
    }
  ]
}

Here’s a pretend response from the OSM API, with the data as JSON. Note that each node has an id, lat, lon, and may or may not include a list of key-value tags. These define what a feature actually is. So what’s the point of a node with no attributes?

Looking at it: Ways

{
  "elements": [
    {
      "type": "way",
      "id": 21814608,
      "nodes": [
        6650982353,
        235170089,
        235170094
      ],
      "tags": {
        "highway": "tertiary",
        "name": "Lew Street"
      }
    },
    {
      "type": "node",
      "id": 6650982353,
      "lat": 41.6620246,
      "lon": -88.5328721,
      "tags": {
        "direction": "forward",
        "highway": "stop"
      }
    },
    {
      "type": "node",
      "id": 235170089,
      "lat": 41.6630576,
      "lon": -88.5327984,
      "tags": {
        "railway": "crossing",
        "crossing:bell": "yes"
      }
    },
    {
      "type": "node",
      "id": 235170094,
      "lat": 41.6637334,
      "lon": -88.5327199
    }
  ]
}

Let’s introduce a way. Notice that we can have different kinds of geometry and entirely different sets of attributes for these features, but they’re together in a single layer. For those familiar, this is a lot like what you see when you open up the raw text of a GeoJSON or KML.

To be clear: ways do not have their own geometry. They have a list of participating nodes. That is why a node with no attributes is acceptable: it may be a component part of a way.

Looking at it: Areas

{
  "elements": [
    {
      "type": "way",
      "id": 21814608,
      "nodes": [
        6650982353,
        235170089,
        235170094,
        235170095,
        6650982353
      ],
      "tags": {
        "building": "office",
        "name": "City Hall"
      }
    }
  ]
}

A “polygon” is really just a way that ends with the same node as its beginning. It is a closed way. Whether or not it represents an area depends on the tags, though.

This seems like an area, because it has the building tag on it.

Looking at it: Areas?

{
  "elements": [
    {
      "type": "way",
      "id": 21814608,
      "nodes": [
        6650982353,
        235170089,
        235170094,
        235170095,
        6650982353
      ],
      "tags": {
        "barrier": "wall"
      }
    }
  ]
}

And what about now? Probably not, since it’s a wall. But What if we were talking about a castle wall, mapped out at an incredibly large scale? Maybe! Though we’d expect to see something like area=yes to clear up any ambiguity there.

Some tags are “safe” to assume represent areas / lines, but this requires certain familiarity with the tagging conventions of OSM.

Looking at it: Relations

{
  "elements": [
    {
      "type": "relation",
      "id": 10004268,
      "members": [
        {
          "type": "way",
          "ref": 21814608,
          "role": ""
        }
      ],
      "tags": {
        "name": "Plano City Road 1",
        "network": "US:IL:Plano",
        "type": "route",
        "route": "road"
      }
    },
    {
      "type": "way",
      "id": 21814608,
      "nodes": [
        6650982353,
        235170089,
        235170094,
        235170095,
        6650982353
      ],
      "tags": {
        "barrier": "wall"
      }
    }
  ]
}

Relations are… diverse, and possibly complicated. The type tag is critical here, as it tells you what to expect from the relation.

The relation is comprised of a list of members. It can hold nodes, ways, and even other relations! Depending on the relation, the role is going to vary quite a bit.

In this example, it’s a road route relation. Route members don’t need a role most of the time.

Looking at it: Relations

{
  "elements": [
    {
      "type": "relation",
      "id": 10004268,
      "members": [
        {
          "type": "way",
          "ref": 21814608,
          "role": "outer"
        },
        {
          "type": "way",
          "ref": 21814722,
          "role": "inner"
        },
        {
          "type": "way",
          "ref": 21815110,
          "role": "inner"
        },
        {
          "type": "way",
          "ref": 21814600,
          "role": "inner"
        }
      ],
      "tags": {
        "building": "yes"
      }
    }
  ]
}

Here’s another example: a multipolygon! Specifically, a building which has a number of courtyards within a larger structure. Because this feature represents an area with holes in it, we use multiple ways in a relation. The main outline has the role outer, the holes, inner.

That is the tip of the iceberg on relations, but just know that they can be quite complex. During our extract process, relations generally need to be handled per type if there’s something specific you want out of them.

So how does this…

…turn into this:

Getting the Data

Sources

• OSM Wiki - Downloading Data

• Geofabrik extracts

mkdir -p data/cache
cd data
wget -N https://download.geofabrik.de/north-america/us/illinois-latest.osm.pbf

If you’re on Windows, wget won’t work. But you can run the command via docker as well. Just make sure you’re in the right directory!

Prep the Data

Osmium

A “swiss army knife” for OSM data.

• Extracting geographic subset

• Getting metadata about objects
• Extract by attribute
• Generate / apply change files
• and more!

Audience
Participation!

geojson.io

Just to prove that this is fast, easy, and live, we can define a custom AOI based on an audience suggestion of a place name.

Extracting with Osmium

osmium extract \
  -p /app/city.geojson \
  /app/data/illinois-latest.osm.pbf \
  -o /app/data/city-extract.osm.pbf --overwrite \
  -s smart -S types=any

Now that we have our AOI, we can execute this command. What is it doing? 1. Uses the AOI as the -p parameter to set the geographic extent of the data 2. Runs against our Geofabrik file 3. Outputs to a new file, overwriting anything that may already exist. 4. Uses the smart method to deal with features that straddle the boundary of our AOI - You can read more about this in the osmium manual - Basically, any feature that has any part in our AOI will be included in full. We like to cast a wide net.

Importing with imposm

Pros / Cons

• Easy to use and configure
• Built in tag “cleaning”
• Table generalization
• AOI filtering on import and update

• Relations are harder to work with
• No advanced geometry processing or configuration
• AOI does not apply to relations
• Development seems to have plateaued

Tag cleaning meaning yes/no values to boolean fields, numeric values to number fields, etc. Generalization - for a given table, simplify geometries and even drop features smaller than a certain size; useful for small scales, or for projects where you will be making multi-scale outputs like atlases, etc

Table Config

Mapping OSM data to tables is done using a YAML config file.

highways-mapping.yml

tables:
  roads:
    columns:
    - name: osm_id
      type: id
    - name: the_geom
      type: geometry
    - name: type
      type: mapping_value
    - key: name
      name: name
      type: string
    filters:
      reject:
        area: ["yes"]
    mapping:
      highway:
      - motorway
      - trunk
      - primary
      - secondary
      - tertiary
      - residential
    type: linestring

So, here we can define the fields and their types. Note that we can tell it to directly grab a key from the features.

At the bottom, you’ll also find a mapping list. That list acts as a filter, selecting only features that have the listed values in the specified keys. And the mapping_value attribute can pipe these values into a field as well.

Importing: Single Table

imposm import \
  -mapping /app/imposm-scenarios/highways-mapping.yml \
  -read /app/data/city-extract.osm.pbf \
  -cachedir /app/data/cache/highways \
  -write -connection postgis://gis:gis@database:5432/osm?prefix=state_highways \
  -deployproduction \
  -optimize \
  -overwritecache

Naturally, the name of the imposm command is import. In this command, we are telling imposm:

1. Use the extract we made with osmium. Technically, reading and writing can be done as separate steps, but we’ll just be doing it all at once.
2. Deploy the imported data to the public schema of our database. The initial import goes into an import schema. If you’re going to be doing production stuff directly out of the database, you may want this as a separate step, to ensure that the imported data is okay before swapping it in. Also note that if the public schema already has stuff in it, those get rotated to a backup table, so you can always undo it later.
3. Spatial indexes will automatically be created and updated, DB will be vacuumed and analyzed.
4. Write the data to our database
5. Overwrite any existing cache

Importing: Single Table

imposm import \
  -config /app/imposm-scenarios/highways-config.json \
  -read /app/data/city-extract.osm.pbf \
  -write \
  -deployproduction \
  -optimize \
  -overwritecache

highways-config.json

{
    "cachedir": "/app/data/cache/highways",
    "mapping": "/app/imposm-scenarios/highways-mapping.yml",
    "connection": "postgis://gis:gis@database:5432/osm?prefix=state_highways"
}

It’s entirely up to you if you use a config file or just write it all into the command line. The config file can be particularly useful if you’re working with multiple extracts and you use the -appendcache flag.

For instance, if you’re on the state line and want to include OSM data from a neighboring state, this can simplify the process of importing data from disparate Geofabrik files into a single set of tables.

Importing: Multiple Tables

imposm import \
  -config /app/imposm-scenarios/city-parks-config.json \
  -read /app/data/city-extract.osm.pbf \
  -deployproduction -optimize -write -overwritecache

In this example, we’re using a different mapping file, one that’s got over 500 lines in it, and a bunch of different layers.

Importing: EVERYTHING

And prepping for future updates!

imposm import \
  -config /app/imposm-scenarios/city-all-config.json \
  -read /app/data/city-extract.osm.pbf \
  -deployproduction -optimize -write -overwritecache \
  -diff

city-all-config.json

{
    "cachedir": "/app/data/cache/city-all",
    "mapping": "/app/imposm-scenarios/city-all-mapping.yml",
    "connection": "postgis://gis:gis@database:5432/osm?prefix=city_all",
    "diffdir": "/app/data/diff"
}

Everything?

city-all-mapping.yml

tags:
  load_all: true
  exclude: [created_by, source, "tiger:*"]
tables:
  nodes:
    mapping:
      __any__: [__any__]
    columns:
    - name: osm_id
      type: id
    - name: the_geom
      type: geometry
    - name: tags
      type: hstore_tags
    type: point

The keyword __any__ in the mapping tells imposm to grab any key and any value. In other contexts, this can be a useful tool, like getting anything with a value in highway, or anything with the value demolished.

When we ask for everything, we can use the hstore_tags field type to hold them.

In this example, I’m specifically excluding tags that I consider worthless. Alternatively, you could specify which tags to include instead, rather than loading everything.

Importing With osm2pgsql

Pros / Cons

• Well established, continued development
• Extremely configurable
• Allows geometry processing “mid-stream”
• Exports to many coordinate systems
• Decent default config
• Lots of examples

• Updating requires a replication URL, not easily limited to AOI
• Custom configuration can be harder to adjust / understand
• Built-in generalization is experimental

This is what we actually use at Kendall, particularly for the projections and the geometry processing.

If you just want to get right to playing with OSM data, you can run osm2pgsql without any config file and it will use default settings to create a whole bunch of tables.

Importing: Defaults

osm2pgsql /app/data/city-extract.osm.pbf \
  -d postgres://gis:gis@database:5432/osm

Take the plunge!

The osm2pgsql maintainers strongly encourage using the flex output, especially if you’re just starting out using it. The default output does not always take advantage of new capabilities.

Flex Output

Mapping to tables is done with a Lua file.

pois.lua

local pois = osm2pgsql.define_table({
    name = 'pois',
    ids = { type = 'any', type_column = 'osm_type', id_column = 'osm_id' },
    columns = {
        { column = 'name' },
        { column = 'class', not_null = true },
        { column = 'subclass' },
        { column = 'tags', type = 'jsonb'},
        { column = 'geom', type = 'point', not_null = true },
  }
})

function process_poi(object, geom)
    local a = {
        name = object.tags.name,
        geom = geom,
        tags = object.tags
    }

    if object.tags.amenity then
        a.class = 'amenity'
        a.subclass = object.tags.amenity
    elseif object.tags.shop then
        a.class = 'shop'
        a.subclass = object.tags.shop
    else
        return
    end

    pois:insert(a)
end

function osm2pgsql.process_node(object)
    process_poi(object, object:as_point())
end

function osm2pgsql.process_way(object)
    if object.is_closed then
        process_poi(object, object:as_polygon():centroid())
    end
end

The Flex output is way more involved, and can do things from simple to very very complex.

First, we define our tables as a series of locals, and osm2pgsql’s built-in define_table method.

Next, we define the function we use to process an object. In it, we: - assign attributes - check if it’s a shop or amenity - assign class and subclass, or just skip - add that object to the poi table

Note that it takes two parameters, an object, and a geometry.

Then, we use that function inside of our two processing functions, process_node and process_way. These are built-in functions that osm2pgsql will look for when it runs an import and call the function for each element in the input data file.

process_node is simple, it just throws the node at our function.

process_way is more interesting. It checks a built-in property, is_closed first, and if the way is closed, it calls the function, sending the centroid of the closed way. Neat! Also really useful, because OSM mapping of businesses is not consistent.

Importing: POIs

osm2pgsql \
  -j /app/data/city-extract.osm.pbf \
  -d postgres://gis:gis@database:5432/osm \
  -O flex -S /app/osm2pgsql-scenarios/pois.lua

We’re calling the program together with our input data, our database connection, and the last line, telling it to use the flex output, with our style file from earlier.

Importing: Road Routes

routes.lua

local ways = osm2pgsql.define_table({
    name = 'routes',
    ids = { type = 'way', id_column = 'osm_id' },
    columns = {
        { column = 'id', sql_type = 'serial', create_only = true },
        { column = 'ref' },
        { column = 'name' },
        { column = 'network' },
        { column = 'tags', type = 'jsonb' },
        { column = 'the_geom', type = 'multilinestring', not_null = true}
    }
})

function osm2pgsql.process_relation(object)
    if object.tags.route == 'road' then
        ways:insert({
            tags = object.tags,
            the_geom = object:as_multilinestring():line_merge(),
            ref = object.tags.ref,
            name = object.tags.name,
            network = object.tags.network
        })
    end
end

As before, we define the table first. I like to call particular attention to line 8. hstore certainly works, but postgres also has a datatype jsonb which functions similarly enough for our purposes, and requires no additional extensions to work with.

In this case, we don’t need any extra function, we can just put our process entirely into the process_relation function.

Also, I’m using a method here, line_merge(). This takes all the relation members and puts them into a single multiline feature. I like this for route relations because they can be made up of many, many small pieces, and labeling them is annoying. Make them into single features, no more problem!

osm2pgsql \
  -j /app/data/city-extract.osm.pbf \
  -d postgres://gis:gis@database:5432/osm \
  -O flex -S /app/osm2pgsql-scenarios/routes.lua

Importing: Everything

osm2pgsql \
  -j /app/data/city-extract.osm.pbf \
  -d postgres://gis:gis@database:5432/osm \
  -O flex -S /app/osm2pgsql-scenarios/city-all.lua

All nodes, ways, and some relations¹ are dumped into tables with all their tags.

As noted before, you really need to know what you want out of a relation before just dumping it into a table. There are a handful of types we’re interested in that can reliably be treated as lines or polygons. You could easily extend this to take things like transit stops and move them to the nodes table (though these often have their own tagging on them already).

There are also ways of processing a relation and pulling in member information so that your output can include route information from the relation as well as member information like the individual road segment’s name, max speed, etc.

Your use case will help determine what you need, and whether it’s worth the trouble.

1. Routes, waterways, boundaries, and multipolygons.

“Everything” Tables

Why Would I Want This?

• Schema remains as flexible as OSM itself
• As use cases change, only the queries need to be modified, not the data
• No re-importing / re-indexing
• Query output can be used identically to any SQL table using >> data accessor
• Can use queries to populate Materialized Views in postgres

I’m quite fond of having tables that just hold all the tags in a single field. I used to spend a lot of time getting my config files just right, only to decide a week later that there was another field, feature type, or use case that I wanted to fold into my process. In those days, that meant re-running the entire import process and interrupting anything using the data.

Nowadays, the data’s all just there. If I want a new field in my roads layer, I adjust the SQL query and it’s there.

Now, this does involve an uptick in storage space. The larger your AOI, the more you’re going to “pay” for using hstore/jsonb data. But for small areas, this really isn’t a major concern. If you’re working with regular updates and a replication URL, processing times will be a lot longer.

Updating

The Easy Way

Just re-run the import steps whenever a new extract is available from the original source!

For GeoFabrik, this means your data is only a day or so behind the main OSM dataset, depending on:

1. When the extract was “cut”
2. When you ran your import

The Automatic Way

• Throw all your commands into a script
• Add in a wget or curl command to download extract file
• Schedule the script to run every 24 hours

Really up to date: imposm

As long as you included -diff in the original import command, updating with imposm simple:

Use imposm run along with the same properties or config file as before.

imposm run -config /app/imposm-scenarios/city-all-config.json

Really up to date: osm2pgsql

Updating with osm2pgsql needs a replication URL. It works great with the diffs at planet.osm.org.

Issues

• That URL is for global changes
• Replication URLs for Geofabrik exist, but they are daily, not minutely
• Setting up a custom replication URL is not a trivial thing

To limit the changes to your area of interest is possible, but requires some custom coding to work.

If “live” data is needed, probably just use imposm.

Using It

jsonb columns

Using the column ->> 'key' syntax returns the value for the specified key.

SELECT
  osm_id,
  the_geom,
  tags ->> 'cuisine' as cuisine,
  tags ->> 'name' as name
FROM osm2pgsql_nodes
WHERE tags ->> 'amenity' = 'restaurant'

Sample Queries

traffic_roads.sql

SELECT
  osm_id,
  the_geom,
  tags -> 'name' name,
  tags -> 'highway' class
FROM osm2pgsql_ways
WHERE tags -> 'highway' IN (
  'primary',
  'secondary',
  'tertiary',
  'motorway',
  'trunk',
  'unclassified',
  'residential',
  'service'
)

SELECT
    osm_id,
    the_geom,
    COALESCE(tags -> 'natural', tags->'landuse') type,
    tags
FROM osm2pgsql_areas
WHERE COALESCE(tags -> 'natural', tags->'landuse') IS NOT NULL

cycling_infrastructure.sql

select
    osm_id,
    the_geom,
    tags -> 'name' name,
    case
        when tags -> 'highway' in ('cycleway', 'footway', 'path') then 'separated'
        else 'on road'
    end class,
    tags -> 'highway' type,
    tags -> 'bicycle' bicycle_access
from osm2pgsql_ways
where tags -> 'bicycle' != 'no'
or tags -> 'cycleway' = 'lane'

charging_stations.sql

select
  osm_id,
  the_geom,
  tags -> 'access' access
from osm2pgsql_nodes
where tags -> 'amenity' = 'charging_station'

What to Do

Honestly, once you write the query, you can use the results for anything that you would a normal table, including:

• Publishing a feature service to the web
• Generating vector tiles
• Using as input in geoprocessing
• Create an ArcGIS Locator dataset
• Create a routable graph network

It was noted earlier, but if you’re going to be providing map layers, services, etc., directly from this database, consider writing your query to a Materialized View for a performance boost.

Just don’t forget to refresh them now and then.

Questions?

Be in Touch!

josh@jcarlson.page

https://jcarlson.page

@jcarlson@mapstodon.space