6.4. Enriching visualization models with thematic data

As mentioned before, the 3D web client extends the Cesium Virtual Globe to support efficient displaying, caching, dynamic loading and unloading of large pre-styled 3D visualization models in the form of tiled KML/glTF datasets exported the 3DCityDB using the KML/COLLADA/glTF Exporter. However, there is a major problem regarding the graphical visualization of semantic 3D city models as their attribute information is completely or partly lost in the 3D graphics formats. This issue has been considered and solved within the 3D web client by supporting the explicit linking of the 3D visualization models with their thematic data, which can be achieved using (1) a Google Spreadsheet stored in the cloud, (2) PostgREST, a RESTful API for PostgreSQL, (3) OGC Feature API, or (4) existing thematic data already embedded within the datasets.

6.4.1. Storing thematic data in Google Spreadsheets

The thematic data stored in the 3DCityDB can be exported to a single table (as a CSV .csv or an MS Excel .xlsx file) using the Spreadsheet Generator Plugin (SPSHG) explained in Section 5.2. This can then be uploaded in Google Drive as Google Spreadsheets.

This strategy can therefore offer the possibilities for collaborative and interactive data exploration of semantic 3D city models by means of querying the thematic data of the selected city object. The corresponding system architecture is illustrated in the following figure.

Fig. 6.7 Coupling an online spreadsheet with a 3D visualization model (i.e. a KML/glTF visualization model) in the cloud [HeNK2012]

Fig. 6.8 Example of an online spreadsheet

6.4.2. Publishing thematic data using RESTful API

Alternatively, the thematic data can be published directly from the 3DCityDB using a RESTful API that is supported by the employed database (such as PostgREST for PostgreSQL).

The RESTful API allows publishing database tables or SQL views on the internet. This approach therefore provides developers full control over: (1) where the resources are being hosted (i.e. independent from third-party cloud service providers), (2) which resources should be published, and (3) who can have access to which resources.

This approach requires however a good understanding of the 3DCityDB schemata (see Section 3.2) as well as familiarity with SQL in general (in order to define custom SQL views to publish directly from the database). For PostgREST, a very good tutorial is available.

Collaborative editing of the published thematic data is theoretically possible, it depends however greatly on the implementation of the employed RESTful services on the database side.

6.4.3. Structure of the tables containing thematic data

Since Google Fusion Tables was shut down on Dec 3, 2019, starting from version 1.9.0, the 3DCityDB Web Map Client is capable of fetching data published from Google Sheets API v4 and a PostgreSQL database with a RESTful API enabled (PostgREST). Data fetched from Google Sheets API and PostgREST can be displayed on the infobox as thematic data when a city object is clicked. Please refer to Section 6.4.6 for a brief tutorial as how to import visualization models with thematic data in the 3D web client.

Based on the two new supported relational data sources, it is now also possible to choose their tableType between All object attributes in one row (horizontal) and One row per object attribute (vertical), where:

• Horizontal: all object attributes are stored in columns of one single row, which means each ID occurs only once in the table. This is applicable if all objects have the same or similar attributes.

  Note

  The thematic data must be stored in the first sheet of the spreadsheet. The first column of this sheet must be called gmlid or GMLID.

  Example:

  gmlid	attribute1	attribute2	attribute3	attribute4
  gmlid1	value1	value2	value3	value4
  gmlid2	value1	value2	value3	value4

• Vertical: each object attribute is stored in one row consisting of three columns ID, Attribute and Value, which means an ID may occur in multiple rows in the table. This is used when the numbers of attributes or attribute names vary greatly between objects.

  Note

  A vertical table must contain exactly three columns in this exact order: gmlid, attribute and value.

  Example:

  gmlid	attribute	value
  gmlid1	attribute1	value1
  gmlid1	attribute2	value2
  gmlid1	attribute3	value3
  gmlid2	attribute1	value1
  gmlid2	attribute2	value2
  gmlid2	attribute3	value3
  gmlid2	attribute4	value4

For an overview of the responses from the Google Sheets API, please refer to the official documentation.

The response from PostgREST service is encoded in JSON with the following structure:

• Both the horizontal and vertical mode consist of an array of records marked by the [ ... ].

