Loading OSM Data into PostGIS with osm2pgsql Flex

Define your own PostGIS schema in Lua and load an OSM extract into it, in a shape that a minutely diff can still update afterwards.

Prerequisites

Conceptual minimum

osm2pgsql has two output backends. The legacy pgsql backend writes a fixed set of tables with a fixed column choice; the flex backend hands you a Lua file and executes it. Everything about the resulting schema comes from that file.

How an osm2pgsql flex style file produces and later updates a table A four-stage chain. define_table declares a name, an ids block and columns, and the ids block is what makes the table updatable. A process_node, process_way or process_relation callback runs once per object and decides what to insert. The insert call writes a row, or nothing at all, since silence is a valid outcome. Later, an append run applies a diff by finding the row by identifier, which only works when the ids block is present. The Lua style file is the schema — osm2pgsql only executes it define_table name · ids · columns ids is what makes it updatable process_node/way/relation one callback per type you decide what inserts insert a row, or nothing silence is a valid outcome --append later diff finds the row by id only with an ids block Everything about the resulting schema — table names, column types, which objects become rows — lives in the style file, not in the tool.
The tool contributes the streaming and the middle tables. Every decision about shape is yours, which is the point of flex and the reason a typo in a tag name looks exactly like an empty region.

A style file does two things. It declares tables with osm2pgsql.define_table, and it supplies callbacks — process_node, process_way, process_relation — that run once per object and decide whether to insert a row. An object your callbacks ignore simply does not appear, which is the intended behaviour and the reason a misspelled tag key produces an empty table rather than an error.

The one declaration that is easy to treat as bookkeeping and is not is ids.

The five ids options in a flex table and what each permits A grid of five ids declarations. Omitting ids stores nothing and produces a write-once table an append run cannot update. Type node stores the node identifier and supports updates for node-derived rows. Type way stores the way identifier. Type area stores a way or relation identifier as a signed number and is the usual choice for polygon tables. Type any stores a type character plus the identifier and suits mixed-source tables. The ids block decides what a later diff can do what it stores can --append update it? ids omitted nothing no — write-once table type = 'node' node id yes, for node-derived rows type = 'way' way id yes, for way-derived rows type = 'area' way or relation id, signed yes — the usual choice for polygons type = 'any' type char + id yes, mixed-source tables An area table stores relation identifiers as negative numbers, which is why osm_id on a polygon table is signed and why a naive join against a node table silently matches nothing.
Relation-derived areas are stored with a negative identifier. A join written without accounting for that returns nothing and raises nothing.

Without an ids block the table is write-once: osm2pgsql --append has no way to find the rows belonging to a modified object, so it leaves the table alone and reports nothing. This is the single most common reason a diff-updated database stops tracking one of its tables while the others keep working.

Runnable solution

A style file producing three tables — points of interest, a road network, and building polygons — all of them updatable:

lua 
-- osm.lua — flex style file. Every table here is updatable by --append.
local srid = 4326

local pois = osm2pgsql.define_table({
  name = 'poi',
  ids = { type = 'node', id_column = 'osm_id' },
  columns = {
    { column = 'name',     type = 'text' },
    { column = 'category', type = 'text', not_null = true },
    { column = 'tags',     type = 'jsonb' },
    { column = 'geom',     type = 'point', projection = srid, not_null = true },
  },
})

local roads = osm2pgsql.define_table({
  name = 'road',
  ids = { type = 'way', id_column = 'osm_id' },
  columns = {
    { column = 'name',     type = 'text' },
    { column = 'highway',  type = 'text', not_null = true },
    { column = 'oneway',   type = 'bool' },
    { column = 'maxspeed', type = 'int' },
    { column = 'tags',     type = 'jsonb' },
    { column = 'geom',     type = 'linestring', projection = srid, not_null = true },
  },
})

local buildings = osm2pgsql.define_table({
  -- 'area' covers both closed ways and multipolygon relations; relation ids
  -- arrive negative, which is why osm_id is signed.
  name = 'building',
  ids = { type = 'area', id_column = 'osm_id' },
  columns = {
    { column = 'name',    type = 'text' },
    { column = 'kind',    type = 'text', not_null = true },
    { column = 'levels',  type = 'int' },
    { column = 'tags',    type = 'jsonb' },
    { column = 'geom',    type = 'multipolygon', projection = srid, not_null = true },
  },
})

