OSM PBF Foreign Data Wrapper

Synopsis

Openstreetmap PBF foreign data wrapper for PostgreSQL

Description

This extension allows to read openstreetmap PBF files directly from PostgreSQL.

Usage

Small example:

CREATE EXTENSION osm_fdw;
CREATE SERVER osm_fdw_server FOREIGN DATA WRAPPER osm_fdw;

-- table defenition
CREATE FOREIGN TABLE osm_malta (
    id bigint,
    type text,
    lat double precision,
    lon double precision,
    tags jsonb,
    refs bigint[],
    members jsonb,

    version int,
    modified timestamp,
    changeset bigint,
    user_id int,
    username text,
    visible boolean
)
SERVER osm_fdw_server
OPTIONS (
    filename '/path_to_file/malta-latest.osm.pbf'
);

-- find count of nodes
SELECT count(*) FROM osm_malta WHERE type='NODE';

Find more examples here: examples

Table structure

The table should have the same structure as in the example. Openstreetmap has 3 main object types: NODE, WAY and RELATION. The table row is a container that can contain all OSM types. Column type is a text column and shows type of the openstreetmap object (NODE, WAY, RELATION)

Columns version, modified, changeset, user_id, username, visible are informative and not empty for all object types.

List of used columns for every object type:

  1. Node (OSM NODE)

    • id
    • type = ‘NODE’
    • lat
    • lon
    • tags

  2. Way (OSM WAY)

    • id
    • type = ‘WAY’
    • tags
    • refs (List of included nodes)

  3. Relation (OSM RELATION)

    • id
    • type = ‘RELATION’
    • tags
    • members

id is not unique, but (id, type) is unique.

tags and members are JSON in v9.3 and jsonb in v9.4

Support

(Issues trackes)

Email: v.pikulik@gmail.com

Author

Vitali Pikulik <v.pikulik@gmail.com>