Exporting OSM to GeoParquet & PostGIS Jump to heading#

Every stage documented in Parsing & Tag Normalization Workflows ends in the same place: a stream of normalised records that has to be written somewhere a consumer can query. That last stage gets far less attention than parsing does, and it decides more about how the data is used than any earlier choice. A schema that buries every tag in a JSON blob makes the fastest export and the slowest analytics. A partitioning scheme chosen without reference to the queries makes every filter a full scan. And a sink chosen without asking whether the data will be updated commits the pipeline to either full rebuilds or a middle-table layout, for its entire life.

This topic covers the two sinks that account for most OSM pipelines. GeoParquet is the columnar, immutable, object-storage-friendly option that analytics engines read directly. PostGIS is the mutable, indexed, transactional option that can be kept current with minutely diffs. They are not alternatives so much as different answers to “what happens on the next run”, and a large fraction of mature pipelines run both.

Prerequisite concepts Jump to heading#

Three things need to be settled before the export stage is designed. The tag vocabulary has to be stable, because the columns you promote out of the tag map are a schema commitment — the material in Tag Taxonomy & Key-Value Standards is what makes that commitment safe. The geometry has to be assembled and valid, which is the subject of Geometry Validation & Repair; writing invalid geometry to either sink pushes the problem to whoever reads it. And the coordinate reference system has to be decided, because both formats record a CRS and neither will convert for you — see Coordinate Reference Systems in OSM.

Choosing between the sinks Jump to heading#

The comparison resolves into one question: does this dataset get updated, or rebuilt? A dataset rebuilt nightly from a fresh extract has no use for the middle tables that make PostGIS updatable, and gains a great deal from immutable files that any number of readers can open at once without a server. A dataset that must reflect upstream edits within minutes needs the update path described in Applying .osc Change Files with osmium, and that path leads to a database.

GeoParquet: what the specification actually requires Jump to heading#

GeoParquet is ordinary Apache Parquet plus a metadata convention. The requirements are modest and worth knowing exactly, because most interoperability failures come from omitting one of them.

Geometry lives in a binary column encoded as well-known binary. The file’s key-value metadata carries a geo key whose value is a JSON document declaring the version, the name of the primary geometry column, and, per geometry column, the encoding, the geometry types present, and the CRS as a PROJJSON object. Omit the geo metadata and you have a Parquet file with a blob column that no spatial tool will recognise; get the column name wrong in the metadata and readers will not find the geometry they are looking at.

import json
import logging
import pyarrow as pa
import pyarrow.parquet as pq
from shapely import to_wkb

logger = logging.getLogger(__name__)

GEO_META = {
    "version": "1.1.0",
    "primary_column": "geometry",
    "columns": {
        "geometry": {
            "encoding": "WKB",
            "geometry_types": ["Polygon", "MultiPolygon"],
            "crs": None,  # null means OGC:CRS84 — longitude, latitude in WGS 84
            "bbox": [-10.6, 51.4, -5.3, 55.5],
        }
    },
}