• Each record represents a line in the table, where:

  • Each record in vertical mode only has exactly 3 elements: gmlid, attribute name and attribute value. The gmlids here can be duplicated in other records, but the combination of the first two columns must be unique.

    [
       { "gmlid" : "id1", "attribute" : "value_name", "value" : "value" },
       { "gmlid" : "id2", "attribute" : "value_name", "value" : "value" },
       ...
    ]
    

  • On the other hand, each record in the horizontal mode can have more than 2 elements, but the first one must always be gmlid and this must be unique for each record.

6.4.4. Retrieving thematic data using OGC Feature API

OGC Feature API enables access to geospatial features available on the web. Like PostgREST, data can be queried using HTTP GET requests. Paremeters and query conditions can be incorporated as strings into the request, such as:

GET /collections/buildings/items?bbox=min_x,min_y,max_x,max_y

The query results are feature collections that can be in many formats, such as HTML, GeoJSON or GML. For more information on the documentation and development of the OGC Feature API, please refer to its GitHub repository.

Starting from version 2.0.0, the 3D web client also supports OGC Feature API as another thematic data source. This API can be applied to any visualization datasets, as long as the identifiers of their objects match those accessible via the API.

Note

Due to the different implementation of the API across regions and countries, the current version 2.0.0 provides some examples for handling the OGC Feature API implementations provided by the German states of Hamburg and North Rhine-Westphalia.

6.4.5. Retrieving thematic data already embedded within visualization datasets

Many visualization datasets, such as KML, GeoJSON, Cesium 3D Tiles and Indexed 3D Scene Layers (I3S) allow for incorporating thematic data within their structure. Starting from version 2.0.0, the 3D web client is capable of retrieving such data embedded within the above mentioned types of visualization datasets.

Note

Due to the inconsistent labelling of object identifiers in Cesium 3D Tiles from various providers, the following approach was used for querying in version 2.0.0:

• Different identifier names are considered, such as gml:id, gml_id, gmlid, gml-id, id, etc., regardless of whether the letters are given in uppercase or lowercase.

• The same also applies to the column name of the identifiers in PostgreSQL/PostgREST and Google Spreadsheets (not embedded), as long as the column names are valid.

6.4.6. Importing visualization models with thematic data

The 3D web client supports the following type of visualization layers:

• COLLADA/KML/glTF

• GeoJSON

• Cesium 3D Tiles

• Indexed 3D Scene Layers (I3S)

For each visualization layer, the 3D web client supports the following thematic data sources:

• Google Spreadsheets (vertical or horizontal layout)

• PostgreSQL/PostgREST (vertical or horizontal layout)

• Embedded

As a result, there are 20 possible configurations to define a single visualization layer with its thematic data.

In order to add a visualization layer along with its linked thematic data to the 3D web client, the parameters must be properly specified on the corresponding input panel (cf. Fig. 6.9) which can be expanded and collapsed by clicking on the Add / Configure Layer button in the top left corner of the screen. The following image shows a COLLADA/KML/glTF layer with Google Spreadsheets as thematic data.

Fig. 6.9 The input panel for adding a new KML/glTF layer with Google Spreadsheets thematic data in the 3DCityDB Web Map Client

Note

All default parameter values used in the 3D web client were chosen accordingly to the standard settings (e.g., the standard predefined tile size is 125m x 125m) specified in the preference settings of the KML/COLLADA/glTF Exporter (cf. Section 4.6.7.1). The parameter name with the suffix (*) denotes that this parameter is mandatory; otherwise it is optional.

6.4.6.1. Layer information

Depending on the selected layer type, a list of parameters are required. The following table provides a quick overview of all these parameters while importing 3D models. Please note that while the table contains the description of all parameters, only a few of them are displayed or required for a selected layer type.

