Towards Integrating Heterogeneous Data: A Spatial DBMS Solution from a CRC-LCL Project in Australia †
Abstract
:1. Introduction
1.1. Existing Approaches and Challenges
1.2. Contributions
- A novel perspective on PIM is proposed considering multi-source heterogeneous data for the sake of improving the potential values and interpretation performance of the built environment at campus scale.
- A general conceptual design and relational database design has been developed for unified 3D city model in an integrating manner.
- A usability analysis of our SDBMS solution through a real-world campus model.
1.3. Organization
2. Background and Related Work
2.1. 3D City Models and PIM
2.2. Smart Built Environment and Data Fusion Opportunities
2.3. Geo-Data Modeling in Spatial DBMS
2.4. CityGML and IFC Standards
2.5. Related Work for CityGML and IFC Integration
3. Precinct Information Modeling (PIM) with Data Fusion
- A Unified Model at Precinct Level (Section 3)To achieve a more compact database schema and improve query performance, the CityGML and IFC model are combined into a simple and unified model at some critical points.
- Derivation of the Relational Database Schema (Section 4)The unified object-oriented data model has been mapped to relational tables. The number of tables were optimized to minimize the number of joins for typical queries.
- Classes painted in YELLOW belong to the pure CityGML model which is subject of discussion in the following subsections.
- Classes painted in GREEN belong to the pure IFC model.
- Classes painted in BLUE are integrated objects from both IFC and CityGML model which are defined in Section 3.2.1.
- Classes painted in PINK are from sensor data, such as gas and electricity.
3.1. Conceptual PIM at Campus Scale
- Building
- Sensor
- City furniture
- Terrain
- Transportation
- Vegetation
- Land use
3.2. Building Model
3.2.1. Integrated Building Model (IBM)
- _Wall is a vertical/semi-vertical element that surrounds or subdivides spaces. It has three subtypes:
- –
- InteriorWall for an internal wall between rooms or spaces (none of its faces has connection with the outer environment);
- –
- ExteriorWall for an external wall that has connection with the outer environment and represents a part of external facades of a building;
- –
- CurtainWall for the outer wall that covers a complete facade of a building or a part of it.
- _Covering is a closing level that covers a space from the top side. It has three types:
- –
- Roof for the top covering of a building or the top storey which gives the external shape of a building from above;
- –
- Ceiling for the internal covering of any space in a building;
- –
- OuterCeiling for the external covering.
- _Level is a walkable (not only horizontal) level that represents the bottom level of a space. It has three types:
- –
- Ground for the bottom level of ground floor which has a connection to the outer ground to give the external shape of a building from the bottom level;
- –
- Floor for the bottom level of a space in any space of a building except the bottom (lowest) storey;
- –
- OuterFloor for the horizontal surface belonging to the outer building shell and with the orientation pointing upwards.
3.2.2. Conversion from IFC & CityGML to IBM
From IFC to IBM
From CityGML to IBM
3.3. Sensor Model
3.4. LandUse Model
3.5. CityFurniture Model
3.6. Transportation Model
3.7. Vegetation Model
3.8. Terrain Model
4. Database Solutions for PIM
- A class shall be mapped into one single table. The mapped table shall have at least one primary key column to store the object identifier which may be known as “ID” and must be unique within the table. Additional columns can also be added to the mapped table for storing the spatial and non-spatial attribute values of the respective class objects.
- A foreign key constraint needs to be added in case of 1:1 or 1:N relationship. For each binary 1:1 or 1:N relationship type, we choose one of the relations and include as a foreign key in the chosen relation the primary key of another relation. It is better to identify the relation S that represents the participating at the N-side of the relationship type.
- An associative table in case of M:N relationship shall be used to link the tables mapped from the associated classes. For each binary N:M relationship type, create a new relation to represent this relationship. Include as foreign key attributes in the chosen relation the primary keys of the relations that represent the participating relations; their combination will form the primary key of the chosen one.
- A foreign key constraint or an associative table needs to be set for inheritance relationship. The inheritance relationship between two classes can either be implemented using a foreign key constraint to link the subclass and superclass tables by joining their primary keys or mapped to a table that represents the two inherited classes at the same time.
- Mapping classes in inheritance relationship or same hierarchy level into one table. We assume that in most cases, subclasses may have or set the same attribute list due to data missing or multiple unique attributes make no contribution to special applications. With this consideration, some classes belonging to an inheritance hierarchy can be mapped into one single table, which results in the retrieval of data in all subclasses just need to perform queries on one table in order to avoid multiple tables joins for speeding up the overall performance. This way, the single table allows for rapid retrieving a list of different objects through a query on the category attribute which distinguishes instances objects stored in the table from different types. For detail, we can add an additional column named “OBJECTCLASS_ID” or “OBJECTCLASS_NAME” which can store a numeric value or string value in each row for representing the respective class type.
- Mapping aggregations and compositions into one table. Due to our building is objected-oriented, aggregation and composition relations of classes can be properly modeled by using a foreign key for joining each class with its parent class. For special case that recursion appears in aggregation or composition relationships, a single table for mapping of all the involved classes along with their inheritance relationship can be added in the database. For detail, we can add an additional column “PARENT_ID” as the foreign key which is used for representing the composition relationship.
5. Case Study
5.1. Conceptual Design
5.2. Relational Database Design
6. Discussion and Conclusions
Author Contributions
Funding
Conflicts of Interest
Abbreviations
BIM | Building Information Modeling |
CAD | Computer Aided Design |
DBMS | Database Management System |
GIS | Geographic Information System |
GML | Graphics Mark-up Language |
IFC | Industry Foundation Classes |
ISO | International Standards Organization |
OGC | Open Geospatial Consortium |
PIM | Precinct Information Modeling |
SQL | Structured Query Language |
Appendix A. IFC Building Model
Appendix B. CityGML Building Model
References
- Biljecki, F.; Stoter, J.; Ledoux, H.; Zlatanova, S.; Çöltekin, A. Applications of 3D city models: State of the art review. ISPRS Int. J. Geo-Inf. 2015, 4, 2842–2889. [Google Scholar] [CrossRef][Green Version]
- Murata, M. 3D-GIS application for urban planning based on 3D city model. In Proceedings of the 24th Annual ESRI International User Conference, San Diego, CA, USA, 9–13 August 2004. [Google Scholar]
- Shiode, N. 3D urban models: Recent developments in the digital modelling of urban environments in three-dimensions. GeoJournal 2000, 52, 263–269. [Google Scholar] [CrossRef]
- Fadli, F.; Kutty, N.; Wang, Z.; Zlatanova, S.; Mahdjoubi, L.; Boguslawski, P.; Zverovich, V. Extending indoor open street mapping environments to navigable 3D CityGML building models: Emergency response assessment. Int. Arch. Photogramm. Remote Sens. Spat. Inf. Sci. 2018, 42, 161–168. [Google Scholar] [CrossRef][Green Version]
- Walenciak, G.; Stollberg, B.; Neubauer, S.; Zipf, A. Extending spatial data infrastructures 3D by geoprocessing functionality-3D simulations in disaster management and environmental research. In Proceedings of the 2009 International Conference on Advanced Geographic Information Systems & Web Services, Cancun, Mexico, 1–7 February 2009; pp. 40–44. [Google Scholar]
- Krüger, A.; Kolbe, T. Building analysis for urban energy planning using key indicators on virtual 3D city models—The energy atlas of Berlin. Int. Arch. Photogramm. Remote Sens. Spat. Inf. Sci. 2012, 39, 145–150. [Google Scholar] [CrossRef][Green Version]
- Breunig, M.; Zlatanova, S. 3D geo-database research: Retrospective and future directions. Comput. Geosci. 2011, 37, 791–803. [Google Scholar] [CrossRef]
- Lo, C.P.; Yeung, A.K. Concepts and Techniques of Geographic Information Systems; Prentice Hall: Upper Saddle River, NJ, USA, 2002. [Google Scholar]
- Azhar, S.; Khalfan, M.; Maqsood, T. Building information modelling (BIM): Now and beyond. Construct. Econ. Build. 2012, 12, 15–28. [Google Scholar] [CrossRef][Green Version]
- Newton, P.; Plume, J.; Marchant, D.; Mitchell, J.; Ngo, T. Precinct information modelling: a new digital platform for integrated design, assessment and management of the built environment. In Integrating Information in Built Environments; Routledge: London, UK, 2017; pp. 111–132. [Google Scholar]
- Cai, G. Contextualization of geospatial database semantics for human–GIS interaction. Geoinformatica 2007, 11, 217–237. [Google Scholar] [CrossRef]
- Gröger, G.; Kolbe, T.; Nagel, C.; Häfele, K. OGC City Geography Markup Language (CityGML) Encoding Standard; Open Geospatial Consortium: Wayland, MA, USA, 2012. [Google Scholar]
- ISO. Industry Foundation Classes (IFC) for Data Sharing in The Construction and Facility Management Industries; International Organization for Standardization: Geneva, Switzerland, 2013. [Google Scholar]
- Güting, R.H. An introduction to spatial database systems. VLDB J. Int. J. Very Large Data Bases 1994, 3, 357–399. [Google Scholar] [CrossRef]
- Tolmer, C.E.; Castaing, C.; Diab, Y.; Morand, D. CityGML and IFC: Going further than LOD. In Proceedings of the 2013 Digital Heritage International Congress (DigitalHeritage), Marseille, France, 28 October–1 November 2013; Volume 1, pp. 645–648. [Google Scholar]
- Deng, Y.; Cheng, J.C.; Anumba, C. Mapping between BIM and 3D GIS in different levels of detail using schema mediation and instance comparison. Autom. Construct. 2016, 67, 1–21. [Google Scholar] [CrossRef]
- Liu, X.; Wang, X.; Wright, G.; Cheng, J.; Li, X.; Liu, R. A state-of-the-art review on the integration of Building Information Modeling (BIM) and Geographic Information System (GIS). ISPRS Int. J. Geo-Inf. 2017, 6, 53. [Google Scholar] [CrossRef][Green Version]
- Ghamisi, P.; Rasti, B.; Yokoya, N.; Wang, Q.; Hofle, B.; Bruzzone, L.; Bovolo, F.; Chi, M.; Anders, K.; Gloaguen, R.; et al. Multisource and Multitemporal Data Fusion in Remote Sensing. arXiv 2018, arXiv:1812.08287. [Google Scholar]
- Zhu, Q.; Hu, M.; Zhang, Y.; Du, Z. Research and practice in three-dimensional city modeling. Geo-Spat. Inf. Sci. 2009, 12, 18–24. [Google Scholar] [CrossRef]
- Billen, R.; Cutting-Decelle, A.F.; Marina, O.; De Almeida, J.P.; Caglioni, M.; Falquet, G.; Leduc, T.; Metral, C.; Moreau, G.; Perret, J.; et al. 3D City Models and Urban Information: Current Issues and Perspectives-European COST Action TU0801; EDP Sciences: Les Ulis, France, 2014. [Google Scholar]
- Nakashima, H.; Aghajan, H.; Augusto, J.C. Handbook of Ambient Intelligence and Smart Environments; Springer Science & Business Media: Cham, Switzerland, 2009. [Google Scholar]
- Zhang, J.; Seet, B.C.; Lie, T. Building information modelling for smart built environments. Buildings 2015, 5, 100–115. [Google Scholar] [CrossRef][Green Version]
- Bleiholder, J.; Naumann, F. Data fusion. ACM Comput. Surv. (CSUR) 2009, 41, 1. [Google Scholar] [CrossRef]
- Dong, J.; Zhuang, D.; Huang, Y.; Fu, J. Advances in multi-sensor data fusion: Algorithms and applications. Sensors 2009, 9, 7771–7784. [Google Scholar] [CrossRef][Green Version]
- Rigaux, P.; Scholl, M.; Voisard, A. Spatial Databases: With Application to GIS; Elsevier: Amsterdam, The Netherlands, 2001. [Google Scholar]
- Zlatanova, S. 3D geometries in spatial DBMS. In Innovations in 3D Geo Information Systems; Springer: Berlin/Heidelberg, Germany, 2006; pp. 1–14. [Google Scholar]
- Wang, X.; Love, P.E.; Kim, M.J.; Park, C.S.; Sing, C.P.; Hou, L. A conceptual framework for integrating building information modeling with augmented reality. Autom. Construct. 2013, 34, 37–44. [Google Scholar] [CrossRef]
- Nagel, C.; Stadler, A.; Kolbe, T.H. Conceptual requirements for the automatic reconstruction of building information models from uninterpreted 3D models. In Proceedings of the Academic Track of the Geoweb 2009-3D Cityscapes Conference, Vancouver, BC, Canada, 27–31 July 2009. [Google Scholar]
- El-Mekawy, M.; Östman, A.; Hijazi, I. A unified building model for 3D urban GIS. ISPRS Int. J. Geo-Inf. 2012, 1, 120–145. [Google Scholar] [CrossRef][Green Version]
- Cheng, J.C.; Deng, Y.; Anumba, C. Mapping BIM schema and 3D GIS schema semi-automatically utilizing linguistic and text mining techniques. J. Inf. Technol. Construct. 2015, 20, 193–212. [Google Scholar]
- Kang, T.W.; Hong, C.H. A study on software architecture for effective BIM/GIS-based facility management data integration. Autom. Construct. 2015, 54, 25–38. [Google Scholar] [CrossRef]
- Agoub, A.; Kunde, F.; Kada, M. Potential of graph databases in representing and enriching standardized Geodata. Tagungsband der 2016, 36, 1–9. [Google Scholar]
- Stadler, A.; Nagel, C.; König, G.; Kolbe, T.H. Making interoperability persistent: A 3D geo database based on CityGML. In 3D Geo-Information Sciences; Springer: Berlin/Heidelberg, Germany, 2009; pp. 175–192. [Google Scholar]
- Robert McNeel & Associates. Rhinoceros: 3D Computer Graphics and Computer-Aided Design Application). 2019. Available online: https://www.rhino3d.com/ (accessed on 27 May 2019).
- IFC++. IFC++: Open Source IFC Implementation for C++). 2019. Available online: http://ifcquery.com/ (accessed on 27 May 2019).
- The CGAL Project. CGAL User and Reference Manual, 4th ed.; CGAL Editorial Board: Tel-Aviv, Israel, 2019. [Google Scholar]
- Li, W.; Zlatanova, S.; Yan, J.; Diakite, A.; Aleksandrov, M. A Geo-Database Solution for the Management and Analysis of Building Model with Multi-Source Data Fusion. Int. Arch. Photogramm. Remote Sens. Spat. Inf. Sci. 2019, 42, 55–63. [Google Scholar] [CrossRef][Green Version]
- Emgård, L.; Zlatanova, S. Implementation alternatives for an integrated 3D Information Model. In Advances in 3D Geoinformation Systems; Springer: Berlin/Heidelberg, Germany, 2008; pp. 313–329. [Google Scholar]
- Zlatanova, S.; Pu, S.; Bronsvoort, W. Freeform curves and surfaces in DBMS-a step forward in spatial data integration. In Proceedings of the ISPRS Commission IV Symposium on ‘Geospatial Databases for Sustainable Development, Goa, India, 27–30 September 2006; pp. 27–30. [Google Scholar]
LOD | Mapping Rule |
---|---|
LOD1 | _AbstractBuilding ⇒ _Building |
LOD2 | RoofSurface ⇒ Roof |
OuterCeilingSurface ⇒ OuterCeiling | |
WallSurface ⇒ ExteriorWall | |
GroundSurface ⇒ Ground | |
FloorSurface ⇒ OuterFloor | |
_BoundarySurface ⇒ _BoundarySurface | |
BuildingInstallation ⇒ _BuildingInstallation | |
LOD3 | IfcBuildingElement ⇒ _BuildingElement |
[_Opening,IfcOpeningElement] ⇒ _Opening | |
[Window,IfcWindow] ⇒ Window | |
[Door,IfcDoor] ⇒ Door | |
IfcWall ⇒ ExteriorWall | |
IfcCurtainWall ⇒ CurtainWall | |
IfcRoof ⇒ Roof | |
IfcSlab ⇒ Ground | |
[IfcWall,IfcRoof,IfcSlab] ⇒ _BoundarySurface | |
[IfcBuildingStorey, _BoundarySurface] ⇒ BuildingStorey | |
LOD4 | [InteriorWallSurface,IfcWall] ⇒ InteriorWall |
[CeilingSurface,IfcSlab] ⇒ Ceiling | |
[FloorSurface,IfcSlab] ⇒ Floor | |
[Room, _BoundarySurface] ⇒ Space | |
BuildingFurniture ⇒ BuildingFurniture | |
IntBuildingInstallation ⇒ BuildingInstallation |
UML | PostgreSQL/PostGIS |
---|---|
String, anyURI | VARCHAR/TEXT |
Int | INT/NUMERIC |
Double | FLOAT/REAL/NUMERIC |
Boolean | BOOLEAN |
Date | DATE/TIMESTAMP |
Pcpatch | PCPATCH |
GML Geometry | GEOMETRY |
© 2020 by the authors. Licensee MDPI, Basel, Switzerland. This article is an open access article distributed under the terms and conditions of the Creative Commons Attribution (CC BY) license (http://creativecommons.org/licenses/by/4.0/).
Share and Cite
Li, W.; Zlatanova, S.; Diakite, A.A.; Aleksandrov, M.; Yan, J. Towards Integrating Heterogeneous Data: A Spatial DBMS Solution from a CRC-LCL Project in Australia. ISPRS Int. J. Geo-Inf. 2020, 9, 63. https://doi.org/10.3390/ijgi9020063
Li W, Zlatanova S, Diakite AA, Aleksandrov M, Yan J. Towards Integrating Heterogeneous Data: A Spatial DBMS Solution from a CRC-LCL Project in Australia. ISPRS International Journal of Geo-Information. 2020; 9(2):63. https://doi.org/10.3390/ijgi9020063
Chicago/Turabian StyleLi, Wei, Sisi Zlatanova, Abdoulaye A. Diakite, Mitko Aleksandrov, and Jinjin Yan. 2020. "Towards Integrating Heterogeneous Data: A Spatial DBMS Solution from a CRC-LCL Project in Australia" ISPRS International Journal of Geo-Information 9, no. 2: 63. https://doi.org/10.3390/ijgi9020063