Implementation of the Application Level

The implementation of the Application Level can be facilitated by making use of the Cloud technology which fits very well to the technical requirements of setting up an application for specific user groups. For the graphical user interface, the 3D web client introduced in the previous chapter has been extended with the full functionalities for interacting with the Cloud-based online spreadsheets which can not only serve as an online repository for storing the thematic information of 3D city models but also provide a sophisticated calculation engine

for performing many kinds of data processing and calculation tasks such as data modification, query, statistics, and ad-hoc calculations.

For instance (cf. Figure 104), when using Google Spreadsheets, the city objects displayed on the 3D web client can be queried on the basis of their attributes stored in the online spreadsheet, which supports a set of query functions allowing to retrieve the desired data records using a query expression that has a very similar syntax as the Structured Query Language (SQL). The query statements can be encoded with the spreadsheet URL and then sent to the spreadsheet server via a simple HTTP call. In addition, since only authenticated users have access to the target online spreadsheet, the request must additionally carry a so- called token code which will be interpreted by the Cloud server to determine, if the request is sent from an authorized user. Once the Cloud server has completed the query processing, a response containing the query results will be formatted in a JSON structure and can be easily parsed at the client side using JavaScript engine.

Figure 104: Sequence diagram of the process flow when performing a query on an online spreadsheet

In the online spreadsheet, the geographic coordinates of each city object’s centroid can be stored in two separate columns which represent the longitude and latitude attributes respectively (cf. Figure 105). By applying a simple query against the two columns, it is very easy to find out those city objects whose center points are contained within a 2D bounding box which can be defined by simply selecting two diagonal points on the map. The query statement may look like the followings:

SELECT * from [tableName] WHERE Longitude > minX AND Longitude < maxX AND Latitude > minY AND Latitude < maxY; Where the value pairs [minX, minY] and [maxX, maxY] denote the coordinates of the diagonal points of the given bounding box in WGS84 Geographic coordinate reference system. In case that a polygon area is given, its maximum bounding box can be first calculated to be used for performing a pre-filter and the returned city objects will be further checked at the client side using a ray-crossing algorithm to determine which city objects are located within the polygon. This function is especially useful when, for example, developing a

Technische Universität München Lehrstuhl für Geoinformatik

47

3D Web Client Google Server Online

Spreadsheet

Launch 3D web client

Login

Response containing a token code

Response containing the query results or an error message if the token is not valid

AJAX query request

Validate the token Result of the validation

Utilization of Domain Extendable 3D City Models

146

web portal which should offer users the possibility to define a polygon and download all the spatially related 3D city objects.

Figure 105: Conceptual idea of using online spreadsheet to perform a simple spatial query

In addition to the query function, the data contents in the online spreadsheet can be updated via a simple authorized HTTP request. Once the value in a column has been updated, these changes can be automatically propagated to another column which is defined as the result of a formula based on the values from other columns. A simple example is shown in Figure 106 to illustrate the basic concept.

Figure 106: Example of creating a simple calculation engine using an online spreadsheet

In this example, three columns Attr1, Attr2, Attr3 are defined in the online spreadsheet representing three different numeric attributes of a 3D city model. A simple formula has been defined on the column Attr1 whose value at each row must be automatically resulted from multiplying the values of the Attr2, Attr3 columns and can therefore not be freely changed by the users. Once a value in the column Attr2 or Attr3 has been changed, then the value of the

47 b a d c City e

GMLID Longitude Latitude …

BLDG_a X1 Y1 {…} BLDG_b X2 Y2 {…} BLDG_c X3 Y3 {…} BLDG_d X4 Y4 {…} BLDG_e X5 Y5 {…} … {…} {…} {…} 3D visulization models on the Web

Thematic Information stored in an online- spreadsheet in the Cloud

Logical link (X1, Y1) (X2, Y2) (X3, Y3) (X4, Y4) (X5, Y5)

Technische Universität München Lehrstuhl für Geoinformatik

49

GMLID Attr1 Attr2 Attr3

BLDG_a 400 4 100 BLDG_b 500 5 100 BLDG_c 600 6 100 BLDG_d 700 7 100 BLDG_e 800 8 100 … … … …

Thematic Information stored in an online-spreadsheet in the Cloud

corresponding cell in the column Attr1 will be automatically updated. Similar to most desktop spreadsheet calculation programs like Microsoft Excel, the Cloud-based online spreadsheet i.e. Google Spreadsheet provides a rich set of functions for designing a formula which can be used for developing a variety of calculation engines for specific applications.

However, using online spreadsheet as a calculation engine requires that the spreadsheet must be editable, and the users must be authorized to have write-access to it. This would be very risky regarding the data security and consistency, since this online spreadsheet may also contain other columns for storing the important attribute values which can only be edited by the application manager. To overcome this issue, a specific strategy using two interrelated spreadsheets has been developed which is illustrated using a simple example (cf. Figure 107). As shown in the figure, all attributes, which must be changeable by the end user to run the calculation, are extracted and stored in a separate spreadsheet (Spreadsheet 1). The end user shall have full access to this spreadsheet and can arbitrarily change the contained values. The second spreadsheet (Spreadsheet 2) can be seen as the main spreadsheet where the calculation formula is defined and in which the unmodifiable attribute data are stored. Thus, the end user can only have read-only access to this spreadsheet. The columns Attr2 and Attr3 are linked with their counterparts in the first spreadsheet and their values can be automatically updated once the referenced column values in the first spreadsheet have been changed. To establish such reference relationship, the owner of the second spreadsheet must be granted to have read access to the first spreadsheet for being able to pull the data from it. In this way, the calculation engine can be run by changing the input values in the first spreadsheet and the calculation results along with the other attribute information can also be well-protected as well as accessible to the public users.

Figure 107: Example of using two separate spreadsheets to perform calculations and simulations with secured data

Technische Universität München Lehrstuhl für Geoinformatik

50

GMLID Attr1 Attr2 Attr3 …

BLDG_a 400 4 100 …

BLDG_b 500 5 100 …

BLDG_c 600 6 100 …

… … … … …

GMLID Attr2 Attr3

BLDG_a 4 100 BLDG_b 5 100 BLDG_c 6 100 … … … End User Spreadsheet 2 Spreadsheet 1

6.3 Example Applications

The new multi-level system has been implemented, used, and evaluated in several commercial and scientific projects within various domains e.g. solar irradiation analysis, energy demand estimations, and identifying retrofitting and refurbishment potentials for buildings. In this section, two corresponding use cases along with the 3D web client applications are presented.