Home
Excel2map is a template spreadsheet that can be used to export data in a spreadsheet to a feature layer in the Common Sense platform. This wiki explains the process of configuring and exporting a dataset using Excel2map.
The Excel2map file has three worksheets: Layers, Helpers and Data. The first sheet, Layers, contains the parameters that define the resulting layer, such as its title, dataformat and styles. The second sheet, Helpers, is an auxiliary worksheet that aids in the conversion process, but does not need to be changed. The third sheet, Data, contains the dataset that will be converted.
Converting the Excel data to a layer in csWeb is a multistep process, in which the Excel file only performs the first step: converting the datatable into a JSON format. However, to create a geoJson layer that can be loaded into csWeb, some additional steps are required, such as adding the latitude and longitude coordinates need to each data entry. Therefore, the Excel2map sheet connects to the server hosting the CommonSense webpage. The server performs the next steps in converting the data into a feature layer on the map.
The following figure shows the data that we will convert into a featurelayer.
The data should have a header in the first row, followed by rows that contain all data entries. The complete dataset including the headers should be defined as a named range called "Properties" (in Excel 2010: click "Name Manager" under the tab "Formulas").
For the configuration of the datalayer, head to worksheet "Layer"
The general layer configuration can be changed in row 6:
- ProjectTitle: The title of the project that is loaded on the CommonSense webpage. It is important that these match, otherwise the resulting layers will not show!
- Reference: An id that can be used within csWeb-code to identify the layer.
- LayerTitle: The title of the layer that will be shown on the CommonSense webpage.
- Description: A description of the layer that will be shown on the CommonSense webpage.
- IsEnabled: Whether the layer should be activated automatically(TRUE or FALSE)
- UseClustering: Whether close features should be clustered on certain zoomlevels (TRUE or FALSE; recommended for many point features, disable for polygons)
- ClusterLevel: The zoomlevel from which the features will be clustered (Range 1-20?)
- Group: The group name to which the layer belongs. Will be shown on the CommonSense webpage.
- IconUri: The path to the icon that must be used for the data.
- IconSize: The size of the markers on the map (in pixels).
- FillColor: The fill color of the markers (in hexadecimal RGB format).
- StrokeColor: The color of the marker borders (in hexadecimal RGB format).
- StrokeWidth: The width of the border stroke
- GeometryType: Select what type of geometry must be used to pinpoint the data on the map. These can be points (e.g. adresses) or regions (e.g. provinces).
- Parameter123: The header of the column containing the location of the data. These can be for example the column containing the province. Some geometrytypes require multiple parameters, e.g., for "Postcode6_en_huisnummer" define "parameter1" as the column containing the zipcode and parameter2 as the column containing the housenumber.
- Opacity: The opacity of the layer.
- NameLabel: The label of the property that defines the title of each entry. For example, setting it to "Naam locatie" in this case will set the name of the hospital as the feature title.
- IncludeOriginalProperties: Set to FALSE when only the columns present in datasheet should be included in the resulting layer. Set to TRUE when the data should be aggregated with data that is present in the templates (for example: the provinces-template contains the number of inhabitants of each province. You can choose to include this in your resulting datalayer by setting this value to TRUE).
From row 9 on, each individual property can be defined.
- Label: Select the label of the datacolumn you want to configure (should be one of the headernames from the Data-worksheet).
- Section: The section the property belongs to. Can be used to group properties together, such as zipcode, street, housenumber into a section "Address".
- Title: Title of the property that shows in the rightpanel in csWeb.
- Description: Description of the property.
- Type: The type of the data: text, number, date or options.
- StringFormat: When the datatype is number, the formatting of the number can be chosen. For example, showing the number with two decimals, or as a currency value without decimals.
- TargetProperty: Used for time-based values (see Time-based values). Select the column containing a datavalue measured at a certain time.
- Date: Used for time-based values. Select the date of the datavalue.
- Time: Used for time-based values. Select the time of the datavalue.
- IsVisible: Whether the property should be shown in the rightpanel (TRUE or FALSE).
- CanEdit: Whether the property can be used to edit the style. Useful for comparing numbers between features, such as number of inhabitants, area, etc.
When the configuration of the data is complete, the template can be exported to the CommonSense page. First, start "csServer.exe" and open your browser. Type "http://localhost:3002" in the addressbar. Set this URL in box A1 of the Excel sheet as well and press the "Upload data to map" button to export your data to the CommonSense page.
Some more advanced features will be explained in this section.
In some cases, values of a property are available at multiple points in time. These values can be exported with the accompanying dates and times, in order to show the visualize the value change over time in csWeb. To export such a dataset, start defining the layer properties as usual. At the propertytype definitions, add an entry for the time-based range defining title, description, etc. Additional to this main propertytype, add a row for each point in time for which data is available. For these rows, only three columns are required: TargetProperty, Date and Time. The TargetProperty should be the header of the datacolumn. The Date and Time columns should contain the date and time of the values. It is important that all columns of a specific timeseries have the exact same headernames, in order for the Excel macro to know which values belong to the timeseries. When the desired configuration has been set, the data can be converted by the upload-button. Screenshots of a time-based conversion can be seen below:
The location of addresses is found using the BAG-database. However, besides the location much more information regarding buildings is present in the database. To add this data to your datalayer, choose the GeometryType "Postcode6_en_huisnummer_met_bag". This will add information like "bouwjaar" and "pandidentificatie". By default this data will be put in an additional section called "BAG". To edit this, create an additional column in the Data-worksheet of your Excel-file, for example called "Bouwjaar". Then edit the named range "Properties" such that the new column is included. Next, in the Layer-worksheet add a new propertytype called "Bouwjaar" and define the properties as usually (in this case, define type as "number"). When the upload button is pressed, the data will be exported to csWeb, including the BAG parameters.
For all templates it is important that the names specifying the region are exactly the same as the names in the template. Otherwise the specified regions cannot be found in the template. The following table lists the naming convention per geometry type:
| Geometry type | Name format | Example | Note |
|---|---|---|---|
| CBS_Provincie_op_naam | Name | Friesland | |
| CBS_Provincie_op_code | ## | 21 | |
| CBS_Gemeente_op_naam | Name | Amsterdam | |
| CBS_Gemeente_op_code | GM#### | GM0003 | |
| CBS_Wijk_op_naam | Name | Wijk 2 De Hoven | |
| CBS_Wijk_op_code | WK###### | WK000300 | |
| CBS_Buurt_op_naam | Name | Appingedam-Centrum | |
| CBS_Buurt_op_code | BU######## | BU00030000 | |
| Zorgkantoorregio | Name | Amsterdam | |
| Postcode4_regio | #### | 2641 | |
| Postcode6_en_huisnummer | ####XX or #### XX | 2555NA | First parameter |
| Postcode6_en_huisnummer | # | 24 | Second parameter |
| Latitude_and_longitude | ##.## | 52.066 |