-
Notifications
You must be signed in to change notification settings - Fork 3
Installation Guide
This guide is intended as an instruction for the installation of a PostgreSQL database management system and the use of the OHDMConverter. The OHDMConverter serves as a tool to import geometric data in the form of osm files into the PostgreSQL database. In order to be able to use the OHDMConverter completely, several tools are required, the installation of which is shown in the following.
NOTE: Only Ubuntu 20.04 and Windows 10 have been successfully tested. Installation on a MacOS should work but without guarantee.
First we need the dbms itself, in this case we use PostgreSQL as the database management system.
Ubuntu 20.04
Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Update the package lists:
sudo apt-get update
Install the PostgreSQL.
sudo apt-get -y install postgresql-14
A server is also initialised within the installation. This server has the following values:
- Name of the server: main
- Port number: 5432
- Name of the database: postgres
- Name of the database owner: postgres
In addition you have to install PostGIS:
sudo apt-get install postgresql-14-postgis-3
PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.
Windows 10
Download PostgreSQL 14.2 → https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
Start the installation of PostgresSQL.
First you have to set the Installation Directory, (default): C:\Program Files\PostgreSQL\14
Be sure the you have all Components selected:
- PostgreSQL Server → this is the database management server itself
- pgAdmin 4 → This GUI-based tool serves as an additional administrative interface for managing PostgreSQL
- Stack Builder → This is used to install extensions for PostgreSQL.
- Command Line Tools → We need this tool for the later setup of the database.
Next, we need to define a directory where the data will be stored by the server,
(default): C:\Program Files\PostgreSQL\14\data
After that we have to assign a password for the superuser (postgres) of the database, i.e: my_password
We have to select the port number the server should listen on (default): 5432
In addition, we can select the locale, but also leave it default.
Now the installation of PostgreSQL should start. Once this is complete, the Stack Builder can be started directly. The Stack Builder can also be started manually afterwards.
In the Stack Builder, the running PostgreSQL server must be selected. Here PostgreSQL 14 on port 5432.
PostGIS must be installed as an extension, this can be found under Spatial Extensions and the latest version should be selected:
PostGIS 3.2 Bundle for PostgreSQL 14 (64bit) v3.2.0
Before the actual installation of PostGIS begins, the path for the downloaded files can be defined.
During the installation the path of the installation is requested, this can be the same as for PostgreSQL itself:
C:\Program Files\PostgreSQL\14.
It also asks about setting up several environment variables, which should all be activated for the OHDM project.
Afterwards the system should be restarted.
MacOS
Download PostgreSQL 14.2 → https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
Start the installation of PostgresSQL.
First you have to set the Installation Directory, (default): Library/PostgreSQL/14
Be sure the you have all Components selected:
- PostgreSQL Server → this is the database management server itself
- pgAdmin 4 → This GUI-based tool serves as an additional administrative interface for managing PostgreSQL
- Stack Builder → This is used to install extensions for PostgreSQL.
- Command Line Tools → We need this tool for the later setup of the database.
Next, we need to define a directory where the data will be stored by the server, (default): Library/PostgreSQL/14/data
After that we have to assign a password for the superuser (postgres) of the database, i.e: my_password
We have to select the port number the server should listen on (default): 5432
In addition, we can select the locale, but also leave it default.
Now the installation of PostgreSQL should start. Once this is complete, the Stack Builder can be started directly. The Stack Builder can also be started manually afterwards.
In the Stack Builder, the running PostgreSQL server must be selected. Here PostgreSQL 14 on port 5432.
PostGIS must be installed as an extension, this can be found under Spatial Extensions and the latest version should be selected:
PostGIS 3.2 Bundle for PostgreSQL 14 (64bit) v3.2.0
Before the actual installation of PostGIS begins, the path for the downloaded files can be defined.
During the installation the path of the installation is requested, this can be the same as for PostgreSQL itself: Library/PostgreSQL/14.
It also asks about setting up several environment variables, which should all be activated for the OHDM project.
Afterwards the system should be restarted.
NOTE No guarantee for functionality, as not tested
It is advantageous to have a project-specific database. This ensures the correct functioning of the OHDMConverter. In addition, a separate schema should be created within the database for each conversion. This example is limited to one additional creation of a schema.
Ubuntu 20.04
to start the PostgreSQL shell type:sudo -iu postgres psql -p [port_number] -U [database_user]
sudo -iu postgres psql
↑ to connect with the default database: main , on port: 5432
The shell should look like this: postgres=#
create new database
CREATE DATABASE ohdm;
connect to the new database
\c ohdm;
install necessary extension:
-
CREATE EXTENSION hstore;← to store sets of key/value pairs within a single PostgreSQL value -
CREATE EXTENSION postgis;← in order to create this extension, postgis had to be installed first.
You can check if you have installed the necessary extension by typing\dx
During installation, a password is set for the database user (default: postgres), but this is not known. The OHDMConverter needs the password to connect to the database. For this reason, the password must be set manually.
\password [database_user]
\password postgres
Enter a new password, e.g. my_password and enter it again.
For the following conversion method, we need to create a schema in which the converted data is stored.
CREATE SCHEMA inter;
after that the SQL Shell can be closed by typing exit or CTRL+D
Windows 10
to start the PostgreSQL shell: press the Windows key → type sql shell → Open SQL Shell (psql)
now the following parameters are requested, which can be left as default with Enter:
Server [localhost]: Name of the Server or ip address , press Enter
Database [postgres]: Name of the database to connect , press Enter
Port [5432]: The port set in the installation , press Enter
Username [postgres]: a user name with appropriate rights , press Enter
Password You must enter the password set during installation and press the Enter.
(see Chapter 1: Installation database management system (DBMS), if you followed completely this guide type my_password)
After that it should be displayed with psql the version number (14.2) and
the shell should look like this: postgres=#
create new database
CREATE DATABASE ohdm;
connect to the new database
\c ohdm;
install necessary extension:
-
CREATE EXTENSION hstore;← to store sets of key/value pairs within a single PostgreSQL value -
CREATE EXTENSION postgis;← in order to create this extension, postgis had to be installed first.
For the following conversion method, we need to create a schema in which the converted data is stored.
CREATE SCHEMA inter;
after that the SQL Shell can be closed by typing exit or press Alt+F4
MacOS
to start the PostgreSQL shell: open the Launchpad → type sql shell → open SQL Shell
now the following parameters are requested, which can be left as default with Enter:
Server [localhost]: Name of the Server or ip address , press Enter
Database [postgres]: Name of the database to connect , press Enter
Port [5432]: The port set in the installation , press Enter
Username [postgres]: a user name with appropriate rights , press Enter
Password You must enter the password set during installation and press the Enter.
(see Chapter 1: Installation database management system (DBMS), if you followed completely this guide type my_password)
After that it should be displayed with psql the version number (14.2) and
the shell should look like this: postgres=#
create new database
CREATE DATABASE ohdm;
connect to the new database
\c ohdm;
install necessary extension:
-
CREATE EXTENSION hstore;← to store sets of key/value pairs within a single PostgreSQL value -
CREATE EXTENSION postgis;← in order to create this extension, postgis had to be installed first.
For the following conversion method, we need to create a schema in which the converted data is stored.
CREATE SCHEMA inter;
after that the SQL Shell can be closed by typing exit
NOTE No guarantee for functionality, as not tested
In order to import an osm file into the OHDM database, several intermediate steps have to be processed. In the first conversion step, the OHDMConverter is used to fill an intermediate database with osm data. Several components are needed for this.
Ubuntu 20.04
First you should create a folder to store all components for the convertion at one place, i.e.
- create a new folder
mkdir ~/convert - change direction to the new folder
cd ~/convert
The OHDMConverter can be downloaded from the releases.
or with
wget https://github.com/OpenHistoricalDataMap/OHDMConverter/releases/download/v0.5.1/OHDMConverter_0_5_1.jar
install Java 11 if necessary
- OpenJDK https://wiki.ubuntuusers.de/Java/Installation/OpenJDK/
- Ocrale JDK (possible with login) https://www.oracle.com/java/technologies/downloads/
The JDBC driver is needed so that a Java application, like the OHDMConverter, can interact with a database.
can be downloaded at https://jdbc.postgresql.org/download.html
or with
wget https://jdbc.postgresql.org/download/postgresql-42.3.3.jar
NOTE: You must choose at least Version 42.2.1
All other instructions require a database parameter, for example for the conversion of osm data to the intermediate database an intermediate_parameter . This parameter is to be provided as a .txt-file and must be structured as follows:
create a new file: nano interdb.txt
servername : localhost
portnumber : 5432
username : postgres
pwd : my_password
dbname : ohdm
schema : inter
An .osm-flie is required for the conversion. A very small .osm-file can be downloaded from the following link.
https://api.openstreetmap.org/api/0.6/map?bbox=13.52157,52.45568,13.52936,52.45923
or with
wget -O map.osm "https://api.openstreetmap.org/api/0.6/map?bbox=13.52157,52.45568,13.52936,52.45923"
This file contains the area of the HTW Berlin Campus Wilhelminenhof.
Windows 10
First you should create a folder to store all components for the convertion at one place, i.e.
- Right-click on a blank space at your Desktop
- select New in the contextual menu and click Folder
- As soon as you do that, Windows makes a new folder on your desktop. Name it as you like, i.e.
convertand press Enter
The OHDMConverter can be downloaded from the releases.
or direct with https://github.com/OpenHistoricalDataMap/OHDMConverter/releases/download/v0.5.1/OHDMConverter_0_5_1.jar
install at least Java 11 if necessary
- OpenJDK https://openjdk.java.net/
- Ocrale JDK (possible with login) https://www.oracle.com/java/technologies/downloads/#java11-windows
The JDBC driver is needed so that a Java application, like the OHDMConverter, can interact with a database.
can be downloaded at https://jdbc.postgresql.org/download.html
direct link for current Version 42.3.3
NOTE: You must choose at least Version 42.2.1
All other instructions require a database parameter, for example for the conversion of osm data to the intermediate database an intermediate_parameter . This parameter is to be provided as a .txt-file and must be structured as follows:
go to your previous created folder convert and create a new .txt-file
- Right-click in the folder and go to New → Text Document
- type
interdband press Enter - now open the
interdb.txtfile with double click or press Enter again, if the file is still selected.
servername : localhost
portnumber : 5432
username : postgres
pwd : my_password
dbname : ohdm
schema : inter
An .osm-flie is required for the conversion. A very small .osm-file can be downloaded from the following link.
https://api.openstreetmap.org/api/0.6/map?bbox=13.52157,52.45568,13.52936,52.45923
This file contains the area of the HTW Berlin Campus Wilhelminenhof.
MacOS
FIXME
First you should create a folder to store all components for the convertion at one place, i.e. convert
The OHDMConverter can be downloaded from the releases.
or direct with https://github.com/OpenHistoricalDataMap/OHDMConverter/releases/download/v0.5.1/OHDMConverter_0_5_1.jar
install at least Java 11 if necessary
The JDBC driver is needed so that a Java application, like the OHDMConverter, can interact with a database.
can be downloaded at https://jdbc.postgresql.org/download.html
direct link for current Version 42.3.3
NOTE: You must choose at least Version 42.2.1
All other instructions require a database parameter, for example for the conversion of osm data to the intermediate database an intermediate_parameter . This parameter is to be provided as a .txt-file and must be structured as follows:
go to your previous created folder convert and create a new .txt-file
servername : localhost
portnumber : 5432
username : postgres
pwd : my_password
dbname : ohdm
schema : inter
An .osm-flie is required for the conversion. A very small .osm-file can be downloaded from the following link.
https://api.openstreetmap.org/api/0.6/map?bbox=13.52157,52.45568,13.52936,52.45923
This file contains the area of the HTW Berlin Campus Wilhelminenhof.
Make sure that all components (see Chapter 3: Preparation for the conversions) are in the same place/folder. If you have followed the instructions, you should have:
- one folder, like:
convert - in the folder there are 4 files, like:
- OHDMConverter_0_5_1.jar
- postgresql-42.3.3.jar
- interdb.txt
- map.osm
Now you can follow the first instruction to convert a .osm-file to the intermediate database.
open a terminal in the folder with the 4 files and type:
java -p postgresql-42.3.3.jar -jar OHDMConverter_0_5_1.jar -o map.osm -i interdb.txt
for further instructions see: https://github.com/OpenHistoricalDataMap/OHDMConverter/wiki/O2I
Run multiple PostgreSQL server of one host
Sometimes it is necessary to have more than one PostgreSQL server instance on a system.
For example, on the OHM server runs two PostgreSQL instances, one to try out with the smaller dataset and the other with the planet.osm data.
The following instructions show how to instantiate another one on one system.
The storage area of the database on the hard disk, i.e. the PostgreSQL server instance, is named cluster according to the SQL standard.
Ubuntu 20.04
| initialize database | pg_createcluster [postgresql_version_number] [clustername] -p [port] |
pg_createcluster 14 example -p 5433 |
|
| start database cluster | pg_ctlcluster [postgresql_version_number] [clustername] start |
pg_ctlcluster 14 example start |
|
| register as a service | systemctl enable postgresql@[postgresql_version_number]-[clustername].service |
systemctl enable postgresql@14-example.service |
NOTE: it seems to be forbidden to use 'test' as cluster name
Windows 10
In default there is a server namend main on port 5432. To additionally create PostgreSQL Cluster you need initdb and pg_ctl.
You can run both executeble files from the folder (e.g. C:\Program Files\PostgreSQL\14\bin\)
First, a new folder should be created. To do this, open Explorer and, for example, type in the location bar %USERPROFILE%
- Right-click on a blank space at your Desktop
- select New in the contextual menu and click Folder
- As soon as you do that, Windows makes a new folder on your desktop. Name it as you like, i.e.
exampleand press Enter.
| initialize database | initdb.exe -D [path\to\database] |
initdb.exe -D %USERPROFILE%\example |
|
| start database cluster | pg_ctl.exe -D [path\to\database] -o "-p [port_number]" -l logfile start |
pg_ctl.exe -D %USERPROFILE%\example -o "-p 5433" -l %USERPROFILE%\example\example_cluster.log start |
|
| register as a service | pg_ctl.exe register -N [service_name] -D [path\to\database] |
pg_ctl.exe register -N example -D %USERPROFILE%\example |
MacOS
TODO
Allow remote access
If the PostgreSQL database is running on a server, it should also be accessible from outside. To ensure this, 2 files must be edited.
postgresql.conf , pg_hba.conf
these files can be found at Database installation location i.e.:
-
Linux:
/etc/postgresql/14/main -
Windows:
C:\Program Files\PostgreSQL\14\data
change in postgresql.conf
| from | to |
|---|---|
listen_addresses = 'localhost' # what IP address(es) to listen on; |
listen_addresses = '*' # what IP address(es) to listen on; |
add at the end of pg_hba.conf
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
the line
host all all 141.45.0.0/16 scram-sha-256
to allow all devices there are connected with i.e. the HTW network
Get osm file
OSM files can be exported via the official website
https://www.openstreetmap.org/, but you have to pay attention to the size.
To fetch larger OSM data, they must be fetched from the Planet OSM site
https://planet.openstreetmap.org/
On this page you can find geodata by continent, country or region https://download.geofabrik.de
In order for the large data to be saved at all, the osm files must be saved in compressed form. However, such a compressed package must be decompressed so that one can work with the plain osm data.
Ubuntu 20.04
If you download for example
wget https://download.geofabrik.de/europe/germany/berlin-latest.osm.bz2
now you can convert the osm.bz2 file to osm like
bunzip2 -kf berlin-latest.osm.bz2
If you download for example
wget https://download.geofabrik.de/europe/germany/berlin-latest.osm.pbf
You have to install an additionally converter
sudo apt install osmctools
now you can convert the osm.pbf file to osm like
osmconvert berlin-latest.osm.pbf >berlin.osm
Windows 10
If you download for example https://download.geofabrik.de/europe/germany/berlin-latest.osm.bz2
now you can decompress with 7zip
authentication type 10
It may happen that an error occurs during the conversion:
The authentication type 10 is not supported. Check that you have configured the pg_hba.conf ...
Make sure that all versions are correct:
- PostgreSQL: at least version 14
- PostGIS: at least version 3
- Java: at least version 11
for these versions the installation guide has been written and tested.
NOTE: Only for Ubuntu 20.04 and Windows 10