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;

Next you should create foreign table. There are two ways: CREATE FOREIGN TABLE query and create_osm_table function. I strongly recommend to use second method. The function create_osm_table(text, text, text) is provided together with the extension. It requires 3 parameters: a name of the table, a 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 start to read the file with every query. I would recommend to create materialized view to fast data access. 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 (Values: NODE, WAY, RELATION)
* `lat` - latitude (not empty only for NODE)
* `lon` - longitude (not empty only for NODE type)
* `tags` - json object with OSM tags (json for postgres 9.3 and jsonb for 9.4)
* `refs` - array on node ids (not empty only for WAY)
* `members` - array of objects with relation members (not empty only for RELATION; json for postgres 9.3 and jsonb for 9.4)
* `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