-- Tags that describe the object rather than the thing: never worth a column.
local uninteresting = {
  'source', 'source:date', 'attribution', 'created_by', 'note', 'fixme',
  'odbl', 'import', 'converted_by',
}

local function clean(tags)
  for _, key in ipairs(uninteresting) do tags[key] = nil end
  return tags
end

local POI_KEYS = { 'amenity', 'shop', 'tourism', 'healthcare', 'office' }

local function poi_category(tags)
  for _, key in ipairs(POI_KEYS) do
    if tags[key] then return key .. '=' .. tags[key] end
  end
  return nil
end

-- "30 mph" and "50" both have to become an integer in one unit.
local function speed_kmh(value)
  if not value then return nil end
  local n, unit = value:match('^(%d+%.?%d*)%s*(%a*)$')
  if not n then return nil end                        -- "walk", "RO:urban", ";50"
  n = tonumber(n)
  if unit:lower() == 'mph' then return math.floor(n * 1.609344 + 0.5) end
  if unit == '' or unit:lower() == 'km/h' then return math.floor(n + 0.5) end
  return nil
end

function osm2pgsql.process_node(object)
  local category = poi_category(object.tags)
  if not category then return end
  pois:insert({
    name     = object.tags.name,
    category = category,
    tags     = clean(object.tags),
    geom     = object:as_point(),
  })
end

function osm2pgsql.process_way(object)
  if object.tags.building and object.is_closed then
    buildings:insert({
      name   = object.tags.name,
      kind   = object.tags.building,
      levels = tonumber(object.tags['building:levels']),
      tags   = clean(object.tags),
      geom   = object:as_multipolygon(),
    })
    return
  end
  if object.tags.highway then
    roads:insert({
      name     = object.tags.name,
      highway  = object.tags.highway,
      oneway   = object.tags.oneway == 'yes' or object.tags.oneway == '1',
      maxspeed = speed_kmh(object.tags.maxspeed),
      tags     = clean(object.tags),
      geom     = object:as_linestring(),
    })
  end
end

function osm2pgsql.process_relation(object)
  if object.tags.type == 'multipolygon' and object.tags.building then
    buildings:insert({
      name   = object.tags.name,
      kind   = object.tags.building,
      levels = tonumber(object.tags['building:levels']),
      tags   = clean(object.tags),
      geom   = object:as_multipolygon(),
    })
  end
end

The load itself, then the indexes:

bash 
createdb osm && psql -d osm -c 'CREATE EXTENSION postgis;'

osm2pgsql --create --slim \
  --output=flex --style=osm.lua \
  --cache=8000 --number-processes=4 \
  -d osm ireland.osm.pbf

psql -d osm <<'SQL'
CREATE INDEX road_geom_idx     ON road     USING GIST (geom);
CREATE INDEX building_geom_idx ON building USING GIST (geom);
CREATE INDEX poi_geom_idx      ON poi      USING GIST (geom);
CREATE INDEX road_highway_idx  ON road     (highway);
ANALYZE road; ANALYZE building; ANALYZE poi;
SQL

Step-by-step walkthrough

define_table is evaluated once, at startup, and creates the table if it does not exist. not_null = true on the geometry column is worth setting on every table: it turns “this object had no usable geometry” from a null row into a loud error at insert time, which is where you want to find out.

process_way handles the fact that a closed way tagged building is a polygon while a way tagged highway is a line, and that these are different tables. The return after the building insert matters — a closed way carrying both tags would otherwise land in both tables, which is occasionally what you want and usually a bug.

speed_kmh is the kind of normalisation that belongs in the style file rather than downstream, because it happens once at load rather than on every query. It also demonstrates the honest failure mode: values it cannot parse become null rather than a guess, following the same provenance discipline as Batch Attribute Mapping Strategies.

process_relation catches multipolygon buildings. Without it, every building with a courtyard is missing from the table, because a relation is not a way and process_way never sees it — the same asymmetry described in Understanding OSM Multipolygon Relations for GIS.

