Joining relation and relation member properties from OpenStreetMap data in QGIS through PostGIS

06 Feb 2020 | all notes

Install postgresql, postgis, and create a fresh database

brew install postgresql postgis

# create new database-cluster folder
initdb -D db
pg_ctl -D db start

# create new database inside the cluster
createdb osm

Set up postgis

psql -d osm -c 'CREATE EXTENSION postgis; CREATE EXTENSION hstore;'
psql -d osm -f /usr/local/Cellar/osmosis/0.47/libexec/script/pgsnapshot_schema_0.6.sql

Check database layout:

psql -d osm -c '\dt'

             List of relations
 Schema |       Name       | Type  | Owner 
--------+------------------+-------+-------
 public | nodes            | table | kevin
 public | relation_members | table | kevin
 public | relations        | table | kevin
 public | schema_info      | table | kevin
 public | spatial_ref_sys  | table | kevin
 public | users            | table | kevin
 public | way_nodes        | table | kevin
 public | ways             | table | kevin
(8 rows)

If you want the ways table to have a readymade column of postgis LineString objects later on, also execute:

psql -d osm -f /usr/local/Cellar/osmosis/0.47/libexec/script/pgsnapshot_schema_0.6_linestring.sql

Import using osmosis (lossless)

--write-pgsql uses the ‘snapshot’ schema in which all tags are stored in a single ‘tags’ hstore data structure column

osmosis --read-pbf "data.pbf" --log-progress --write-pgsql database=osm

Merging relations data using an OUTER JOIN

To merge tags from the relation (such as colour, ref) into the way a 3-way join is necessary, where the inner join of relation_members and relations needs to be given precedence through bracketing, before performing an outer join with ways (which preserves all non-relation-member ways as they are, with NULL columns for the relation data). The query can be added as a QGIS layer from the Database > DB Manager menu.

SELECT DISTINCT ways.id, ways.tags->'railway' AS railway, ways.tags->'tunnel' AS tunnel, ways.tags->'bridge' AS bridge, ways.tags->'layer' AS layer, relations.tags->'colour' AS colour, relations.tags->'ref' AS name, linestring AS geometry FROM ways LEFT OUTER JOIN (relation_members INNER JOIN relations ON relation_members.relation_id = relations.id) ON ways.id = relation_members.member_id WHERE (relation_members.member_role <> '') IS NOT TRUE AND ways.tags->'area' IS NULL;

(The extraction of individual fields from the tags column can be skipped of you use QGIS’ Explode hstore processing algorithm.)

Comments