By using Sophora’s geographic data table you can collect and manage extensive geographic information easily.
Table of Contents
The geographic data table gives editors the opportunity to collect and manage large data amounts with geographic information in tabular form. The table can be customized individually and can be used for varied geographic information.
Based upon points:
- Traffic accidents and its causes on certain traffic junctions
- Average income per town
Based upon areas:
- Amount of traffic accidents per federal state
- Average rental prices per federal state
This gives a brief overview on the terminology of this document.
|Map||A tile-based web-view on the earth. The imagery is not necessarily the one of a classical map. Sattelite-based imagery is possble as well.|
Tiles are provided by Bing Maps.
|Point||A specific geographical point on earth's surface specified by latitude and longitude.|
|Pin||An icon indicating the location of a point on a map.|
|Cluster||An icon to identify the center of a group of points on a map. Clusters are shown when points are too close to each other for being all represented by single pins.|
A number written on the cluster indicates how many poins are contained.
|Line (or sometimes line string)||A line of multiple geographical points on earth's surface specified by multiple latitude and longitude values.|
|Shape||An abstraction of objects, that can be visualized on a map. Throughout the scope of this context this typically refers to areas or groups of areas.|
|Zoom||A numerical value indicating from how close or far away the map's imagery is viewed.|
A zoom of 0 indicates, that the whole world can be seen, while the maximum value of 20 indicates that just a small region but with all possible details is shown.
|Popup||An info box shown to the user when clicking on either a pin, a line or a shape.|
|Mouseover||A small info box shown to the user when hovering a pin, a line or a shape with the mouse cursor.|
You can change settings in the map document for the entire map regardless of its geographic information.
|Name||A human readable name describing what this map shows.|
|About this map||Provide more information about the map and assistance in this field.|
|Minimum zoom||The user can not zoom further out of this map than this value.|
|Maximum zoom||The user can not zoom deeper into this map than this value.|
|Show Search Slot||Controls whether a search field will be available to the user. This search slot will search for geographical elements using bing. It won't correlate with actual points or shapes marked on the map.|
If the search slot is shown it will contain a placeholder that can be adjusted through the addons own configuration document. See the administration article's section "Further Configuration" for details.
Important: This property is new with DeskClient versions 2.3.43, 2.4.23, 2.5.15 and newer and has to be configured by an admin to be available.
|Clustering zoom limit||Controls unto which zoom level clustering is performed. This value should be between the minimum and the maximum zoom level. (The value "0" means that no clustering is performed.)|
Given a minimum zoom of 5, a maximum zoom of 15 and a clustering zoom limit of 10 no zoom level between 10 to 15 will contain any cluster points. Everything between 5 to 10 may result in the creation of cluster points, depending on the cluster factor and the proximity of the pins.
|Cluster factor||Controls how tight clustering is performed.|
|Color of cluster nodes||The color to use on cluster nodes.|
|Allow multiple pop-ups||Determines whether this map allows opening several popups.|
|Without popup||Determines whether this map supports popups when the user clicks on a geo location. If there is a column of type "Document reference" and the checkbox is selected, the referenced document will be opened directly when clicking the geo location.|
|Show filter result hits||The maps filter menu bar shows the number of hits per value.|
|Initial map view||Determines center, zoom and imagery of this map when it is loaded.|
The possible imageries to select from depend on the underlying provider. DeskClient and GeoData Delivery use the same set of providers. Up to GeoData Delivery Version 2.5.8 this has been exclusively Bing. With GeoData Delivery Versions 2.5.9 and 2.6.0 this has been extended to Bing, OpenStreetMap and MapBox. Setting the provider is part of the node types configuration, see the administration guide for details. Note: Whenever the provider is changed configuration wise you have to adjust the selected style in the maps initial view property in order to take effect in this maps delivery.
By using libraries, it is possible to assemble geographic information, which can be reused for different maps (for different use cases).
|Name of Library||Libraries always have to be named in order to use them in map documents. The name should be comprehensible and describes which geographic information is included in the library. Also, library names should be distinct.|
|Column "id"||Libraries have to contain a column with the key "id". The cells in this column contain distinct identifiers for each line which is supposed to be referenced in a map document.|
|Column "label"||Libraries have to contain a column with the key "label". Cells in this column contain human readable names for each line.|
|Geographic Data Column||A library has to contain at least one column of the type called "Area". The cells in this column include actual geographic information. It is also possible to configure several columns of this type for different zoom levels. See zoom level in the column configuration.|
Demonstration – Federal States of Germany
The following data table contains an example library with the trade fairs in Germany and is completely configured and ready to use:
The column called "ID" (key: "id") contains the municipality keys of Germany’s federal states. The column "name" (key: "label") contains the names of the federal states and will be used as the label for the geographic information of the respective row.
Furthermore, this example contains three additional "Area" columns, each containing a state's area in different resolutions. Those areas can be used for different zoom levels in the playout, in order to reduce the amount of transferred data in the currently visible part of the map. If users zoom out of the map, they get lower resolution variants of Germany’s federal states hence allowing all federal states to be displayed while avoiding to transfer large amounts of data.
The library of federal states can now be referenced in map documents by using the column type "Library reference". The respective map document can enrich the library with even more information, in order to adapt the information to a specific use case.
By using the library of Germany's federal states, editors can create a map document with all registered domains per state and another map document with the average rents per state. The same library is used as the basis for both map documents and can be extended individually with additional information.
The menu for column actions can be opened via right click on the column header of the desired column.
|Insert column before||When clicking on this action the column configuration for a new column will be opened. If the configuration is confirmed with "OK", a new column will be inserted before the selected one.|
|Insert column behind||When clicking on this action the column configuration for a new column will be opened. If the configuration is confirmed with "OK", a new column will be inserted after the selected one.|
|Delete column||This action deletes all selected columns.|
|Configure column||Opens a dialog for the configuration of the selected column.|
|Adjust all column widths||Performs an adjustment of the column widths depending on the corresponding contents. This action ensures that columns with much content get more space.|
The column configuration can be opened via the column’s context menu and contains various settings for the chosen column, for instance name, type etc. Depending on the column’s type, the configuration options may differ.
|Name||Display name of the column. This field must be filled otherwise you will not be able to save the document.|
|Key||The distinct identifier of the column. The key is automatically generated through the name, in case it is not given yet. This field must be filled otherwise you will not be able to save the document.|
|Type||Defines which data is allowed to be inserted in the column. The following settings are dependent on the type and may be missing.|
|Text||Cells in this column contain any text.|
|Richtext||Cells in this column contain multiline formatted text that may contain bold and italic letters as well as bullet points (lists).|
|Boolean||Cells in this column contain either the value true or false.|
|Date||Cells in this column contain date values.|
|Integer||Cells in this column contain integer values.|
|Float||Cells in this column contain floating-point numbers.|
|Area||Cells in this column contain any geographic data (for instance area) represented in GeoJson format.|
|Line||Cells in this column contain lines that are defined by multiple points, i.e. multiple latitude and longitude values.|
|Geo point||Cells in this column contain geographical points with longitude and latitude.|
|Library reference||Cells in this column refer to an entry of the chosen library.|
|Document reference||Cells in this column contain references to Sophora documents.|
|Text (multi)||Cells in this column contain multiple text values.|
|Date (multi)||Cells in this column contain multiple date values.|
|Integer (multi)||Cells in this column contain multiple integers.|
|Float (multi)||Cells in this column contain multiple floating-point numbers.|
|Library||Defines to which geographic data library the column refers. This setting is only available for the column type called "library reference".|
|Playout settings||Provides different settings for the web playout.|
|Show in accessible mode||Determines whether the column will be visible in accessibility mode.|
|Sortable||Specifies whether the column is sortable if visible in accessibility mode.|
|Show in mouseover||Defines whether a mouseover appears when moving the mouse cursor over the corresponding geo location, in which the column’s content is displayed. This setting is only valid for one column of the table.|
|Popup configuration||Specifies whether and how the values of this column should appear in the popup.|
|Do not show in popup||The content inside the cells of this column will not be shown in the popup.|
|Show as headline (without label)||The content inside the cells of this column is displayed as headline in the popup.|
This setting is only valid for one column of the table and is not available for the column type "Document reference".
|Show column name and value||The content inside the cells of this column is displayed as a label value pair in the popup. This setting is not available for the column type "Document reference".|
|Show as teaser||The referenced document will be displayed as teaser in the popup.|
This setting is only valid for one column of the table and is only available for the column type "Document reference".
|Show as additional teaser link||The referenced document is displayed as an additional teaser link in the popup.|
This setting is only available for the column type "Document reference".
|Show teaser image of the document||The teaser image of the referenced document will be displayed in the popup. |
This setting is only valid for one column of the table and is only available for the column type "Document reference".
|Layout group||Defines the layout group for the current geo data column. |
This setting is only valid for the column types "Library reference", "Area", "Line" and "Geo point".
|Filter settings||Determines whether it is a filter column and also according to which values filtering is possible. |
Besides you can specifiy which filter values are preselected in the playout. The order of the filter values here has an effect on the order in the playout.
This setting is not available for all column types.
|Zoom level||Defines at which zoom level this geographic data column shall be used. This setting is important for adjusting geographic columns with different resolutions of geographic data for different zoom levels. |
This setting is only available within libraries and only for columns of the type "Area".
The menu for row actions can be opened by right-clicking on the row header of the desired row.
|Insert row before||Inserts a new line above the selected one.|
|Insert row behind||Inserts a new line below the selected one.|
|Delete row||Deletes the selected lines.|
The editing area called "Edit row" for a table row is located above the geographic table.
In the editing area an input field is located for each column, in order to edit the respective cell of the row. An additional input field for the layout of the cell exists for the following types: "Library reference", "Area", "Line" and "Geo point". Here, the layout which is used in the playout is determined. The selectable values come from the chosen layout group in the column configuration.
The map legend can be opened via the corresponding button below the geographic table.
In the opening dialog you can find all layout elements which are currently in use (see editing area). These can be sorted and provided with a description. Layout elements which are not in use can be added manually as well.
You may also provide an info text which gives more explanation about the legend.
Additional Possibilities To Manipulate The Table
Drag & Drop
Columns and rows can be resorted via drag & drop, in order to change the table’s structure.
Content of a table can be sorted (virtually) according to columns. Double click on the column header to sort the appropriate column.
- Double click: Column is sorted in ascending order.
- Double click: Column is sorted in descending order.
- Double click: Column is no longer sorted.
Filtering is possible for an easier editing of data. This is done by a corresponding text field above the geographic data table. When using the filter, only rows are displayed that contain the respective filter text.
Export as XLSX/CSV file
To export geo data, open the geo data table and click on "Export data" at the bottom of the table.
Click browse to choose the file location and file type. You may export the table as XLSX or CSV.
When exporting as XLSX you can also select the checkbox "Export column configurations" to export the column configurations as well. As a result, a special notation will be written into the topline which contains the column configurations and can be reimported.
You may also choose to export missing library reference entries of a referenced library. To do so, select the checkbox "Export missing library reference entries". All missing entries will be appended to the end of the Excel table.
Exporting as XLSX will export a binary file which can be opened with Microsoft Excel or the Open Office alternatives. Exporting as CSV will create a text file which can also be opened in any text editor and then be processed the way the user sees fit.
Import of Excel files
The Sophora DeskClient offers the possiblity to import geo data from Excel files.
The import process can be initiated via a button at the bottom of the table.
Once the button has been clicked, the import dialog will open, where you will be able to select the Excel file to import. The dialog supports the following file extensions: XLS, XLSX and XLSM. You also have to choose whether the current geo data table should be overwritten completely by the imported table or whether the new data should be appended to the existing table. When appending an existing table, new columns and rows will be appended to the respective end. The column configuration of an existing column that matches the key of an imported column configuration will be overwritten.
It is also possible to have the first few lines in the Excel table ignored. This is useful if the table contains additional information or documentation above the actual table content.
In order to successfully import an Excel table, a particular format must be matched.
- Prepare the geo data table with the column configurations in the DeskClient
- Export the table (include column configurations) and open it in Excel
- Add the content to the Excel table
- Import the Excel table via the DeskClient and overwrite the existing table
The first line of the Excel table (after the lines that should be ignored) is designated for the import of the column configuration. In this line you have to specify which key, name and type the column has. Furthermore you can specify all settings that can also be configured comfortably via the DeskClient. The column configuration must match a specific notation in order to be recognized correctly during the import process. This notation is described below.
The following attributes can be used to configure a column. The assignment of attributes to its values is made with the equality sign "=". Multiple attributes (including their values) must be separated by the pipe "|".
The type attribute specifies which kind of data can be within the column. This attribute is mandatory. The specified type is case insensitive.
|STRING||The content of this column can be any text.||type="STRING"|
|RICHTEXT||The content of this column consists of multiline formatted text that may contain bold and italic letters as well as bullet points (lists). Alternatively, cells may contain Sophora rich text (HTML like).||type="RICHTEXT"|
|BOOLEAN||The content of this column consists of truth values. Valid values for true are "yes"/"ja"/"true"/"wahr"/"1", other values are treated as false.||type="BOOLEAN"|
|DATE||The content of this column consists of dates.||type="DATE"|
|INTEGER||The content of this column consists of integers.||type="INTEGER"|
|DOUBLE||The content of this column consists of floats.||type="DOUBLE"|
|GEODATA_SHAPE||The content of this column consists of any geo data (e.g. areas).||type="GEODATA_SHAPE"|
|GEODATA_LINESTRING||The content of this column consists of lines that are defined by multiple points, i.e. multiple latitude and longitude values.||type="GEODATA_LINESTRING"|
|GEODATA_POINT||The content of this column consists of geo points with latitude and longitude. Depending on the map configuration the pins may appear as cluster points (e.g three pins may be combinied to one cluster point displaying the number "3").||type="GEODATA_POINT"|
|GEODATA_LIBRARY_REFERENCE||The content of this column consists of references to entries of the selected library. The library can be specified via one of the attributes geoDataLibraryExternalId or geoDataLibraryName.||type="GEODATA_LIBRARY_REFERENCE"|
|DOCUMENT_REFERENCE||The content of this column consists of references to Sophora documents (external ID or Sophora ID of the document).||type="DOCUMENT_REFERENCE"|
|MULTI_STRING||The content of this column consists of multiple comma separated texts.||type="MULTI_STRING"|
|MULTI_DATE||The content of this column consists of multiple comma separated dates.||type="MULTI_DATE"|
|MULTI_INTEGER||The content of this column consists of multiple comma separated integers.||type="MULTI_INTEGER"|
|MULTI_DOUBLE||The content of this column consists of multiple comma separated decimals.||type="MULTI_DOUBLE"|
Some complex values consist of fraction values. In order to be able to import such values, the fraction values will be split into multiple columns. If multiple columns refer to the same value they must have the same key. Columns with the same key will be merged to one column during the import process.
|Area||GEODATA_SHAPE_JSON||The content of this column consists of the GeoJSON of the areas.||key="area"|type="GEODATA_SHAPE_JSON"|
|GEODATA_SHAPE_NAME||The content of this column consists of the names of the areas.||key="area"|type="GEODATA_SHAPE_NAME"|
|GEODATA_SHAPE_LAYOUT||The content of this column consists of the layout entries of the areas.||key="area"|type="GEODATA_SHAPE_LAYOUT"|
|Line||GEODATA_LINESTRING_COORDS||The content of this column consists of the coordinates of the lines. One cell contains latitudes and longitudes as two separate lists.||key="line" | type="GEODATA_LINESTRING_COORDS"|
|GEODATA_LINESTRING_NAME||The content of this column consists of the names of the lines.||key="line" | type="GEODATA_LINESTRING_NAME"|
|GEODATA_LINESTRING_LAYOUT||The content of this column consists of the layout entries of the lines.||key="line" | type="GEODATA_LINESTRING_LAYOUT"|
|Geo point||GEODATA_POINT_LAT||The content of this column consists of the latitudes of the geo points. Will be determined automatically if the column GEODATA_POINT_NAME is given and contains valid addresses or geographic identifiers.||key="point" | type="GEODATA_POINT_LAT"|
|GEODATA_POINT_LON||The content of this column consists of the longitudes of the geo points. Will be determined automatically if the column GEODATA_POINT_NAME is given and contains valid addresses or geographic identifiers.||key="point" | type="GEODATA_POINT_LON"|
|GEODATA_POINT_NAME||The content of this column consists of the names of the geo points. Will be determined automatically if the columns GEODATA_POINT_LAT and GEODATA_POINT_LON are given and contain valid coordinates.||key="point" | type="GEODATA_POINT_NAME"|
|GEODATA_POINT_LAYOUT||The content of this column consists of the layout entries of the geo points.||key="point" | type="GEODATA_POINT_LAYOUT"|
|Date - Date/Time||DATE_DATE||The content of this column consists of the day specifications of the dates to be created, e.g. 11.11.2011||key="date" | type="DATE_DATE"|
|DATE_TIME||The content of this column consists of the time specifications of the dates to be created, e.g. 11:11||key="date" | type="DATE_TIME"|
|Date - Single fields||DATE_MINUTE||The content of this column consists of the minute specifications of the dates to be created. Valid values: 0-59||key="date" | type="DATE_MINUTE"|
|DATE_HOUR||The content of this column consists of the hour specifications of the dates to be created. Valid values: 0-23||key="date" | type="DATE_HOUR"|
|DATE_DAY||The content of this column consists of the day specifications of the dates to be created. Valid values: 1-31||key="date" | type="DATE_DAY"|
|DATE_MONTH||The content of this column consists of the month specifications of the dates to be created. Valid values: 1-12||key="date" | type="DATE_MONTH"|
|DATE_YEAR||The content of this column consists of the year specifications of the dates to be created.||key="date" | type="DATE_YEAR"|
|Library reference||GEODATA_LIBRARY_ID||The content of this column consists of the entries of the selected library.||key="library" | type="GEODATA_LIBRARY_ID"|
|GEODATA_LIBRARY_LAYOUT||The content of this column consists of the layout entries of the selected layout group.||key="library" | type="GEODATA_LIBRARY_LAYOUT"|
Additional column settings can be declared via more attributes.
|Attribute||Range of values||Description||Example|
|key||any text||The unique identifier of the column.|
This attribute is mandatory.
|name||any text||The display name of the column.||name="Federal State"|
|isPlayoutShowInAccessibleMode||"true" for true; other values are treated as false||Determines whether the column shall be displayed in the accessible mode.||isPlayoutShowInAccessibleMode="true"|
|isPlayoutSortableInAccessibleMode||"true" for true; other values are treated as false||Specifies whether the column is sortable if visible in accessibility mode.||isPlayoutSortableInAccessibleMode="true"|
|isPlayoutMouseover||"true" for true; other values are treated as false||Determines whether a mouseover shall appear at the mouse position when a user holds the mouse cursor over a geo location which contains the respective column content.||isPlayoutMouseover="true"|
|popupConfiguration||NOT_IN_POPUP||see column configuration||popupConfiguration="NOT_IN_POPUP"|
|AS_HEADLINE||see column configuration||popupConfiguration="AS_HEADLINE"|
|AS_LABEL_VALUE_PAIR||see column configuration||popupConfiguration="AS_LABEL_VALUE_PAIR"|
|AS_TEASER||see column configuration||popupConfiguration="AS_TEASER"|
|AS_TEASER_LINK||see column configuration||popupConfiguration="AS_TEASER_LINK"|
|AS_TEASER_IMAGE||see column configuration||popupConfiguration="AS_TEASER_IMAGE"|
|geoDataLibraryExternalId||external ID of a valid library||Defines which geo data library the column is referencing. Alternatively, you can also specify the name of the library, see geoDataLibraryName.||geoDataLibraryExternalId="federal-states-official"|
|geoDataLibraryName||name of a valid library||Defines which geo data library the column is referencing. Alternatively, you can also specify the external ID of the library, see geoDataLibraryExternalId.||geoDataLibraryName="Federal States (official)"|
|filter||list of filter values including whether they are initial filters||Defines whether the column is filterable and if yes, by which values it can be filtered. You can also specify which values are preselected in the playout.|
Multiple filter values must be separated by a semicolon ";". If the initial filter value setting is omitted, it is false by default.
|filter=["Hamburg"; "Brandenburg" -> "true"; "Lower Saxony" -> "false"]|
|zoomlevel||0 - 20||Defines from which zoom level onwards the geo data column shall be used.||zoomlevel="5"|
|layoutSetKey||any values of the select value document for area, line string or pin layout groups||Defines the layout group for the geo data column.||layoutSetKey="5-classes-red"|
|ignore||"true" for true; other values are treated as false||Defines whether the column shall be imported. If this attribute is omitted, the column will be imported.||ignore="true"|
A fully prepared Excel table might look like this: