Support for data sources Google Spreadsheets and PostgreSQL

Release v1.8.1 demo link

In preparation of Google Fusion Tables being shut down by Google: Extend the 3DCityDB Web Client to support further data sources such as Google Spreadsheets (using Google Sheets API v4) and directly from PostgreSQL (using PostgREST API).

IMPORTANT CHANGES

• The 3DCityDB Web Map Client has been utilizing the Google Fusion Table to store and display thematic attributes of a selected city object. However, Google Fusion Tables will be unavailable after Dec 3, 2019. It is recommended to backup thematic data stored in such tables locally/offline or using alternative cloud services. Please refer to Google announcements for more information.

• In this context, besides Google Fusion Tables, the Web Client is now additionally capable of fetching data using Google Sheets API v4 and a PostgreSQL database with a RESTful API enabled (PostgREST).

  Like with Google Fusion Tables, data fetched from Google Sheets API and PostgREST can also be displayed on the infobox as thematic data when a city object is clicked. Simply enter the URL to corresponding tables in the thematicDataUrl field as well as the type of thematic data source in > Thematic Data Source field in Show / Hide Toolbox -> Add / Configure Layer. This could be:

  • The spreadsheet URL using Google Sheets API, e.g. with the following structure https://docs.google.com/spreadsheets/d/<spreadsheet_id>
  • The table URL published by the PostgreSQL REST API, e.g. https://example.com:3000/<table_name>

• In addition to the two new supported 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). The selected table type is encoded in URLs generated by Generate Scene Link as well as parsing project URLs, see 05e692d, where:

  • Horizontal: all object attributes are stored in columns of one single row, which means each ID occurs only once in the table.

    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.

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

    Example:

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

  • 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", value_name : "value_name", value : "value" },
          { gmlid : "id2", value_name : "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.

UPDATES

• Added support for thematicDataSource in URLs generated by Generate Scene Link as well as parsing project URLs, see85afb36.

FIXES

• Fixed a bug that prevented Geocoder to function properly on defined active layers, see 0e60059.