OSM PBF Foreign Data Wrapper

Synopsis

Openstreetmap PBF foreign data wrapper for PostgreSQL

Description

This extension for PostgreSQL implements Foreign Data Wrapper (FDW) for reading Openstreetmap PBF file format (*.osm.pbf)

Usage

Example: sql CREATE EXTENSION osm_fdw; CREATE SERVER osm_fdw_server FOREIGN DATA WRAPPER osm_fdw; -- table definition SELECT create_osm_table('osm_malta', 'osm_fdw_server', '/path_to_file/malta-latest.osm.pbf'); -- count all nodes SELECT count(*) FROM osm_malta WHERE type='NODE';

Find more examples here: examples

How to use

Create extension at first: sql CREATE EXTENSION osm_fdw;

To access foreign data, you need to create a foreign server object: sql CREATE SERVER osm_fdw_server FOREIGN DATA WRAPPER osm_fdw;

Then create foreign table. There are two options: CREATE FOREIGN TABLE query and create_osm_table function. I would strongly recommend to use second method. The function create_osm_table(text, text, text) is provided together with this extension. It requires 3 parameters: name of the table, name of the foreign server object and a path to *.osm.pbf file. sql SELECT create_osm_table('table_name', 'osm_fdw_server', '/path_to_file/file.osm.pbf');

FDW reads the file with every query. The right approach is to copy data to postgresql table or materialized view, create required indexes and query this table or view. sql CREATE MATERIALIZED VIEW osm_data AS SELECT * FROM osm_foreign_table WITH DATA;

Table structure

Table should have this structure: ```sql CREATE FOREIGN TABLE table_name ( 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/file.osm.pbf' ); ``` Fields can have other names, but position and types must be as in this example.

Column types

The FDW can read 3 openstreetmap types: (NODE), (WAY) and (RELATION).

* `id` - OSM object id
* `type` - type of the object (Possible values: NODE, WAY, RELATION)
* `lat` - latitude (filled only for NODE)
* `lon` - longitude (filled only for NODE)
* `tags` - jsonb object with OSM tags
* `refs` - array on node ids (filled only for WAY)
* `members` - array of objects with relation members (jsonb)
* `version` - OSM version
* `modified` - OSM last change date
* `changeset` - OSM changeset
* `user_id` - id of the OSM user
* `username` - name of the OSM user
* `visible` - shows if object is visible