Geoscience Reference
In-Depth Information
Fig. 11
Diagram of the logical model: entity tables, their attributes, and their relations (after Liu
2013
)
tables whose columns in turn correspond to the main attributes considered for each
entity. Primary key and possible foreign keys were identified as constraints in each
entity case.
In designing the physical model, geospatial data were transferred into a data-
base management system (DBMS)—for our purposes, Oracle v.3.2.20.09. This
phase consisted of a couple of steps that can be summarised as follows:
1. SQL was used to create entity tables depicted above, associate primary and for-
eign keys;
2. Given the fact basemap data available was 2D data, the 3D occurrences
were identified by combining different sources of data like 2D basemaps,
GoogleEarth imagery and photography—2D basemaps were imported into the
Feature Manipulator Engine (FME) Data Inspector in which feature point (x, y)
coordinates were retrieved;
3. Initial sketches were manually drawn as geometric abstractions of the case
studies' real infrastructures—these have the same appearance as that of the 3D
models to be shown on the computer screen;
4. 3D coordinate values (x, y, z) were derived for each object point—in all case
studies, lowest points within each infrastructure were assumed to be at the zero
level, building heights were worked out based on the number of floors, and
hence Z-coordinate values for all other points (Fig.
12
);
5. Geometric data were then inserted into the Oracle DBMS—taking into account
relationships between entities and associate constraints imposed by foreign
keys, value insertion followed the BUILDINGS, PART_GEOMETRIES,
OWNERS, and ONWERSHIPS order;
6. Building infrastructures within were divided into several constituent simple sol-
ids, each of which was stored as a new record of PART_GEOMETRIES table.
According to the format of SOD_GEOMETRY (Oracle
2009
), the format and
method to store simple solid is explained in Table
2
.
