Geographic Data Table

By using Sophora’s geographic data table you can collect and manage extensive geographic information easily.

Table of Contents

The following documentation refers to the add-on DDJM: Data Driven Journalism with Maps.
If you are an administrator, please have a look at the documentation for administration.
If you are a web developer, please have a look at the delivery documentation.

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.

Use cases

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

Wording

This gives a brief overview on the terminology of this document.

MapA 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.
PointA specific geographical point on earth's surface specified by latitude and longitude.
PinAn icon indicating the location of a point on a map.
ClusterAn 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.
ShapeAn 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.
ZoomA 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.
PopupAn info box shown to the user when clicking on either a pin, a line or a shape.
MouseoverA small info box shown to the user when hovering a pin, a line or a shape with the mouse cursor.

Map Configuration

You can change settings in the map document for the entire map regardless of its geographic information.

FieldDescription
NameA human readable name describing what this map shows.
About this mapProvide more information about the map and assistance in this field.
Minimum zoomThe user can not zoom further out of this map than this value.
Minimum: 0
Maximum: 20
Default: 2
Maximum zoomThe user can not zoom deeper into this map than this value.
Minimum: 0
Maximum: 20
Default: 20
Show Search SlotControls 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.
Default: false
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 limitControls 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.)
Example:
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 factorControls how tight clustering is performed.
Color of cluster nodesThe color to use on cluster nodes.
Allow multiple pop-upsDetermines whether this map allows opening several popups.
Without popupDetermines 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 hitsThe maps filter menu bar shows the number of hits per value.
Initial map viewDetermines center, zoom and imagery of this map when it is loaded.

Libraries

By using libraries, it is possible to assemble geographic information, which can be reused for different maps (for different use cases).

ConfigurationDescription
Name of LibraryLibraries 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 ColumnA 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.

Column Actions

The menu for column actions can be opened via right click on the column header of the desired column.   

data driven journalism insert row
ActionDescription
Insert column beforeWhen 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 behindWhen 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 columnThis action deletes all selected columns.
Configure columnOpens a dialog for the configuration of the selected column.
Adjust all column widthsPerforms an adjustment of the column widths depending on the corresponding contents. This action ensures that columns with much content get more space.

Column Configuration

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.

FieldDescription
NameDisplay name of the column. This field must be filled otherwise you will not be able to save the document.
KeyThe 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.
TypeDefines which data is allowed to be inserted in the column. The following settings are dependent on the type and may be missing.
TextCells in this column contain any text.
Richtext
(since 2.5.23)
Cells in this column contain multiline formatted text that may contain bold and italic letters as well as bullet points (lists).
BooleanCells in this column contain either the value true or false.
DateCells in this column contain date values.
IntegerCells in this column contain integer values.
FloatCells in this column contain floating-point numbers.
AreaCells in this column contain any geographic data (for instance area) represented in GeoJson format.
LineCells in this column contain lines that are defined by multiple points, i.e. multiple latitude and longitude values.
Geo pointCells in this column contain geographical points with longitude and latitude.
Library referenceCells in this column refer to an entry of the chosen library.
Document referenceCells 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.
LibraryDefines to which geographic data library the column refers. This setting is only available for the column type called "library reference".
Playout settingsProvides different settings for the web playout.
Show in accessible modeDetermines whether the column will be visible in accessibility mode.
SortableSpecifies whether the column is sortable if visible in accessibility mode. (Since 2.5.8)
Show in mouseoverDefines 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 configurationSpecifies whether and how the values of this column should appear in the popup.
Do not show in popupThe 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 valueThe 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 teaserThe 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 linkThe 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 documentThe 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 groupDefines 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 settingsDetermines 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 levelDefines 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".

Row Actions

The menu for row actions can be opened by right-clicking on the row header of the desired row.

ActionDescription
Insert row beforeInserts a new line above the selected one.
Insert row behindInserts a new line below the selected one.
Delete rowDeletes the selected lines.

Editing Rows

The editing area called "Edit row" for a table row is located above the geographic table.

Please note, that the editing area needs to be expanded, because it is initially collapsed.

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.

Changes within the editing area are immediately adopted to the table.

Map Legend

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.

Layout elements that are removed from the map legend, but are still in use in the table, are re-added automatically when saving the document or reopening the dialog of the map 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.

Sorting Columns

Content of a table can be sorted (virtually) according to columns. Double click on the column header to sort the appropriate column.

  1. Double click: Column is sorted in ascending order.
  2. Double click: Column is sorted in descending order.
  3. Double click: Column is no longer sorted.

The order of columns only affects the view, particular table data is not involved. The order will not be saved.
By using the ALT key and double click simultaneously on a further column, a secondary order can be applied. You can repeat this action as often as you want.
Filtering

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.

Column configuration
To avoid having to configure the columns in Excel manually, the following procedure is recommended:
  1. Prepare the geo data table with the column configurations in the DeskClient
  2. Export the table (include column configurations) and open it in Excel
  3. Add the content to the Excel table
  4. 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.

