-
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.
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 postgis
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-downloadsStart 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: postgres
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: postgres
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.
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.
ALTER USER [database_user] WITH PASSWORD '[database_user_password]';
ALTER USER postgres WITH PASSWORD 'my_password';
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
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.
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
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.
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 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
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 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 Java 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
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
TODO
Make sure that all components 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 -classpath 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 [email protected] |
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.:
-
Windows:
C:\Program Files\PostgreSQL\14\data -
Linux:
/var/lib/postgresql/14/main
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 md5
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
the line
host all all 141.45.0.0/16 md5
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 ...
Ubuntu 20.04
goto dircetory of PostGreSQL, for example:
/var/lib/postgresql/14/main
open the file postgresql.conf and change the entry
#password_encryption = scram-sha-256 to password_encryption = md5
open the file pg_hba.conf and change all entries in row 'METHOD' to md5
open a SQL Shell like sudo -iu postgres psql
and change the password of the database user, i.e.
ALTER USER postgres WITH PASSWORD 'my_password';
after that you have to restart the postgresql server:
systemctl restart postgresql@[postgresql_version_number]-[clustername].service
systemctl restart [email protected] ← to restart default PostgreSQL Server on your system
Windows 10
goto dircetory of PostGreSQL, for example:
C:\Program Files\PostgreSQL\14\data
open the file postgresql.conf and change the entry
#password_encryption = scram-sha-256 to password_encryption = md5
open the file pg_hba.conf and change all entries in row 'METHOD' to md5
open a SQL Shell press the Windows key → type sql shell → Open SQL Shell (psql)
and change the password of the database user, i.e.
ALTER USER postgres WITH PASSWORD 'my_password';
after that you have to restart the postgresql server:
first find the pg_ctl.exe this executable file is in subfolder \bin of your installed PostgreSQL
i.e. C:\Program Files\PostgreSQL\14\bin
open a terminal in this folder and type pg_ctl.exe -D C:\Program Files\PostgreSQL\14\data restart