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:

```sql 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>