Exercise 05: Introduction to the 3D CityDB

The goals of this exercise are to deepen your understanding of the CityGML concept and its mapping to Spatial Relational Databases. In the course of the exercise you will work with PostgreSQL, PostGIS, pgAdmin and the 3DCityDB along with the citydb-tools to enhance your understanding of the whole ecosystem from setting up a citydb instance to importing data and then understanding (in real-time) how the data (and the relationships between the data) is encoded into the different tables.

Once you are done with this exercise, you would have learnt these key points.

  1. Setting up your own instance of the 3DCityDB and working with Docker in general.

  2. Working with CityGML files in spatial relational databases - import/export using the citydb-tools.

  3. Understanding the mapping of objects and object-relationships from CityGML to 3DCityDB.

  4. Using SQL to query information from the 3DCityDB.

Data and Software required

The following set-up is required for this exercise.

  • Docker to initialise the 3DCityDB instance.

  • pgAdmin to interact with the 3DCityDB instance set up by you.

  • citydb-tool to import/export CityGML to/from the 3DCityDB

  • CityGML LoD3 Data given in the Data Repository or on Moodle.

  1. As practised at the end of Exercise 03: Working with CityGML, you need to set-up an instance of 3DCityDB using Docker on your systems.

  2. Import the data given (here or on Moodle under Exercise_05) using the citydb-tool which is used to import/export data from the 3DCityDB.

Important

  • Please refer to the end of Exercise 3 on how to import the data to the 3DCityDB if you do not remember.

Bug

  • Please reinstall docker if it is not working due to some dependency error.

  • Apple/Mac users - Please use the shell script commands given under the “Linux” tab in the documentation.

Exercise Tasks

Go through the 3DCityDB schema i.e., all the tables given under the citydb Schema and answer the following questions. Please refer to the 3DCityDB lecture slides in case you are confused about the Mapping of CityGML to the 3DCityDB instance.

Exercise questions (Conceptual) - To be submitted

  1. What information is stored in the feature table?

  2. In the lecture and the previous exercises, you have learned about unique identifiers such as the gml_id. How does the gml_id map to the database schema in this version of the database and in which table can you find this?

  3. How are addresses stored?

  4. Where are attributes stored in the database schema?

  5. Describe how attributes are referenced to their corresponding objects.

  6. Describe how semantic relationships between objects are represented.

Exercise questions (SQL) - Extra - Not subject to submission

Answer the following question with SQL queries. Refer to the SQL Lecture Slides and videos given here if you do not remember SQL from the previous semesters.

  1. How many windows does the Building in the database have? Query all the windows and output a table that shows all windows (along with their id, objectclass_id and objectid) and the corresponding walls they are located in. Group them by the walls.

Hint

All windows are embedded in walls i.e., the openings share an aggregation relationship with the walls.