Property	Description
Type	The type of models to be imported, currently supports: COLLADA/KML/glTF, GeoJSON`, Cesium 3D Tiles and Indexed 3D Scene Layers (I3S).
URL (*)	The dataset location. For example, a KML/glTF dataset can be given as a web link to the master JSON file (cf. Section 4.6), holding the relevant meta-information of the data layer to be imported. Locally stored files can also be published using the same directory as the web client (please refer to Section 6.2).
Name (*)	A proper layer name must be specified which will be listed at the top of the input panel (in the top left corner of the screen) once the visualization layer has been successfully loaded into the 3D web client.
glTF version	(Only for glTF datasets) The version of the glTF models being imported. Currently supports: 2.0 (latest), 1.0 and 0.8.
cityobjectsJsonUrl	(Only for glTF datasets) The URL of the JSON file which can be generated automatically by using the KML/COLLADA/glTF Exporter (cf. Section 4.6.7.1). For more information please refer to explantation below this table.
minLodPixels and maxLodPixels	(Only for glTF datasets) The minimum and maximum limit of the visibility range for each data layer to control the dynamic loading and unloading of the data tiles. For more information please refer to explantation below this table.
maxCountOfVisibleTiles	(Only for glTF datasets)The maximum number of allowed visible data tiles. For more information please refer to explantation below this table.
maxSizeOfCachedTiles	(Only for glTF datasets) The maximum allowable cache size expressed as a number of data tiles. For more information please refer to explantation below this table.
MaximumScreenSpaceError	(Only for Cesium 3D Tiles und Indexed 3D Scene Layers) The maximum screen space error used to drive level of detail refinement.
Load via proxy	(Only for KML and GeoJSON datasets; only in client hosted by 3DCityDB) Specify if the KML datasets should be loaded using the built-in proxy server hosted in the 3DCityDB server. This can be used for remote KML datasets hosted on servers that do not allow Cross-Origin Resource Sharing (CORS).
Clamp to ground	(Only for KML and GeoJSON datasets) Specify if the KML models should be clamped to the ground on the globe. This is useful when the KML dataset does not have correct heights and thus may be hidden under the terrain.

• More details on cityobjectsJsonUrl: This JSON file contains a list of GMLIDs of all 3D objects which were exported and might be distributed over different tiles. For every 3D object, it is also stored in which tile it is contained together with its envelope represented using a bounding box in WGS84 lat/lon. These location information can be used to search for a certain 3D object with the help of the Geocoder widget (the lupe symbol in the top right corner of the screen), which has been extended to support a specific geocoding process performed in the following manner: In the input field, either a GMLID of a 3D object or an address can be entered. If an object with the given GMLID is found in the JSON file, the camera perspective will be adjusted to look at the center point of the 3D object with a proper oblique view. If not, the search engine Nominatim for OpenStreetMap shall be used and the map view will be adjusted to the returned location and bounding box.

• More details on minLodPixels and maxLodPixels: The maximum visibility range can start at 0 and end at an infinite value expressed as -1. Optionally, the user can directly specify the two parameter values within the 3D web client. Otherwise, the parameter values will be achieved from the master JSON file, which also contains the parameters minLodPixels and maxLodPixels and their values which have been specified using the KML/COLLADA/glTF Exporter before performing the export process.

  With these two parameters, the 3D web client implements the so-called Level of Details (LoD) concept which is a common solution being used in 3D computer graphics and GIS (e.g. KML NetworkLinks) for efficient streaming and rendering of tiled datasets. According to the LoD concept, the data tiles with higher resolution should be loaded and visualized when the observer is viewing them from a short distance. When data tiles are far away from the observer, the data tiles with higher resolution should be substituted by the data tiles with lower resolution. In order to realize this LoD concept in the 3D web client, each data tile which is being intersected with the current view frustum will be projected onto the screen while navigating the Earth map. Subsequently, the diagonal length of the projected area on the screen will be calculated by the 3D web client to determine whether the respective data tile should be loaded or unloaded. If the diagonal length is greater than minLodPixels and less than maxLodPixels, the respective data tile will be loaded and displayed; otherwise it will be hidden from display and unloaded. Of course, all data tiles lying outside of the view frustum are unloaded and invisible anyway.

  

  Fig. 6.10 Efficient determination of which data tiles should be loaded according to the user-defined visibility range in screen pixel

• More details on maxCountOfVisibleTiles: Loading massive amounts of data tiles often result in poor performance of the 3D web client or even memory overload of the web browser. This could happen when, for example, the visibility range (determined by the parameters minLodPixels and maxLodPixels) starts at a very small value and ends at an infinite size. In this case, each data tile will always be visualized even though it only takes up a very small screen space. This issue can be avoided by a proper setting of the parameter maxCountOfVisibleTiles. When this limit is reached, any additional data tiles that are farthest away from the camera will not be shown, regardless the size of screen space they occupy. Per default, this parameter receives a value of 200, which is appropriate in most use cases. However, depending on data volume of each tile and the hardware you use, this parameter value has to be adjusted by means of practical tests.

• More details on maxSizeOfCachedTiles: As mentioned before, the 3D web client implements a caching mechanism allowing for high-speed reloading of those data tiles that have been loaded before and which are stored in the memory of the web browser. In order to prevent memory overload, the parameter maxSizeOfCachedTiles can be applied. With this parameter, the 3D web client implements the so-called Least Recently Used (LRU) algorithm which is a caching strategy being widely used in many computer systems. According to this caching algorithm, newly loaded data tiles will be successively put into the cache. When the cache size limit is reached, the 3D web client will remove the least recently visualized data tiles from the cache. By default, the value of this parameter is set to 200 and can of course be increased to achieve a better viewing experience depending on the hardware you use.

6.4.6.2. Thematic data configuration

Like the visualization datasets, some parameters are required for defining a linked thematic data source, as described in the tabled below.

Type	The thematic data source type, currently supports: Google Sheets API, PostgreSQL REST API, OGC Feature API, and Embedded as data source.
URL	(Only for Google Sheets API, PostgreSQL REST API and OGC Feature API) The location of the thematic data source on the web.
Table structure	(Only for Google Sheets API and PostgreSQL REST API) The type of tables containing thematic data, currently supports: All object attributes in one row (horizontal) and One row per object attribute (vertical).

The URL of the thematic data source can be a Google Spreadsheets (e.g., with the following structure https://docs.google.com/spreadsheets/d/<spreadsheet_id>) or a table/view published by PostgREST (e.g., with the following structure https://example.com:3000/<table_name>). Please note that the shared table must be publicly available. For Google Spreadsheets, please refer to Google Support to learn how to share documents online. For the structure of these tables, please refer to Structure of the tables containing thematic data.

In horizontal tables, each row represents an object with known attributes. The number of attributes across all objects must be the same.

On the other hand, vertical tables only have three columns representing each object’s identifier, an attribute name and its value. Thus, each row represents an attribute of an object and there may exist multiple rows having the same object identifiers.

6.4.6.3. Usage example

In this example, a tiled KML/glTF dataset of buildings in the Manhattan district of New York City (NYC) will be visualized on the 3D web client. This dataset is derived from the semantic 3D city model of New York City (NYC) which has been created by the Chair of Geoinformatics at Technical University of Munich on the basis of datasets provided by the NYC Open Data Portal.

The following parameter values should be entered into the corresponding input fields:

Layer configuration:

Type	COLLADA/KML/glTF
URL	https://www.3dcitydb.net/3dcitydb/fileadmin/public/3dwebclientprojects/NYC-Model-20170501/Building_gltf/Building_gltf_collada_MasterJSON.json
Name	NYC Buildings
glTF version	1.0

Thematic data configuration:

Type	Google Sheets API
URL	https://docs.google.com/spreadsheets/d/1DbkMUSYW_YlE48MUxH5fak56uaCL8QXNrBgEr0gfuCY
Table structure	`All object attributes in one row`(horizontal)

All remaining optional parameters can be left empty and the 3D web client will fill them with default values.

After clicking on Add layer, a data layer will be loaded into the 3D web client and the corresponding layer name NYC Buldings will be listed above the input panel. The Earth map can be zoomed to the extent of the loaded data layer by double-clicking on the layer name. The parameter values of the data layer (its radio button must be activated) can be changed and applied at any time by clicking on the Save layer settings button.

Fig. 6.11 Screenshot showing how to add a new KML/glTF data layer into the 3D web client

Note

For your convenience, this example is readily available for access here.

Users are also able to control the visibility of the selected data layers by deactivating the checkbox in front of the layer’s name or clicking on the Remove selected layer button to completely remove the layer from the 3D web client.