An osm2pgsql style to simplify OSM data use
An osm2pgsql multi-backend style designed to simplify consumption of OSM data for rendering, export, or analysis.
ClearTables is currently under rapid development, and schema changes will frequently require database reloads.
make
createdb ct
psql -d ct -c 'CREATE EXTENSION postgis; CREATE EXTENSION hstore;'
cat sql/types/*.sql | psql -1Xq -d ct
# Add other osm2pgsql flags for large imports, updates, etc
osm2pgsql -d ct --number-processes 2 --output multi --style cleartables.json extract.osm.pbf
cat sql/post/*.sql | psql -1Xq -d ct
Replace ct
with the name of your database if naming it differently.
osm2pgsql will connect to PostgreSQL once per process for each table, for a total of processes * tables connections.
If PostgreSQL max_connections
is increased from the default, --number-processes
can be increased. If --number-processes
is omitted, osm2pgsql will
attempt to use as many processes as hardware threads.
These are still a bit vague, and might be split into principles and practices
Simplify data for the consumer
Use PostgreSQL types other than text
if appropriate
Use boolean for yes/no values
Use enum types where there's a limited list of possibilities independent of data to be included, or a well defined ordering
Addresses and buildings have a many-to-many relationship. Multiple addresses inside one building are very common, and multiple buildings in one address can be found. If rendering, a separate table is fine, and if doing an analysis these cases need to be considered which requires joins.
A road may have multiple refs, and it's wrong to ignore this. To pretend that
there's only one ref, use SQL like array_to_string(refs, E'\n')
or
array_to_string(refs, ';')
. The latter will reform the ref tag as it was in
the original data.
--hstore
?ClearTables uses the hstore type but doesn't support --hstore
.
The goal of ClearTables is to abstract away OSM tagging. Copying all the tags to the output is contrary to this.
Copying all tags is technically possible, but wouldn't be done with --hstore
, instead it would be done similar to the names column. The --hstore
option doesn't work well when using custom column names which may collide with OSM tags.
With tables for different types of features fine-grained selection of appropriate columns is possible and hstore isn't necessary.
Values within a hstore are untyped which is contrary to the principle of using appropriate types.
Bug reports, suggestions and (especially!) pull requests are very welcome on the Github issue tracker. Please check the tracker to see if your issue is already known, and be nice. For questions, please use IRC (irc.oftc.net or http://irc.osm.org, channel #osm-dev) and http://help.osm.org.
tags
are OSM tags, cols
are database columnsfunction f (args)
_polygon
and _point
suffix when there will be two tables holding the same type of object represented differently (e.g. most POIs)_area
when there isn't a corresponding _point
table for the same object, but there is another table for points or lines of a similar class but different objects (e.g. wood_areas
for forests and wood_line
for rows of trees)assert(transform({foo="bar"}).baz == "qux")
instead of assert(deepcompare(transform({foo="bar"}), {baz="qux"}))
.Issues tagged with new column are often good ones to get started with. Issues tagged experimental are focused on researching new best practices and state of the art.