Column typeDescriptionExample
STRINGThe content of this column can be any text.type="STRING"
RICHTEXT
(since 2.5.23)
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"
BOOLEANThe 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"
DATEThe content of this column consists of dates.type="DATE"
INTEGERThe content of this column consists of integers.type="INTEGER"
DOUBLEThe content of this column consists of floats.type="DOUBLE"
GEODATA_SHAPEThe content of this column consists of any geo data (e.g. areas).type="GEODATA_SHAPE"
GEODATA_LINESTRINGThe content of this column consists of lines that are defined by multiple points, i.e. multiple latitude and longitude values.type="GEODATA_LINESTRING"
GEODATA_POINTThe 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_REFERENCEThe 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_REFERENCEThe content of this column consists of references to Sophora documents (external ID or Sophora ID of the document).type="DOCUMENT_REFERENCE"
MULTI_STRINGThe content of this column consists of multiple comma separated texts.type="MULTI_STRING"
MULTI_DATEThe content of this column consists of multiple comma separated dates.type="MULTI_DATE"
MULTI_INTEGERThe content of this column consists of multiple comma separated integers.type="MULTI_INTEGER"
MULTI_DOUBLEThe 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.

Column typeDescriptionExample
AreaGEODATA_SHAPE_JSONThe content of this column consists of the GeoJSON of the areas.key="area"|type="GEODATA_SHAPE_JSON"
GEODATA_SHAPE_NAMEThe content of this column consists of the names of the areas.key="area"|type="GEODATA_SHAPE_NAME"
GEODATA_SHAPE_LAYOUTThe content of this column consists of the layout entries of the areas.key="area"|type="GEODATA_SHAPE_LAYOUT"
LineGEODATA_LINESTRING_COORDSThe 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_NAMEThe content of this column consists of the names of the lines.key="line" | type="GEODATA_LINESTRING_NAME"
GEODATA_LINESTRING_LAYOUTThe content of this column consists of the layout entries of the lines.key="line" | type="GEODATA_LINESTRING_LAYOUT"
Geo pointGEODATA_POINT_LATThe 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_LONThe 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_NAMEThe 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_LAYOUTThe content of this column consists of the layout entries of the geo points.key="point" | type="GEODATA_POINT_LAYOUT"
Date - Date/TimeDATE_DATEThe content of this column consists of the day specifications of the dates to be created, e.g. 11.11.2011key="date" | type="DATE_DATE"
DATE_TIMEThe content of this column consists of the time specifications of the dates to be created, e.g. 11:11key="date" | type="DATE_TIME"
Date - Single fieldsDATE_MINUTEThe content of this column consists of the minute specifications of the dates to be created. Valid values: 0-59key="date" | type="DATE_MINUTE"
DATE_HOURThe content of this column consists of the hour specifications of the dates to be created. Valid values: 0-23key="date" | type="DATE_HOUR"
DATE_DAYThe content of this column consists of the day specifications of the dates to be created. Valid values: 1-31key="date" | type="DATE_DAY"
DATE_MONTHThe content of this column consists of the month specifications of the dates to be created. Valid values: 1-12key="date" | type="DATE_MONTH"
DATE_YEARThe content of this column consists of the year specifications of the dates to be created.key="date" | type="DATE_YEAR"
Library referenceGEODATA_LIBRARY_IDThe content of this column consists of the entries of the selected library.key="library" | type="GEODATA_LIBRARY_ID"
GEODATA_LIBRARY_LAYOUTThe 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.

AttributeRange of valuesDescriptionExample
keyany textThe unique identifier of the column.
This attribute is mandatory.
key="federal_state"
nameany textThe display name of the column.name="Federal State"
isPlayoutShowInAccessibleMode"true" for true; other values are treated as falseDetermines whether the column shall be displayed in the accessible mode.isPlayoutShowInAccessibleMode="true"
isPlayoutSortableInAccessibleMode"true" for true; other values are treated as falseSpecifies whether the column is sortable if visible in accessibility mode. (Since 2.5.8)isPlayoutSortableInAccessibleMode="true"
isPlayoutMouseover"true" for true; other values are treated as falseDetermines 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"
popupConfigurationNOT_IN_POPUPsee column configurationpopupConfiguration="NOT_IN_POPUP"
AS_HEADLINEsee column configurationpopupConfiguration="AS_HEADLINE"
AS_LABEL_VALUE_PAIRsee column configurationpopupConfiguration="AS_LABEL_VALUE_PAIR"
AS_TEASERsee column configurationpopupConfiguration="AS_TEASER"
AS_TEASER_LINKsee column configurationpopupConfiguration="AS_TEASER_LINK"
AS_TEASER_IMAGEsee column configurationpopupConfiguration="AS_TEASER_IMAGE"
geoDataLibraryExternalIdexternal ID of a valid libraryDefines which geo data library the column is referencing. Alternatively, you can also specify the name of the library, see geoDataLibraryName.geoDataLibraryExternalId="federal-states-official"
geoDataLibraryNamename of a valid libraryDefines 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)"
filterlist of filter values including whether they are initial filtersDefines 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"]
zoomlevel0 - 20Defines from which zoom level onwards the geo data column shall be used.zoomlevel="5"
layoutSetKeyany values of the select value document for area, line string or pin layout groupsDefines the layout group for the geo data column.layoutSetKey="5-classes-red"
ignore"true" for true; other values are treated as falseDefines whether the column shall be imported. If this attribute is omitted, the column will be imported.ignore="true"
Example

A fully prepared Excel table might look like this:

Deutsche Version dieses Dokuments (German version of this document)

Um diese Dokumentation auf Deutsch zu lesen, wechseln Sie bitte hier zur deutschen Version.

In order to read this documentation in German please refer here to the German version of it.