def write_geoparquet(table: pa.Table, path: str, row_group_rows: int = 250_000) -> None:
    """Write an Arrow table as GeoParquet with the metadata spatial readers expect."""
    meta = dict(table.schema.metadata or {})
    meta[b"geo"] = json.dumps(GEO_META).encode()
    table = table.replace_schema_metadata(meta)
    pq.write_table(
        table, path,
        compression="zstd", compression_level=3,
        row_group_size=row_group_rows,
        write_statistics=True,     # per-row-group min/max is what enables pruning
    )
    logger.info("wrote %s: %d rows, %d row groups", path, table.num_rows,
                -(-table.num_rows // row_group_rows))

A null CRS in the metadata is not an omission — it is the specification’s way of saying OGC:CRS84, longitude then latitude in WGS 84, which is what unprojected OSM data is. Writing projected coordinates without replacing that null is the most common CRS mistake in OSM exports, and it produces a file every reader misinterprets confidently.

Schema shape Jump to heading#

The measurements point clearly at a hybrid: promote the tags you actually filter and group by into real typed columns, and keep the remainder in a map column so nothing is lost. Typed columns compress well and let a reader touch one column instead of parsing a blob; the map column preserves the long tail described in Tag Taxonomy & Key-Value Standards without inflating the schema to thousands of mostly-null columns.

Two details make a large difference. Row-group size sets the granularity of statistics-based pruning: too large and a filter reads more than it needs, too small and metadata overhead grows and compression suffers. A quarter of a million rows is a reasonable default for OSM features. And sorting rows before writing — by an H3 cell, a quadkey, or any locality-preserving key from Spatial Index Selection — clusters spatially near features into the same row group, so a bounding-box filter skips most of the file on statistics alone.

PostGIS: the flex output Jump to heading#

osm2pgsql has two output backends and the difference matters. The legacy pgsql output writes a fixed set of tables with a fixed column choice and a hstore catch-all. The flex output lets you define tables and the mapping from OSM objects to rows in Lua, which means the schema is yours rather than the tool’s.

local buildings = osm2pgsql.define_table({
  name = 'buildings',
  ids = { type = 'area', id_column = 'osm_id' },
  columns = {
    { column = 'name',        type = 'text' },
    { column = 'building',    type = 'text', not_null = true },
    { column = 'levels',      type = 'int' },
    { column = 'height_m',    type = 'real' },
    { column = 'tags',        type = 'jsonb' },
    { column = 'geom',        type = 'multipolygon', projection = 4326, not_null = true },
  }
})

function osm2pgsql.process_way(object)
  if not object.is_closed or not object.tags.building then return end
  buildings:insert({
    name     = object.tags.name,
    building = object.tags.building,
    levels   = tonumber(object.tags['building:levels']),
    height_m = tonumber((object.tags.height or ''):match('^%d+%.?%d*')),
    tags     = object.tags,
    geom     = object:as_multipolygon(),
  })
end

The ids declaration is what makes the table updatable: it tells osm2pgsql how to find the rows belonging to an OSM object when a diff modifies it. A flex table without an ids block is write-once, and --append will not touch it.

For data that does not come through osm2pgsql at all — a derived table your own pipeline produces — bulk loading goes through COPY rather than inserts, by roughly two orders of magnitude:

import io
import logging
import psycopg

logger = logging.getLogger(__name__)

def copy_features(conn: psycopg.Connection, rows: list[tuple[int, str, bytes]]) -> int:
    """Bulk-load (osm_id, name, wkb_geometry) rows into a prepared table."""
    with conn.cursor() as cur, cur.copy(
        "COPY features (osm_id, name, geom) FROM STDIN (FORMAT BINARY)"
    ) as copy:
        copy.set_types(["int8", "text", "bytea"])
        for row in rows:
            copy.write_row(row)
    logger.info("copied %d rows", len(rows))
    return len(rows)

Build the spatial index after the load, not before. A GiST index maintained during a bulk load costs roughly three times what building it once at the end does.

Validation and error-handling matrix Jump to heading#

That last row is the systemic one. When two sinks are fed by the same pipeline, every filtering decision must happen upstream of the fork, or the sinks drift apart and nobody can say which is right.

Performance and scale considerations Jump to heading#

Export cost is dominated by serialisation and compression, not by the sink. Writing 14 million features to GeoParquet with zstd level 3 runs at roughly 380 000 rows per second on one core; raising compression to level 9 costs three times the CPU for around eight percent more compression, which is rarely a good trade for data that is read often. Geometry encoding is the other large term: shapely.to_wkb over an array is vectorised and fast, while calling it per row in a Python loop is not, in the same way and for the same reason as the regex work in Value Standardization & Regex Cleaning.

For PostGIS, the dominant term is index construction and the write-ahead log. Loading with COPY into an unindexed table, then creating indexes, then running ANALYZE, is between five and ten times faster end to end than loading into an indexed table. On a bulk initial load where the data can be re-created, an UNLOGGED table during the load and a switch to logged afterwards removes the WAL cost as well.

Failure modes and gotchas Jump to heading#

Parquet has no schema evolution rules of its own, so two runs of the same pipeline that promote different tag columns produce files that a reader cannot union. Pin the promoted-column list in the mapping registry described in Batch Attribute Mapping Strategies and version it with the data.

Partition cardinality is the other trap. Partitioning by country gives roughly two hundred directories and works well; partitioning by H3 resolution 8 gives millions of tiny files and makes every query slower than no partitioning at all. Aim for partitions of a few hundred megabytes.

Finally, osm2pgsql in flex mode silently drops objects your Lua returns without inserting. That is the intended behaviour and it makes a typo in a tag name look exactly like an area with no buildings. Count insertions in the Lua and log them.

Keeping two sinks honest Jump to heading#

Running both sinks is common and it introduces a failure the single-sink case does not have: the two can disagree, and neither knows it. The discipline that prevents it is to treat one as the system of record and derive the other from it, rather than feeding both from the pipeline in parallel.

Deriving GeoParquet from PostGIS costs a query and guarantees the two agree by construction, because there is only one copy of the filtering and normalisation logic. Feeding both in parallel means every validity gate, every tag rule and every fallback chain exists twice, and the moment one is changed without the other the exports diverge in a way that only shows up as a row-count difference somebody notices weeks later.

Where parallel feeding is genuinely necessary — usually because the export must not wait for the database — the mitigation is a reconciliation job: count rows by feature class in both sinks on a schedule and alert on any divergence beyond a small tolerance. It does not prevent the drift, but it bounds how long it can go unnoticed.

In this section Jump to heading#

• Writing OSM Features to GeoParquet with PyArrow — the complete writer, metadata block and row-group sizing included.
• Loading OSM Data into PostGIS with osm2pgsql Flex — a Lua style file that produces an updatable schema.
• Partitioning a GeoParquet OSM Lake by H3 Cell — a partition key that prunes without shattering the dataset into tiny files.

Frequently Asked Questions Jump to heading#

Related Jump to heading#

• Parsing & Tag Normalization Workflows — the section this export stage terminates.
• Batch Attribute Mapping Strategies — where the promoted-column list is decided and versioned.
• Memory-Efficient Chunk Processing — the chunking that feeds the writer.
• Applying .osc Change Files with osmium — the update path that only the database sink supports.
• Spatial Index Selection: R-tree, H3 or Quadkey — how to pick the sort and partition key.
• Geometry Validation & Repair — the gate that must sit upstream of the fork.

Up one level: Parsing & Tag Normalization Workflows.