Seconds per stage of an osm2pgsql flex import A bar chart of a 1.2 gigabyte country import into an empty PostGIS database. Reading and parsing the PBF takes 210 seconds. The Lua callbacks take 340 seconds. Bulk COPY into the tables takes 190 seconds. Writing the slim middle tables takes 420 seconds, the price of updatability. Index creation, clustering and analyze take 880 seconds and run after the load rather than during it. Where the time goes in a flex import country extract, 1.2 GB, into an empty PostGIS database read + parse the PBF 210 s · unavoidable Lua callbacks 340 s · your code, per object COPY into the tables 190 s · bulk path middle tables (slim) 420 s · the price of updatability index + cluster + analyze 880 s · after the load, not during Index construction dominates and is the one stage that must not overlap the load — building it during the insert costs roughly three times as much.
Two of these five are optional. Dropping slim mode saves seven minutes and gives up the ability to apply a diff ever again.

--cache=8000 gives the node cache eight gigabytes; too little and the load thrashes, too much and the machine swaps. --slim writes the middle tables that make --append possible later.

Verification

Three checks, in order of how quickly they fail:

sql 
-- 1. Every table has rows, and the geometry is the type you declared.
SELECT 'road' AS t, count(*), ST_GeometryType(geom) AS gt FROM road GROUP BY 1,3
UNION ALL SELECT 'building', count(*), ST_GeometryType(geom) FROM building GROUP BY 1,3
UNION ALL SELECT 'poi', count(*), ST_GeometryType(geom) FROM poi GROUP BY 1,3;

-- 2. The middle tables exist — without them --append is a no-op.
SELECT tablename FROM pg_tables WHERE tablename LIKE 'planet_osm_%';

-- 3. Relation-derived buildings arrived, and they are the negative ids.
SELECT count(*) FILTER (WHERE osm_id < 0) AS from_relations,
       count(*) FILTER (WHERE osm_id > 0) AS from_ways
FROM building;

The third query is the one that catches a missing process_relation: a real country extract has buildings from relations in the low single-digit percentages, and a count of exactly zero means they were never inserted.

Then prove the table is updatable before you rely on it:

bash 
osm2pgsql --append --slim --output=flex --style=osm.lua -d osm changes.osc.gz

Common errors and fixes

Message or symptom Root cause Fix
Need slim mode to update Loaded without --slim Reload with --slim; there is no retrofit
--append runs, row counts never change Table declared without ids Add the ids block and reload
A table is empty Callback never inserts — tag key typo Log a counter in Lua; do not trust silence
NOT NULL violation on geom Way is not closed, or relation is broken Guard with object.is_closed; quarantine the rest
Buildings with courtyards missing No process_relation Add it; multipolygons are relations
Import slows to a crawl part-way Indexes present during load Create indexes after, then ANALYZE
maxspeed column all null Values carry units the parser rejects Widen the parser, or keep the raw tag in tags

Frequently Asked Questions

Can I convert a non-slim import to slim later?

No. The middle tables are populated during the load from data that is discarded afterwards, so there is nothing to reconstruct them from short of re-reading the PBF — which is the reload. Decide before importing whether the database will ever need to track upstream, and if there is any doubt, use --slim; the cost is disk, and the alternative is a multi-hour reimport at the moment you discover you need it.

Why is osm_id negative on some building rows?

Because an area table can be fed by both closed ways and multipolygon relations, and the two identifier spaces overlap — way 12345 and relation 12345 are different objects. osm2pgsql disambiguates by storing relation-derived areas as negative numbers. Any join from this table to a way-keyed table must filter on osm_id > 0, and any join to a relation-keyed one must negate.

Should the raw tags column be jsonb or hstore?

jsonb unless you have an existing hstore schema. It indexes as well with a GIN index, it nests if you ever need it to, and every client library speaks JSON. The one argument for hstore is slightly smaller storage on tag-heavy tables, which rarely outweighs the interoperability.

How do I add a column without reimporting?

Add it in SQL and in the style file, then re-run with --append over a diff — but understand what that does: only objects touched by that diff get the new column populated, so the table ends up partly filled. For a column that must be complete, the honest options are a reimport or a one-off UPDATE that derives the value from the tags column you kept.

Is flex slower than the legacy pgsql output?

Marginally, because your Lua runs per object. On the measurements above the callbacks account for roughly a fifth of the load, and a style file that does heavy string work per object can push that much higher. It buys a schema you designed rather than one you have to work around, which is almost always the better trade.

Specification reference

osm2pgsql.define_table({ name, ids, columns }) creates or attaches a table. The ids field declares how OSM object identifiers map to rows and is required for the table to be updatable in append mode. Valid type values are node, way, relation, area and any; area stores way identifiers positive and relation identifiers negative in a single signed column.

Up one level: Exporting OSM to GeoParquet & PostGIS.