3.2. Relational database schema

The simplified UML database model defined in the previous Section 3.1 has been mapped to a relational schema based on some general mapping rules and conventions. The following sections discuss these rules and present the relational schema in detail using entity-relationship diagrams. Based on this work, the platform-specific SQL scripts for setting up the 3D City Database on PostgreSQL and Oracle have been derived automatically.

• 3.2.1. Mapping rules and metadata
• 3.2.2. Core schema
• 3.2.3. Geometry schema
• 3.2.4. Appearance schema
• 3.2.5. Building schema
• 3.2.6. Bridge schema
• 3.2.7. CityFurniture schema
• 3.2.8. Generics schema
• 3.2.9. LandUse schema
• 3.2.10. Digital terrain schema
• 3.2.11. Transportation schema
• 3.2.12. Tunnel schema
• 3.2.13. Vegetation schema
• 3.2.14. WaterBody schema
• 3.2.15. Sequences
• 3.2.16. Managing ADE schemas

The figures for the entity-relationship diagrams are taken from Oracle JDeveloper (v12.2.1), which is used to model the database schema and extract SQL DDL scripts automatically for Oracle databases. It is a freeware IDE by Oracle and can be downloaded at https://www.oracle.com/application-development/technologies/jdeveloper.html.

For PostgreSQL databases, the open source tool pgModeler (v0.8.2) is used to maintain the schema. The source code is available on GitHub at https://github.com/pgmodeler/pgmodeler. Pre-built binaries can be purchased from the official website at https://www.pgmodeler.io/.

Starting from version 3.0.0 of the 3DCityDB, the corresponding schema modelling projects are shipped with the release and can be edited by the user to create customized SQL scripts. However, note that the 3DCityDB Importer/Exporter tool only supports the default schema, unless it is not reprogrammed against the user’s new database schema.