Database Setup

The recommended way to set up a bare metal VC Database (VCDB) instance is by using the installation scripts provided as separate VCDB download package. These scripts are also included in each vcdb-tool distribution. Alternatively, use Docker for a simpler setup and deployment of a VCDB instance.

VCDB setup steps

Step 1: Create an empty PostgreSQL database

The first step is to create a new, empty database on your PostgreSQL server. Use a superuser or a database user with the CREATEDB permission to do so. While not required, it is recommended to select or create a dedicated user to own the database, who will also be used to run the VCDB setup scripts in later steps.

The following command demonstrates how to create a new database for the user citydb_user.

CREATE DATABASE citydb_v5 OWNER citydb_user;
You can execute this command using PostgreSQL’s command-line client psql or any other SQL tool, such as the graphical database client pgAdmin. Refer to the documentation of your preferred tool for more information.

Step 2: Add the PostGIS extension

The VC Database requires the PostGIS extension to be added to the database. This can only be done by a superuser. Use the following command to add the PostGIS extension:

CREATE EXTENSION postgis;
Optional

To enable advanced spatial 3D operations such as extrusion, volume calculation, or union/intersection/difference of volume geometries, you can additionally install the postgis_sfcgal extension:

CREATE EXTENSION postgis_sfcgal;

Step 3: Edit the connection-details script

Now it’s time to use the VCDB setup scripts. Navigate to the vcdb/postgresql/shell-scripts directory where you have unzipped the VCDB software package, or locate this folder within the installation directory of vcdb-tool. Then, change to the subfolder unix or windows, depending on your operating system.

Once inside, locate the connection-details.[sh|bat] script and open it with a text editor of your choice. Enter the database connection details along with the full path to the psql executable in this file. As mentioned above, it is recommended to provide the user who owns the database as PGUSER in the script.

Below is an example of the required information to include in the connection-details file.

  • Linux

  • Windows CMD

#!/bin/bash
# Provide your database details here ----------------
export PGBIN=/var/lib/postgresql/[version]/bin
export PGHOST=localhost
export PGPORT=5432
export CITYDB=citydb_v5
export PGUSER=citydb_user
#----------------------------------------------------
# Provide your database details here ----------------
set PGBIN=C:\Program Files\PostgreSQL\[version]\bin
set PGHOST=localhost
set PGPORT=5432
set CITYDB=citydb_v5
set PGUSER=citydb_user
#----------------------------------------------------
If the psql executable is already on your PATH, you can comment out or remove the line setting the PGBIN variable in the script.

Step 4: Execute the create-db script

Once the connection details have been set in the connection-details file, execute the create-db.[sh|bat] script located in the same folder to begin the setup process. You can do this either by double-clicking the script or by running it from within a shell environment. On UNIX/Linux machines, you may first need to set the appropriate file permissions to make the script executable.

  • Linux

  • Windows CMD

chmod u+x create-db.sh
./create-db.sh
create-db.bat

It is also possible to use a different connection-details file from another folder:

  • Linux

  • Windows CMD

./create-db.sh /path/to/connection-details.sh
create-db.bat C:\path\to\connection-details.bat

After executing the script, a welcome message along with usage hints will be displayed on the console. The script will prompt the user for several essential parameters required for setting up the VCDB instance. The details of these user inputs are explained in the following steps.

Step 5: Specify the coordinate reference system

You are prompted for the coordinate reference system (CRS) to be used for the VCDB instance. Simply enter the PostGIS-specific SRID (Spatial Reference ID) of the CRS. In most cases, the SRID will be identical to the EPSG code of the CRS. There are three parameters that need to be provided in this step:

  • The SRID to be used with the geometries stored in the database: Only one SRID can be used for the VCDB instance.

  • The EPSG code of the height system: This is considered metadata and does not affect the geometries in the database. If the above SRID already references a true 3D CRS or if the height system is unknown, enter "0" (meaning "not available"). This is also the default value.

  • The OGC-compliant name of the CRS: The CRS name is, for instance, written to CityGML/CityJSON files when exporting data from the database. The create-db script proposes a URN-encoded name based on your input, following OGC recommendations. Simply press Enter to accept the proposed value.

    urn:ogc:def:crs,crs:EPSG::<SRID>[,crs:EPSG::<HEIGHT_EPSG>]
The coordinate reference system can be changed at any time after setup using the database function citydb_pkg.change_schema_srid. Refer to the database functions section for more information.

Step 6: Create changelog extension

You can choose whether to create the changelog extension for your VCDB instance. The changelog extension adds a table to the VCDB schema, where insert, delete, and update operations on top-level city objects are tracked. Each changelog entry provides metadata about the affected city object (identifier, envelope, etc.), the database user executing the operation, a reason for the update, and the transaction type. Database triggers are installed to automatically populate the changelog table. As a result, insert, delete, and update operations may take longer when the changelog extension is used.

Enter yes or no to decide whether to install the changelog extension. The default value is no, which can be confirmed by simply pressing Enter.

The changelog extension can be installed and removed at any time after setup using the shell scripts create-changelog and drop-changelog.

Step 7: Execute the installation

Finally, enter the password for the database user provided in the connection-details script. Afterward, the setup process will begin, and log messages informing you about the progress will be printed to the console.

The setup process will terminate immediately if any errors occur. Possible reasons include:

  • The user executing create-db is neither a superuser nor the owner of the specified database (or does not have the privileges to create objects in that database).

  • The PostGIS extension has not been installed.

  • The specififed SRID is not supported.

  • Parts of the VCDB already exist because of a previous setup attempt. To prevent this, ensure that the schemas citydb, citydb_pkg, and vcdb_pkg do not exist in the database before starting the setup process.

If no error message is displayed, the setup has been successfully completed. The figure below shows a summary of the required user input for the create-db script.

image createDBScript
Figure 1. Example user input for the create-db script.
In addition to the connection-details and create-db shell scripts for setting up a VCDB instance, the VCDB package includes several other shell and SQL scripts for tasks such as removing a VCDB instance, creating additional schemas, and granting or revoking access permissions. Complete documentation of these database scripts is available here.

Using template databases to set up a VCDB

In PostgreSQL, template databases are pre-configured databases that serve as a base for creating new databases. Using a template database during database creation is a common practice. The new database is essentially created as a copy of one of the template databases, with a few modifications (such as the database name). These template databases enable quick database creation by copying an existing structure, schema, and even data.

You can also use a VCDB instance as a template database. However, be cautious, as the search_path is not automatically copied from the template database to the new VCDB instance. A correct search path is crucial for accessing data and database functions in the citydb, citydb_pkg, and vcdb_pkg schemas. Therefore, you must manually set the search path in your new VCDB instance:

ALTER DATABASE new_citydb_v5 SET search_path TO citydb, citydb_pkg, vcdb_pkg, public;
If your VCDB template contains more schemas, ensure to add them all to the search_path. Note that the search path will be updated upon the next login, not within the same session.

Upgrading a VCDB v5 to the latest release

If you already have a VCDB v5 installed and running, and want to update it to the latest release instead of setting up a new instance, please follow this guide.