Exercise 06: Working with the 3DCityDB

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 will 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 practiced at the end of Exercise 03, 04: Working with CityGML, you need to set-up an instance of 3DCityDB using Docker on your systems.

  2. Import the an LoD3 model from the TUM2TWIN dataset (that you used in Exercise 03 & 04) 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 2DCityDB lecture slides in case you are confused about the Mapping of CityGML to the 3DCityDB instance.

Exercise questions (Conceptual)

Info

Submission: Answer the questions in any document - as a .txt, .doc or even as handwritten text.

  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. How are complex attributes managed?

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

  7. Describe how semantic relationships between objects are represented.

Exercise questions (SQL)

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. Write a query that evaluates how many buildings are present in the database.

  2. Write a query that lists all class names together with the number of corresponding objects.

  3. write a query that lists the wall objects in descending order of their surface area, with a limit of 10.

  4. Write a query that lists all buildings with the corresponding number of wall objects.

  5. Write an SQL statement making the geometries in geometry_data human-readable.

Hint

Leverage the well-known-text representation of geometries in PostGIS. Use the ST_AsText function to convert the geometry data into a readable format.

Submission

Submit the answers to all the questions in a single document (as a .txt, .doc or even as handwritten text) and upload it to Moodle. Additionally, submit all SQL-Queries that you wrote in a separate, dedicated markdown (.md) or SQL (.sql) file.