Import OSM data into PostGIS and visualize with QGIS
Posted for memorandum. Since this article was written through trial and error with little prerequisite knowledge, there may be some incorrect expressions and interpretations.
Previous knowledge
OSM (OpenStreetMap) A project to create open data geographic information together so that anyone can use the map freely. Anyone is free to participate, edit and use the map
- https://openstreetmap.jp/
- https://www.openstreetmap.org/
- OpenStreetMap - Wikipedia
PostGIS
Extensions for handling spatial maps for PostgreSQL databases
QGIS
GIS (Geographic Information System) software that can view, edit, and analyze geographic information
- https://qgis.org/ja/site/
- QGIS - Wikipedia
Get OSM data
OSM data can be downloaded from the following sites throughout Japan or for each region.
- http://download.geofabrik.de/asia/japan.html
This time, I tried using Kansai data.
「 Kansai region (a.k.a. Kinki region) 」
There are multiple options for downloading
osm.pbfandosm.bz2are so-called osm data, and it seems that the compression format is different.shp.zipis shape data
So, if you download the shape data and visualize it as it is with QGIS, the purpose of the title can be achieved, but this time the purpose is to interact with OSM data, so kansai-latest.osm.pbf (189MB) (As of December 2020)) to download
When acquiring OSM data for a more limited area
From now on, the article will continue on the premise of using the OSM of Kansai data mentioned above, but honestly, in the case of a weak PC, it takes time for various processes. Especially in the final visualization using QGIS, the amount of data is too large and I think that the operation may be stressful.
Working with smaller size (regionally limited) OSMs will solve this problem. The method is also described. You can get osm with limited area by opening Site described in OSM of previous knowledge and selecting "Export" from the top menu.
However, if you select an area that is as large as possible, you will get an error such as You requested too many nodes (limit is 50000). Either request a smaller area, or use planet.osm.
In the city center, you can actually make a selection only within a few minutes' walk.
I selected the area on the north side of Kyoto Station above, but it is within the limit of exceeding the upper limit. The downloaded osm file is now uncompressed 7MB. Since it is uncompressed, you can check the contents. It turns out that the contents are XML data.
PostGIS preparation
This time, the PostGIS server will be built inside Docker. If you want to know the basics of Docker, I think the article I wrote earlier will be helpful.
PostGIS is already available on Docker Hub, so take advantage of it
- https://registry.hub.docker.com/r/postgis/postgis
$ cd (Appropriate local working directory)
#For local storage of PostGIS data
$ mkdir postgis_data
#Start Docker.Postgresql data directory mounted locally.Port mapping.If you do not set a password, startup will fail, so set it appropriately.
$ docker run -v `pwd`/postgis_data:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_PASSWORD=password -d postgis/postgis
#Start confirmation
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
07588ca07dd0 postgis/postgis "docker-entrypoint.s…" 24 seconds ago Up 23 seconds 0.0.0.0:5432->5432/tcp objective_golick
#Postgresql data is stored locally
$ ls postgis_data/
PG_VERSION pg_multixact pg_tblspc
base pg_notify pg_twophase
global pg_replslot pg_wal
pg_commit_ts pg_serial pg_xact
pg_dynshmem pg_snapshots postgresql.auto.conf
pg_hba.conf pg_stat postgresql.conf
pg_ident.conf pg_stat_tmp postmaster.opts
pg_logical pg_subtrans postmaster.pid
#Invade the container
$ docker exec -it 07588ca07dd0 /bin/bash
root@07588ca07dd0:/#
#PostGIS connection
root@07588ca07dd0:/# psql -U postgres
psql (12.5 (Debian 12.5-1.pgdg100+1))
Type "help" for help.
postgres=#
#Operation check
postgres=# select VERSION();
version
-----------------------------------------------------------------------------------------------
-------------------
PostgreSQL 12.5 (Debian 12.5-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.
0-6) 8.3.0, 64-bit
(1 row)
#Postgresql disconnect
postgres=# \q
#Docker withdrawal(Local return)
root@07588ca07dd0:/# exit
$
Import OSM data into PostGIS
Preparation of osm2pgsql
Use software called osm2pgsql for import
- https://github.com/openstreetmap/osm2pgsql
- https://learnosm.org/ja/ (It was helpful because it provided an easy-to-understand explanation not only about osm2pgsql but also about OSM in general)
The PostGIS Docker prepared earlier is Debian-based and can be installed with the apt command, so install it in the PostGIS container and use it.
#Invade the container
$ docker exec -it 07588ca07dd0 /bin/bash
#Can be easily introduced below
$ root@07588ca07dd0:/# apt update
$ root@07588ca07dd0:/# apt install osm2pgsql
#Installation complete
root@07588ca07dd0:/# which osm2pgsql
/usr/bin/osm2pgsql
root@07588ca07dd0:/# osm2pgsql --version
osm2pgsql version 0.96.0 (64 bit id space)
#Docker withdrawal(Local return)
root@07588ca07dd0:/# exit
$
Here, once create an image called PostGIS with osm2pgsql installed.
#docker process confirmation
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
07588ca07dd0 postgis/postgis "docker-entrypoint.s…" 21 minutes ago Up 21 minutes 0.0.0.0:5432->5432/tcp objective_golick
#docker stop
$ docker stop 07588ca07dd0
07588ca07dd0
#Creating an image
$ docker commit 07588ca07dd0 postgis_with_osm2pgsql
sha256:63aa984c42a530615618d971d64bcc9a1c18fb91a4274325e747fcd5df1a09f3
#Image confirmation
$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
postgis_with_osm2pgsql latest 63aa984c42a5 14 seconds ago 513MB
...
#Discard the running container once
$ docker rm 07588ca07dd0
07588ca07dd0
Import OSM data into PostGIS using osm2pgsql
Start Docker
Save the previously downloaded OSM data in your working directory
$ ls -F
kansai-latest.osm.pbf postgis_data/
Start with the newly created image. (At that time, add a mount point for OSM data)
#Start by adding mount option
$ docker run -v `pwd`/postgis_data:/var/lib/postgresql/data -v `pwd`:/work -p 5432:5432 -d postgis_with_osm2pgsql
#Start confirmation
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
55f0d5aec9a0 postgis_with_osm2pgsql "docker-entrypoint.s…" 36 seconds ago Up 35 seconds 0.0.0.0:5432->5432/tcp objective_swartz
#Docker intrusion
$ docker exec -it 55f0d5aec9a0 /bin/bash
root@55f0d5aec9a0:/#
#Check if it is mounted correctly
root@55f0d5aec9a0:/# ls -l /work
total 196612
-rw-r--r-- 1 root root 198153708 Dec 17 06:35 kansai-latest.osm.pbf
drwx------ 26 postgres root 832 Dec 20 04:55 postgis_data
PostGIS database creation
When importing OSM data with osm2pgsql, the definition of the Postgresql database of the import destination is required in advance, so create it (create it with the name osm this time). At that time, it is necessary to enable EXTENSION that handles PostGIS for that database, so be sure to do it.
#Postgresql connection
root@55f0d5aec9a0:/# psql -U postgres
psql (12.5 (Debian 12.5-1.pgdg100+1))
Type "help" for help.
#Database creation(Named osm)
postgres=# create database osm;
CREATE DATABASE
#Database list display
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------------+----------+----------+------------+------------+-----------------------
osm | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
...
#Connect to osm Database
postgres=# \c osm
You are now connected to database "osm" as user "postgres".
#Adapt PostGIS extensions to osm databases
osm=# create extension postgis;
CREATE EXTENSION
#Ready
osm=# \q
root@55f0d5aec9a0:/#
Executing the osm2pgsql command
It's finally import work, but one more preparation is required. Each OSM data has data such as points, lines, and polycons, but there are various attribute values for each of them. It is necessary to prepare a definition file (called a style file in osm2pgsql) on how the attribute value should be related to the Table column of the DB. This time, we will use the style provided from the site LearnOSM --OpenStreetMap Guide from the beginning introduced earlier.
- https://learnosm.org/files/default.style
Download the above and save it in your working directory
# default.I downloaded and placed style from the above
root@55f0d5aec9a0:/# ls -l /work
total 196620
-rw-r--r-- 1 root root 6025 Dec 20 05:16 default.style
-rw-r--r-- 1 root root 198153708 Dec 17 06:35 kansai-latest.osm.pbf
drwx------ 26 postgres root 832 Dec 20 04:55 postgis_data
It's finally import work. Execute the following command
root@55f0d5aec9a0:/# cd /work
root@55f0d5aec9a0:/work# osm2pgsql --create --database=osm --slim --style=./default.style -U postgres -H localhost kansai-latest.osm.pbf
...
(Wait for a while)
...
Osm2pgsql took 24146s overall
Then wait patiently. For a weak PC like me, it takes one night to complete the next morning. (24,146s = 6.7hour)
Check the finish
It can be confirmed that multiple Tables called planet_osm_ * are defined and data is inserted in each.
root@55f0d5aec9a0:/work# psql -U postgres osm;
psql (12.5 (Debian 12.5-1.pgdg100+1))
Type "help" for help.
osm=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------+-------+----------
public | planet_osm_line | table | postgres
public | planet_osm_nodes | table | postgres
public | planet_osm_point | table | postgres
public | planet_osm_polygon | table | postgres
public | planet_osm_rels | table | postgres
public | planet_osm_roads | table | postgres
public | planet_osm_ways | table | postgres
public | spatial_ref_sys | table | postgres
(8 rows)
osm=# \d planet_osm_roads;
Table "public.planet_osm_roads"
Column | Type | Collation | Nullable | Default
--------------------+---------------------------+-----------+----------+---------
osm_id | bigint | | |
access | text | | |
addr:housename | text | | |
addr:housenumber | text | | |
addr:interpolation | text | | |
admin_level | text | | |
aerialway | text | | |
...
osm=# select count(*) from planet_osm_point;
count
--------
367808
(1 row)
osm=# select count(*) from planet_osm_line;
count
---------
1263136
(1 row)
Basic structure of OSM data
-
This section may be wrong because my understanding is weak. See this area for a solid understanding
-
http://wiki.openstreetmap.org/wiki/JA:Map_Features
nodes (planet_osm_nodes)
The points, lines, and polycons that make up OSM are all a set of one or more vertices. Nodes define the vertices. The Table structure is also a simple one with 3 columns of ID and latitude / longitude.
osm=# select * from planet_osm_nodes limit 5;
id | lat | lon
----------+-----------+------------
56577716 | 353219106 | 1396343130
56577734 | 353219087 | 1396342033
56577739 | 353223037 | 1396319209
56577850 | 353221939 | 1396321446
56577855 | 353229890 | 1396317181
point (planet_osm_point)
Represents a so-called point. For example, when the following points are defined in the osm data, they are registered in planet_osm_point as id = osm_id, and the coordinate values are defined in planet_osm_nodes with the same ID.
<node id="274017002" visible="true" version="4" changeset="14708059" timestamp="2013-01-19T14:04:25Z" user="xxxx" uid="xxxx" lat="34.9867552" lon="135.7624890">
<tag k="amenity" v="restaurant"/>
<tag k="name" v="Shinpuku Saikan Main Store"/>
</node>
osm=# select * From planet_osm_nodes where id = '274017002';
-[ RECORD 1 ]---
id | 274017002
lat | 349867552
lon | 1357624890
osm=# select * From planet_osm_point where osm_id = '274017002';
-[ RECORD 1 ]------+---------------------------------------------------
osm_id | 274017002
...
amenity | restaurant
...
name |Shinpuku Saikan Main Store
...
way | 0101000020110F0000902A9E6466D36C41AB1EE7AF10C14F41
way (planet_osm_way) and polygon (planet_osm_polygon), line (planet_osm_line)
An ordered set of vertices. Those that are closed are polygons, and those that are not closed are lines. The following is an example of ploycon if it is defined below in the original OSM
<way id="160731923" visible="true" version="11" changeset="63886009" timestamp="2018-10-26T02:30:00Z" user="xxxx" uid="xxxx">
<nd ref="1727782796"/>
<nd ref="1727782822"/>
<nd ref="1727782826"/>
<nd ref="1727782831"/>
<nd ref="1727782834"/>
<nd ref="1727782833"/>
<nd ref="1727782788"/>
<nd ref="1727782787"/>
<nd ref="1727782794"/>
<nd ref="1727782792"/>
<nd ref="1727782789"/>
<nd ref="1727782796"/>
<tag k="addr:block_number" v="590"/>
<tag k="addr:city" v="Kyoto City"/>
<tag k="addr:housenumber" v="2"/>
<tag k="addr:postcode" v="600-8216"/>
<tag k="addr:province" v="Kyoto"/>
<tag k="addr:quarter" v="Higashishiokoji Town"/>
<tag k="addr:street" v="Karasuma-dori Shichijo down"/>
<tag k="addr:suburb" v="Shimogyo Ward"/>
<tag k="building" v="retail"/>
<tag k="building:levels" v="7"/>
<tag k="height" v="30"/>
<tag k="name" v="Yodobashi"/>
<tag k="name:en" v="Yodobashi Camera"/>
<tag k="name:fr" v="achat électronique apple"/>
<tag k="name:ja" v="Yodobashi Camera"/>
<tag k="name:ja_rm" v="Yodobashikamera"/>
<tag k="name:ko" v="요도야바시"/>
<tag k="opening_hours" v="Mo-Su 09:30-20:00"/>
<tag k="shop" v="electronics"/>
<tag k="source" v="Bing"/>
<tag k="website" v="http://www.yodobashi-kyoto.com/"/>
<tag k="wheelchair" v="yes"/>
</way>
It is represented below by two tables, planet_osm_way and planet_osm_polygon.
osm=# select * from planet_osm_ways where id = 160731923;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | 160731923
nodes | {1727782796,1727782822,1727782826,1727782831,1727782834,1727782833,1727782788,1727782787,1727782794,1727782792,1727782789,1727782796}
tags | {name,Yodobashi,shop,electronics,height,30,source,Bing,name:en,"Yodobashi Camera",name:fr,"achat électronique apple",name:ja,Yodobashi Camera,name:ko,요도야바시,website,http://www.yodobashi-kyoto.com/,building,retail,addr:city,Kyoto City,name:ja_rm,Yodobashikamera,wheelchair,yes,addr:street,Karasuma-dori Shichijo down,addr:suburb,Shimogyo Ward,addr:quarter,Higashishiokoji Town,addr:postcode,600-8216,addr:province,Kyoto,opening_hours,"Mo-Su 09:30-20:00",building:levels,7,addr:housenumber,2,addr:block_number,590}
osm=# select * from planet_osm_polygon where osm_id = 160731923;
-[ RECORD 1 ]------+------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
osm_id | 160731923
...
addr:housenumber | 2
...
building | retail
...
name | Yodobashi
...
shop | electronics
...
z_order | 0
way_area | 12775
way | 0103000020110F0000010000000C000000F895DC782CD36C41CFB8D3368AC14F410A0A518A2CD36C41C2414A5E7CC14F41DE753F892
CD36C418CB722035FC14F41D10F7B1F32D36C41FAA90BC15EC14F41A67C765633D36C41566CC89A5EC14F412BD4BC963AD36C41A1CF38F55EC14F4165EA6F9C3
AD36C414D3854005DC14F41CE975B4B3ED36C4103E3FAE75CC14F4128B2515A3ED36C411735F2808CC14F415880BD0C36D36C41CA4633078CC14F41E5CCC0073
6D36C41DBEA5AAB8AC14F41F895DC782CD36C41CFB8D3368AC14F41
Here's an example of line as well (planet_osm_ways and planet_osm_line)
<way id="174762075" visible="true" version="10" changeset="79181018" timestamp="2020-01-04T05:34:57Z" user="hayashi" uid="621319">
<nd ref="2783315750"/>
<nd ref="3554802235"/>
<nd ref="410083087"/>
<nd ref="5196506199"/>
<nd ref="5196506198"/>
<nd ref="2783315752"/>
<nd ref="3743790129"/>
<nd ref="3743790130"/>
<tag k="highway" v="secondary"/>
<tag k="lanes" v="2"/>
<tag k="maxspeed" v="40"/>
<tag k="name" v="Shiokoji-dori"/>
<tag k="name:en" v="Shio-koji dori"/>
<tag k="name:ja" v="Shiokoji-dori"/>
<tag k="name:ko" v="시오코지도리"/>
<tag k="oneway" v="yes"/>
<tag k="oneway:bicycle" v="yes"/>
<tag k="ref" v="115"/>
<tag k="source" v="Bing"/>
</way>
↓
osm=# select * from planet_osm_ways where id = 174762075;;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------
id | 174762075
nodes | {2783315750,3554802235,410083087,5196506199,5196506198,2783315752,3743790129,3743790130}
tags | {ref,115,name,Shiokoji-dori,lanes,2,oneway,yes,source,Bing,highway,secondary,name:en,"Shio-koji dori",name:ja,Shiokoji-dori,name:ko,시오코지도리,maxspeed,40,oneway:bicycle,yes}
osm=# select * from planet_osm_line where osm_id = 174762075;;
-[ RECORD 1 ]------+------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------
osm_id | 174762075
...
highway | secondary
...
name |Shiokoji-dori
...
oneway | yes
...
ref | 115
...
z_order | 36
way_area |
way | 0102000020110F0000080000007397286741D36C41A7993C8930C14F41C41EAC8A43D36C41B2B274B630C14F412B5EB73249D36C417
A3ADA3F31C14F410EA803644DD36C41B59C6B8531C14F4173DF29004FD36C41258413E531C14F4151B3B01E51D36C41F6344DB232C14F41B567F1EC51D36C41A
FB7F4E932C14F418AD4EC2353D36C41366D9D9933C14F41
Perhaps the way column in planet_osm_line/polygon is like a reverse lookup to planet_osm_way, but I don't understand the details.
planet_osm_rels
Hold because I don't understand
planet_osm_road
Probably a subset of planet_osm_line
Visualize OSM data with QGIS
QGIS can be downloaded and installed from the download site below. This time, we will use Version 3.10, which is considered to be a stable version (as of December 2020).
- https://qgis.org/ja/site/forusers/download.html
Connection with PostGIS
If you keep Docker running as described above, the local 5432 port will be bound to the PostGIS 5432 port in Docker. Select PostGIS from the left browser panel of QGIS (right-click) and then select "Create New Connection ...".
- Name: OSM_KANSAI
- Host: localhost
- Port: 5432
- Database: osm
When you press the connection test as, the screen for entering the user name/password appears. If you leave the password empty as the user name "postgres" here, an error will occur.
Therefore, set the password for the postgres user on the Postgres side.
Then test again and the connection should succeed.
#In Docker running PostGIS
root@55f0d5aec9a0:/work# psql -U postgres
psql (12.5 (Debian 12.5-1.pgdg100+1))
Type "help" for help.
#Set password
postgres=# alter role postgres with password 'postgres';
ALTER ROLE
After the connection is completed, expand the connection destination added from the browser panel PostGIS, and line, point, polygon, roads will appear as items as shown below.
Displayed in QGIS
Try selecting planet_osm_line (double-click). Add it to the layer.
Since the line includes the ferry route, the Kinki region is small and the lines are densely displayed.
Adjust the QGIS zoom button to see the Kinki region
If you zoom in further and look around Kyoto Station, it looks like this
If you also select planet_osm_point in this state, it will look like this.
QGIS feature information display
After selecting the QGIS feature information display function, select point or line on the map to check various attribute values.
After selecting OpenStreetMap from "XYZ Tiles" in the browser panel, if you place it on the bottom layer in the layer, you can check the OSM data on the map of OpenStreetMap as shown below.
Easy feature narrowing with QGIS
For example, let's display only the mailbox around Kyoto Station. After unchecking planet_osm_line from the layer and leaving only points, right-click on the layer's planet_osm_point and select "Filter".
If you select "amenity" in the field and then press "All" for the value, the value panel will display a list of elements that are different in SQL from the amenity column of planet_osn_point.
If you double-click the field "amenity", the operator "=", and the value "post_box" in that order, the white filter expression part should become " amenity "='post_box', and when you press OK, the map The above Point is only a mailbox. (Changed the style of point to a big red circle for easy understanding)
If you are interested, please refer to the article about using Valhalla, a route search engine that uses OSM data.