Openstreetmap PBF foreign data wrapper for PostgreSQL
This extension for PostgreSQL implements Foreign Data Wrapper (FDW) for reading Openstreetmap PBF file format (*.osm.pbf)
Example:
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
Create extension at first:
CREATE EXTENSION osm_fdw;
To access foreign data, you need to create a foreign server object:
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.
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.
CREATE MATERIALIZED VIEW osm_data AS SELECT * FROM osm_foreign_table WITH DATA;
Table should have this structure:
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.
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