Recently, I have demonstrated how
the open street map pbf parser (OSMpbfParser.py) can be used to create
xml files. With little
effort, a script can be written that creates and fills an
sqlite database.
Of course, I first have to define a schema into which I load the data. I think this is also a good exercise to understand the internals of osm data. The tables
are adapted from the
original open street data database schema.
Tables
Table node
The
node table is
the fundamental table for open street map. Each node consists of a longitude (
lon) / latitude (
lat) pair
and thus defines a geographical point on the surface of the earth. The original schema has a few more attributes, such as
changeset_id or
version. I am not interested in these, so I don't load them:
create table node(
id integer primary key,
lat real not null,
lon real not null
);
Tables way and node_in_way
Each "line" (such as a street or border etc) on open street map is an ordered list of
nodes. Open Street Map calles them
ways. Ways are also used to define areas in which case the last node equals the first node in the list.
First, I need a table just to store each way. Again, I am not interested in attributes such as user or changeset, so I just load the way's id:
create table way(
id integer primary key
);
The table
node_in_way relates nodes and ways:
create table node_in_way(
way_id integer not null
references way
deferrable initially deferred,
node_id integer not null
references node
deferrable initially deferred,
order_ integer not null
)
Tables relation and member_in_relation
Open Street Map allows to relate multipe nodes and ways (and even relations) in a relation:
create table relation(
id integer primary key
);
Each member (that is either node, way or relation) is listed in member_in_relation.
Thus, exactly one of the attributes node_id, way_id or relation_id
is not null:
create table member_in_relation(
id_of_relation integer not null
references relation
deferrable initially deferred,
node_id integer null
references node
deferrable initially deferred,
way_id integer null
references way
deferrable initially deferred,
relation_id integer null
references relation
deferrable initially deferred,
role text
)
Table tag
Finally, there are
tags. A tag is a key (
k) value (
v) pair that
can be assigned to nodes, ways or relations. Often, the values for specific keys define what an object
is. As in
member_in_relation exactly on of
node_id,
way_id or
relation_id is not null.
create table tag(
node_id integer null
references node
deferrable initially deferred,
way_id integer null
references way
deferrable initially deferred,
relation_id integer null
references relation
deferrable initially deferred,
k text not null,
v text not null
)
ERD
Here's the ERD for these tables:
The ERD was created with
dia from
pbf2sqlite-erd.dia.
Loading the pbf to a sqlite db
In order to run the script, a pbf must be obtained, for example with
download-switzerland-pbf.py.
Then, this pbf can be loaded into a sqlite db on the command line with
pbf2sqlite.py xyz.pbf xyz.db
The script is on github: pbf2sqlite.py.
Links
Parsing an Open Street Map pbf file with Python
Open Street Map: convert pbf to xml
A Google Earth hiking map for the Säntis region with Open Street Map data
An open street map node density map for Switzerland
