Data Table Loader (DTL) is a wizard-based tool designed and developed aimed at ingesting data provided in an Excel file by offering a flexible environment to establish a direct connection between the file data and the Snap4City platform. Using DTL, it is possible to import (static) data, by uploading an Excel file and execute a set of operations to make the uploaded data compatible with the Snap4City platform and the KM4City multi-ontology. The data is uploaded as excel file in the tool, also asking to the providers to add some descriptors for each dataset (some of them connected to the KM4City classes), then is automatically aggregated to the other processed data in the platform in a semantic way.
NOTE: Please note that the publication of data via this tool is moderated and some time can passed before to have the loaded data available.
DTL Overview
In the following sections, the general features and the process that are needed to be completed to ingest data, using DTL , is briefly described.
File Upload
The first step of data ingestion in DTM is uploading the Excel file. DTM offers suitable flexibility by supporting multi-sheet excel files. Some guidelines are provided to the user to assist him on how to upload an Excel file properly. If the user does not upload an Excel file, following the provided guidelines, s/he needs to edit the file and re-upload the file again. Also, a table which presents the information of uploaded files by the user is available and includes:
- the number of files that has been already uploaded by the user
- the maximum number of files to be uploaded by the user
- uploaded file (for each file in chronological order):
- File download (if the user role is "RootAdmin")
- Organization associated with the file (if the user role is "RootAdmin")
- filename
- upload date and time
- status: { Model: Created or Not Created, Device(s): Created or Not Created, Instance(s): Created or Not Created}
- Possible actions for each file:
- View the file data and metadata, by clicking on “VIEW DETAILS” button, including:
- device names
- instances
- values
- value names
- value types
- dateObserved
- coordinates
- nature and sub-nature (semantic descriptors)
- context broker
- Delete the file and the data created on Snap4City: (by clicking on “DELETE” button). This functionality is available only if the associated instances have not yet been created.
- View the file data and metadata, by clicking on “VIEW DETAILS” button, including:
Inserting General Information
In this step, the user is asked to insert:
- dateObserved: there are two cases:
- Row case: there is a dateObserved for each row, as in the following file: Example_dateObservedRow_CoordFile.zip. The user must select a column that contains dateObserved values
- Download the example file: Example_dateObservedRow_CoordFile.zip
- File case: there is a dateObserved for the whole file, this means that each row of the file is not associated to a specific date, as happens in the following file: Example_dateObservedFile_NoCoord_Address.zip. In this case, the user must select a dateObserved from a date-time picker.
- Download the example file: Example_dateObservedFile_NoCoord_Address.zip
A set of guidelines is provided to the user directly on the tool for setting dateObserved in both File and Row cases.
- Defining the ‘General Information’ for each file, includes:
- Sheet name: A general name for the sheets of the file (e.g., year, age, city)
- Context broker: which refers to the context broker of IoT devices to be created (selecting one from the predefined available list)
- Nature and Sub-Nature: which refers to the nature and sub-nature of the IoT devices to be created. The IoT Devices are the type of data created on the Snap4City platform form the excel file uploaded. Nature and Sub-nature can be selected by the user from the available list (that is related with the KM4City multi-ontology, this choice will allow the semantic aggregation in the Snap4City Knowledge Base).
Inserting Coordinates
In this step, the user is asked to insert coordinates regarding the file. There are two cases:
- Row case: there is a GPS location for each row, as in the following file: Example_dateObservedRow_CoordRow.zip. The user then must select columns that include latitudes and longitudes.
- Download the example file: Example_dateObservedRow_CoordRow.zip
- File case: there is a GPS location for the whole file, this means that there is not present a GPS location at row level: Example_dateObservedRow_CoordFile.zip. The user then must insert a latitude and longitude for the file (the same date will be associated to each row).
- Download the example file: Example_dateObservedRow_CoordFile.zip
- Address case: There is an address for each row in this case: Example_dateObservedFile_NoCoord_Address.zip. Therefore, the user must select a column that contains the addresses for each row. Also, to obtain the coordination of each device/instance, the user must specify a search circle by:
- the latitude and longitude of center
the radius of the search circle A possible coordination is then returned by the tool which can be viewed in the preview table in the next step. It is noted that in the Address case, if there is no address provided for a row, the associated device/instance will not be produced (later by an IoT App). A set of guidelines is provided, to the user for setting coordination in both File, Address, and Row cases.
- Download the example file: Example_dateObservedFile_NoCoord_Address.zip
Selecting Value Types and Value Units
In this step, for each column header, the user is asked to insert a value type and an associated value name. If there is no proper value type or value name for a column, the user needs to contact snap4city platform (mail to: snap4city@disit.org).
Composing Value Name
In this step, the user is asked to compose a value name for the uploaded file. A value name will be used to identify an IoT device. Therefore, as suggested in the provided guidelines, a proper value name is composed of a set of column headers that could be used to identify the IoT device. By default, the file name and the sheet name, chosen in the previous step, is included in the composed value name. It is noted that, if there is a dateObserved for each row, this step is skipped. A set of guidelines is provided to the user for composing value name directly on the tool.
Preview
In this step, an overview of inserted data and other configured settings (e.g., device name(s), sheet name(s), value types, value names, data types, nature and sub-nature, context broker) is presented. The user can observe them and change the settings by coming back to the previous step(s). Otherwise, s/he can save the data of the uploaded file by clicking on Save button.
It is noted that, in the preview table, if an address is provided for each row, the calculated coordinate for each row can be edited manually by the user.
Result
When the data of the uploaded file is saved in our database, the result is presented to the user, together with a table including the ID of inserted data, the file name, and the username which uploaded the file.
Data Table Manager: updated dataset
Here after a table reporting the excel files uploaded on Snap4City. For each dataset are shown the following details:
- Dataset (file) status: All = Model: Created + Device(s): Created + Instance(s): Created
- #total Instances = Total number of Instances present on Snap4City = #IoTDevices*#Instances for device
- #fields for each device = Number of fields for each device (that corresponds to the columns numbers of the excel file excuding latitude and longitude) = #total fields (not considering lat, long and measured time)
- #total fields = #total Instances* (#fields for each device)
Organization |
User |
File name |
Status |
#IoTModels |
#Instances for device |
#total Instances |
#fields for each device |
#total fields |
|
WestGreece |
****** |
Rooms for rent 2017-2019.xlsx |
All |
1 |
20 |
3 |
60 |
6 |
360 |
WestGreece |
****** |
Arrivals_Departures of Air Transport_Montly_2010-2019.xlsx |
All |
1 |
1 |
120 |
120 |
4 |
480 |
WestGreece |
****** |
Arrivals_Departures of Air Transport_Annual_2010-2019.xlsx |
All |
1 |
1 |
10 |
10 |
4 |
40 |
WestGreece |
****** |
Arrivals_nights_and_occupancy_in_hotel_2010-2018.xlsx |
All |
1 |
4 |
9 |
36 |
4 |
144 |
WestGreece |
****** |
DOMESTIC MOVEMENTS 2013-2018.xlsx |
All |
1 |
6 |
6 |
36 |
6 |
216 |
WestGreece |
****** |
WESTERN GREECE Hotel potential 2010-2019.xlsx |
All |
1 |
24 |
10 |
240 |
6 |
1440 |
WestGreece |
****** |
REGION OF WESTERN GREECE Visitors to Museums Archaeological sites 2010-2018.xlsx |
All |
1 |
4 |
9 |
36 |
4 |
144 |
WestGreece |
****** |
TRAFFICKERS ABROAD 2013-2018.xlsx |
All |
1 |
1 |
6 |
6 |
6 |
36 |
WestGreece |
****** |
Cruise ship movement in the port of Patras.xlsx |
All |
1 |
1 |
7 |
7 |
5 |
35 |
WestGreece |
****** |
Cruise ship movement in the port of Katakolo.xlsx |
All |
1 |
1 |
7 |
7 |
5 |
35 |
WestGreece |
****** |
Basic Sizes of Incoming Tourism of the Region of Western Greece.xlsx |
All |
1 |
6 |
4 |
20 |
7 |
140 |
|
|
|
|
|
|
|
|
|
|
Mostar-BosniaHerzegovina |
****** |
Annex II_Indicators_Mostar_HERITdata_quarterly.xlsx |
All |
1 |
1 |
9 |
9 |
8 |
72 |
Mostar-BosniaHerzegovina |
****** |
Annex II_Indicators_Mostar_HERITdata_annual.xlsx |
All |
1 |
1 |
3 |
3 |
26 |
78 |
Mostar-BosniaHerzegovina |
****** |
Annex I_Attractions.xlsx |
All |
1 |
30 |
1 |
30 |
7 |
210 |
Mostar-BosniaHerzegovina |
****** |
Annex III_Tourist number in 2019.xlsx |
All |
1 |
65 |
1 |
65 |
5 |
325 |
Mostar-BosniaHerzegovina |
****** |
Annex III_Number of tourists_monthly_2018_2019.xlsx |
All |
1 |
65 |
24 |
1560 |
5 |
7800 |
Mostar-BosniaHerzegovina |
****** |
Annex IV_Monthly statistics.xlsx |
All |
1 |
1 |
24 |
24 |
5 |
120 |
Sintesi:
TOT westgreek |
11 |
69 |
191 |
578 |
57 |
3070 |
TOT Mostar |
6 |
163 |
62 |
1691 |
56 |
8605 |
TOTALE |
17 |
232 |
253 |
2269 |
113 |
11675 |
Data Table Manager: not managed datasets
Organization |
File name |
Notes |
WestGreece |
GreeceHotels.xlsx |
The dataset seems containing Point of interest (Hotels), but the location is missing (latitude and longitude) |
WestGreece |
Peripheral Unit of Ilia.xlsx |
The dataset seems containing Point of interest (Hotels), but the location is missing (latitude and longitude) |
Data Table Loader IOT App configuration
To setup a Data Table Loader (or POI) IoT application for a user, associated with an organization (assuming that the IoT application code is available (for example, in the clipboard or in JSON format), the following steps are needed to be followed:
- Login: into the user’s account on the Snap4City platform. In fact, only authorized users, associated with an organization, are capable of running an IoT application on Snap4City the platform;
- Create: a new application, by inserting a name, when navigating to the IoT applications;
- Import: the last version of IoT application:
- in JSON format or by directly inserting/pasting the IoT application code
- Configure Fiware: for Data Table Loader IoT Application by:
- selecting the associated context broker by updating the service property of ‘fiware orion out api v2’
- updating the context broker