Workshop on ingestion from an Open Data portal, visualization with dashboards and appification

1 Introduction
2 Ontology Modelling
3 Data intake
4 GIS viewer with station inventory::
5 Dashboard consuming the information
6 Query REST API

Introduction

This workshop will explain a simple example with the platform covering the following points:

Modeling of two ontologies: You will model the ontologies of this case study, one of them using as a basis a GSMA Datamodel and the other one creating the data structure from scratch.
Intake from an Open Data portal, using the Flow Engine: You will define two information flows that will retrieve information from the Open Data of the city of Barcelona and store that information in the previously defined ontologies.
Creation of a GIS viewer: One of the previously modeled ontologies stores georeferenced information. We will define a platform GIS viewer to display this information on a map.
Creation of a Dashboard: You will make a simple Dashboard, which will visually structure the information stored on the platform from the Open Data portal.
Exposure in a REST API: You will use the API Manager to expose an operation with the information from one of the ontologies.

The example will consist of a load of data from the city of Barcelona’s Open Data portal. Specifically, two resources will be chosen from the bicycle rental service dataset. These resources are: The inventory of rental stations and the status of each station that is periodically refreshed.
Within the platform components diagram, in the following image you can see the components with which we are going to work (marked as surrounded):

Control Panel: It is the web interface with which you will interact to work with the platform
Flow Engine: Engine of flows that will make the ingestion of the data from the Open Data service of Barcelona city council. In the example, two flows will be defined: one to check and create the inventory of stations on the platform, and another one to check and periodically update the status of the stations.
Semantic Broker: It provides the entry point of the information on the platform. It checks the access permissions to the information and whether the information is correct or not, according to the scheme defined in the ontology. If the information is correct, it sends it to the storage module. In our case, the Semantic Broker provides transparently to the Flow Engin the point where it must dump the information consulted in the Open Data portal, and it is responsible for validating it with the scheme defined in the ontologies.
Semantic Datahub: It provides the physical infrastructure (Databases) for information storage, query and maintenance. In the example, this module provides the Database where both inventory information and bicycle stations state are stored, as well as the query engine.
Gis Viewers: It facilitates the GIS visualization of the information stored in the platform that has a geographical component. In our example, the GIS viewer will allow to create an HTML viewer to georeference the stations of the inventory ontology.
Dashboard Engine: Allows you to create and visualize graphically, using HTML5 web components, the information stored on the platform.In the example we will create a Dashboard that shows a GIS viewer with the georeferenced bicycle inventory, the status of all stations, as well as a filter that indicates the comparison between electric and mechanical bicycles available at each rental station.
API Manager: It facilitates integration with other systems, allowing the information stored on the platform to be exploited by other systems, in a standard way and controlled by a REST API.

In this example, you will create a REST API with an operation that allows to know the list of stations whose available bicycles are below a threshold that is specified by a parameter in the API.

Ontology Modelling

The modeling of an ontology is inherent to the information that it must store. Thus, to create the ontologies, you must first consult the information returned by the Bicing Open Data service. Specifically, the Open Data portal is: https://opendata-ajuntament.barcelona.cat/data/es/dataset/informacio-estacions-bicing

And the two resources are:

Inventory:

https://api.bsmsa.eu/ext/api/bsm/gbfs/v2/en/station_information
That returns the inventory of stations in the following format:

Status:

https://api.bsmsa.eu/ext/api/bsm/gbfs/v2/en/station_status
That is periodically updated, returning the status of each station in the following format:

That way, you will moderate two ontologies:

BicingBCN_Inventory: With the inventory of stations.

To create this ontology, use a GSMA template that already defines a standard for this type of information and whichthrough prior transformation during intake, is compatible with the one returned by the Open Data service. Information about this datamodel can be found at the following address: https://fiware-datamodels.readthedocs.io/en/latest/Transportation/Bike/BikeHireDockingStation/doc/spec/index.html To create the ontology, once logged in the platform, follow the following steps:

Choose the Menu option: Tools > My Ontologies and then the Create button.

Select the option Creation Step by Step:

Create the ontology specifying: name, description, meta-information and select the GSMA-Bikestation datamodel Template:

Click Update Schema to generate the JSON Schema of the ontology:

Finally, click New:

BicingBCN_Status: Ontology with the state (available bicycles) for each of the stations. In this case, do not use an ontology template, but instead create it property by property.

The properties that the ontology will have are based on those returned by the Open data service and are:

station_id
- numBikesAvailable
- numBikesMechanical
- numBikesElectric
- status

The process for creating the BicingBCN_Status ontology is the same as for BicingBCN_Inventory, except for the template, which in this case will be empty (Template: General> Empty Base) and the properties will be added manually using the add new property button:

Add the properties specified above one by one as you can see in the image:

Finish the creation of the ontology generating the scheme by pressing Update Schema and finally the New button.

Once both ontologies have been created, you can verify that they exist, but have no data yet. To do this, select the Query Tool from Tools > Query Tool:

Find the BicingBCN_Inventory ontology:

And use a query statement to check that there is no data:

Similarly, if you query the BicingBCN_Status ontology, you will see that it does not have data either:

Data intake

Al crear las ontologías, ya hemos adelantado los servicios Open Data y el formato de la información que devuelven. En la ingesta, vamos a crear dos flujos de información, uno por cada servicio, que consultará la información, la transformará al formato semántico de la ontología definida, y la almacenará en la ontología correspondiente.
Para crear los flujos accedemos al Flow Engine de la plataforma desde el menú Tools > My IoT Flows

If you haven’t created any flow until now, an empty list will appear:

In this case, you will have to create an instance of the flow engine by pressing the Create button. From there, you can give an identification to your instance and create it by pressing New:

This will instantiate our flow engine, which you will proceed to start by pressing Start:

Once started, access the flow editor by pressing View it:

And the flow editor appears like this:

Station Inventory Intake Flow:

Next, you will program a flow that will connect to the Open Data service daily, check the inventoried stations, convert that information to the semantic model of the platform (BicingBCN_Inventory ontology) and store it in the ontology.
Steps:

Periodic launch of the flow: Program the periodic execution of the flow through an inject type node.

Find the node in the palette:

And drag it to the grid:

Then, double click on the node to access its configuration:

Configure it to run every day at 06:00 and click Done:

Invocation to the Open Data service: The next stage in the flow is to retrieve station information from the Open Data service. As we saw earlier, this is achieved by invoking the url: https://api.bsmsa.eu/ext/api/bsm/gbfs/v2/en/station_information

To do this, use a node of type Http Request.
Find the node in the palette:

And drag it to the grid:

Next, double click on it to establish its configuration, specify the url to which it has to invoke and press Done:

Lastly, establish the connection with the previous node to connect it to the flow:

Once connected, you can test the flow by checking that the output of the http request node is the response to the Open Data service. To do this, connect a debug type node. This node shows the output message of the node that connects to it in the tool's debug console:

Next, deploy the flow by pressing Deploy:

And you can test it by enabling the debug tab

and pressing the inject node button.

Conversion of the information to the semantic model of the ontology: The information retrieved from the Open Data service is a list of stations with the following format for each station:
And to store them on the platform, you have to convert it to the model defined in the BicingBCN_Inventory ontology, where the instances must have the following structure:
To do this, do the following in the flow:
Convert the response of the Open Data service, which is a String with the JSON of the response, to a pure JSON object model. To do this, connect a Json type node, which does precisely this, converts the service response into an array of JSON objects.
Next, process the array of stations with the Open Data service format, turning it into an array of stations with the BicingBCN_Inventory ontology format. To do this, use a function type node, which allows you to program logic in JavaScript:

Double-click on the newly added function type node to configure it, including the following code, which performs the transformation processing indicated above, then press Done:

Y para almacenarlas en la plataforma tenemos que convertirla al modelo definido en la ontología BicingBCN_Inventory, donde las instancias deben tener la siguiente estructura:Para ello haremos lo siguiente en el flujo:Convertiremos la respuesta del servicio Open Data, que es un String con el JSON de la respuesta, a un modelo de objetos JSON puro. Para ello conectaremos un nodo de tipo Json, que hace precisamente esto, convertir la respuesta del servicio en un array de objetos JSON. A continuación procesaremos el array de estaciones con el formato del servicio Open Data, convirtiéndolo en un array de estaciones con el formato de la ontología BicingBCN_Inventory. Para ello utilizaremos un nodo de tipo function, que nos permite programar lógica en Javascript: Hacemos doble click sobre el nodo de tipo function recién añadido para configurarlo, incluyendo el siguiente código, que realiza el procesamiento de transformación indicado anteriormente y pulsamos Done:

var stations=msg.payload.data.stations; 
var gsmaStations=[]; 
for(i=0;i<stations.length;i++){
var station ={BikeHireDockingStation : {
id: ""+stations[i].station_id,
type: "BikeHireDockingStation",
name: ""+stations[i].name,
category: ""+stations[i].physical_configuration,
status: "ON",
availableBikeNumber: stations[i].capacity,
freeSlotNumber: stations[i].capacity,
totalSlotNumber: stations[i].capacity,
location: {
coordinates: [stations[i].lon, stations[i].lat],
type: "Point"
},
address: ""+stations[i].address,
description: ""+stations[i].physical_configuration,
dateModified: "03-04-2019",
ownership:"Ayto Barcelona"
}
};
gsmaStations[i]=station;
} 
msg.payload=gsmaStations;
return msg;

Platform insertion: Once the information has been converted to the ontology format, it can now be stored on the platform.

Include a node of type onesaitplatform-insert in the flow:

Double click on the node, configure the BicingBCN_Inventory ontology as the information destination and click Done:

Add a debug type node to visualize the result of the insertion in the debug tab which, being an insert, is the number of elements inserted in the platform. After deploying and testing, you can see:

Deleting previous records: As you saw when starting the flow, this is a flow that will be executed periodically every day. Thus, in each execution, you should update the inventory. The easiest way to do this is to previously delete the stations from the last execution of the flow, then reload those of the current execution. To do this, add another node to the flow for the previous erase of stations just when the flow is launched.

To do this, add a node of type onesatiplatform-query-static and connect it to the initial node (you can also take the opportunity to add a node of type debug to visualize the result).

Double-click on the onesaitplatform-query-static node and configure the ontology we want to delete: BicingBCN_Inventory and the SQL statement: delete from BicingBCN_Inventory. Press Done.

Redeploy and check in the debug tab that first it deletes the previous information, and then it stores the new information:

Insertion lanzamos una sentencia de consultation: We verify in platform that the information has been inserted correctly in the ontology. To do this we access Tools > Query tool and launch a query statement about the BicingBCN_Inventory ontology:

Station Status Intake Flow: The station state intake flow is very similar to the previous one. It requires the same nodes, only changing the service origin of the data and the destination ontology BicingBCN_Status, so we will not go into much detail.

Steps:

Create a new flow: Press the
button to add a new tab with a new blank grid to define another flow.

Periodic launch of the flow: Program the periodic execution of the flow through an inject type node. When dealing with the status of the stations, configure the node to run every 15 minutes:

Invocation to the Open Data service: The next stage in the flow is to retrieve the status information of the stations from the Open Data service, invoking through an Http Request node to the URL: https://api.bsmsa.eu/ext/api/bsm/gbfs/v2/en/station_status

Conversion of the information to the semantic model of the ontology: The information retrieved from the Open Data service is the list of stations with their statuses, in the following format for each station:

And to store them on the platform, you have to convert it to the model defined in the BicingBCN_Status ontology, where the instances must have the following structure:

To do this, do the following actions in the flow:

Convert the response of the Open Data service, which is a String with the JSON of the response, to a pure JSON object model. To do this, connect a Json type node, which does precisely this, it converts the service response into an array of JSON objects.

Next, process the station status array with the Open Data service format, converting it into an array of stations with the BicingBCN_Status ontology format. To do this, use a function type node again, which allows you to program logic in JavaScript:

Double-click on the newly added function type node to configure it, including the following code, which performs the transformation processing indicated above, and press Done:

var stations=msg.payload.data.stations; 
var ontologyStations=[]; 
for(i=0;i<stations.length;i++){
var station= {BicingBCN_Status: { 
station_id:""+stations[i].station_id,
numBikesAvailable:stations[i].num_bikes_available,
numBikesMechanical:stations[i].num_bikes_available_types.mechanical,
numBikesElectric:stations[i].num_bikes_available_types.ebike,
status:stations[i].status
}
}; 
ontologyStations[i]=station;
} 

msg.payload=ontologyStations;
return msg;

Insertion in the Platform: Once the information has been converted to the ontology format, it can now be stored on the platform.

Include a node of type onesaitplatform-insert in the flow and configure it to store the information in the BicingBCN_Status ontology.

Add a debug type node to visualize the result of the insert in the debug tab which, being an insert, is the number of elements inserted in the platform. After deploying and testing, you can see:

Deletion of previous records: Same as in the previous flow, you have to delete the information previously inserted in each execution of the flow, so you have to configure the deletion of the BicingBCN_Status ontology with a onesaitplatform-query-static node:

Deploy and check the response in the debug tab:

Insertion verification: Check in platform that the information has been inserted correctly in the ontology. To do this, access Tools > Query tool and launch a query statement on the BicingBCN_Status ontology:

GIS viewer with station inventory::

The next thing you will do is create a GIS Viewer with a layer, representing the inventoried bicycle stations in the BicingBCN_Inventory ontology.
Steps:

Create a GIS layer from the BicingBCN_Inventory ontology. To do this, access the menu option Gis Tools > My Gis Layers and on the screen press the Create button:

Next, select Creation Ontology Layer:

In the following form, assign an identifier and description to the layer, and select the ontology to be represented (BicingBCN_Inventory). When selecting the ontology, the geometry fields of the ontology will automatically be suggested, so that you can select the one with you want to represent. In this case, the field is location:

Then, in the Symbology tab, select the default representation that your instances of the ontology will have on the viewer:

And in the Info Box field, select the attributes of the ontology to be represented in the pop-up that will open when clicking on each element.

Finally, click on New to create the layer, which will already appear in the initial list of layers.

Next, you must create the viewer with the layer. Select the menu option Gis Tools > My Gis Viewers and on the screen select the Create button:

In the form, specify the identifier and description of the viewer, select the technology (currently Cesium is available), the base layer of the viewer and the previously created layer. The viewer will be automatically created, with its source code, and it can be displayed on the screen itself:

Click New to create the viewer.
That way, it will appear in the list of available viewers:

And among other things, you can check the url in which the platform serves it. It can be checked from any browser:

Dashboard consuming the information

Next, you will create a Dashboard that will consume the information from the previous ontologies. It will integrate a GIS display with the inventory of stations, display a table with the status of the stations and allow selecting a station to make a comparison between the number of available mechanical and electric bicycles.

The final appearance of the dashboard will be similar to the following one:

Steps:

Create the dashboard: Select the menu option Display > My Dashboards and click Create:

Register the Dashboard in platform: In the following screen, you can simply assign an identifier and a description to the dashboard:

Edit Dashboard components: When creating the Dashboard, you will have an editor with a blank grid to start adding components:

Include Gadget with GIS viewer: The first Gadget that you will include will be a GIS viewer with the inventory of stations. To do this, press the button

that allows adding elements to the Dashboard grid. This will deplay, at the bottom of the screen, a wizard that allows you to add Gadgets to the dashboard:

As you can see, a map component already exists. Simply drag this component to the grid.
Then, a pop-up will open to ask whether you want to include an existing (previously created) Gadget or to create a new one. Choose New Gadget:

In the following form, assign an identifier to the Gadget and select the ontology where the geographical information is to be displayed:

Having done this, you can construct a query type statement that tells the Gadget the sentence it must launch to retrieve the information to be shown on the map, In this case, use select * from BicingBCN_Inventory as c limit 400:

By clicking Continue, you go to the next screen, where you will associate the fields recovered from the previous sentence to their representation in the viewer. Specifically, indicate the field that provides latitude and longitude, the identifier of each instance, and the properties that will be displayed in the popup of each element:

Press New to finish the creation of the Dashboard, and you can see it on the dashboard grid:

At this point, we strongly recommend you to save the work done by pressing

Include Gadget with table with the status of each station: For this Gadget, you will join, with a simple JOIN SQL, information from the BicingBCN_Status and BicingBCN_Inventory ontologies, since the BicingBCN_Status ontology does not have the name of the stations.

Besides, this gadget will refresh periodically, to reflect changes in statuses. Bear in mind that the flow that updates the BicingBCN_Status ontology is refreshed every 15 minutes.

To do all this, you need, before creating of the Gadget, to create a datasource that provides the information and its refresh to the Gadget.

To create the Datasource of the Gadget Table, access the platform menu Visualizations > My Datasources and press the Create button:

Next, assign an identifier to the Datasource, select an ontology, give it a refresh time of 15 minutes and associate the SQL statement that provides the information of the two tables:

select inv.BikeHireDockingStation.name as name, st.BicingBCN_Status.numBikesAvailable as numBikesAvailable, st.BicingBCN_Status.numBikesMechanical as numBikesMechanical, st.BicingBCN_Status.numBikesElectric as numBikesElectric, st.BicingBCN_Status.status as status from BicingBCN_Status as st JOIN BicingBCN_Inventory as inv ON st.BicingBCN_Status.station_id=inv.BikeHireDockingStation.id

Once the Datasource is created, return to the dashboard editor and add a new table type gadget:

Select New Gadget in the popup:

Associate an identifier to the Gadget and select the previously created Datasource (dsBicingBcnStatus):

Then, go down to the measures section and add the fields to be shown in the table, which correspond to those indicated in the statement and that you can map to a title for the table column. You will see dynamically how they are added to the table:

Finally, click New to create the gadget and it will appear on the grid of the Dashboard:

Include station selection combo: We will now see how to add a combobox that will allow you to select a station.

By examining the gadgets, you can see there is no combo type gadget, but this can be added as one of the Template types, which is a special type of Gadget that allows you to create reusable templates from HTML code.

For this Gadget, first create the datasource. In this case, you need the name of each station, which you will recover from the BicingBCN_Inventory ontology.

To create the Datasource of the Gadget Combo, access the platform menu Visualizations > My Datasources and press the Create button:

Next, assign an identifier to the Datasource, select an ontology and create the query statement that will feed the combo:

Once the Datasource is created, return to the dashboard editor and add a new Template-type gadget:

In the pop-up, select No to specify that you are going to create a new Template-type gadget:

This creates the empty gadget:

Proceed to edit it by selecting Edit:

On the next screen, select the previously created datasource (dsBicingBcnStations) and, in the HTML code, the following code, which iterates in the result of the datasource and shows the name of each station:

Press Compile & Synchronize and Close, and you will have the combo available on the Dashboard grid:

Include comparator of available bicycles: The next component to include is a bar chart, which, once you select a station in the combo, shows the available bicycles classified as mechanical and electrical in two bars.

Once more, start with the datasource of the gadget. In this case, you need to extract, from the BicingBCN_Status ontology, the number of available electric and mechanical bicycles, grouping them by the station identifier.
To create the Datasource of the Combo Gadget, access Visualizations > My Datasources in the platform menu and click on the Create button:

Next, assign an identifier to the Datasource, select an ontology and create the query statement that will feed the combo:

Once the Datasource is created, return to the dashboard editor and add a new gadget of the Bar Chart type:

In the pop-up, choose New Gadget, then assign an identifier to the Gadget and choose the previously created Datasource (dsBicingBCNAvailables):

Next, in the Measures section, specify the columns to display from the attributes retrieved from the Datasource query. Specifically, there will be two columns: Mechanical bicycles and Electric bicycles (X axis), both grouped by the id (Y axis):

Una vez creado el Gadget aparece en el grid del dashboard:

Assign Style and titles: The following point is to give each Gadget a correct title. To do this, in each gadget, choose the Styling option:

And change the title in the Gadget Title attribute:

In addition, in the bar chart gadget, you can see that it does not make much sense to show it if it was not previously filtered in the combo, so we will mark the Show Gadget only when it is filtered option:

Associate filtering in combo with comparison gadget: Finally, create the association that allows filtering the combo of stations, with the comparator of mechanical-electrical available in each one.

To do this, select to create a Datalink, by pressing the button:

A form opens in which you can establish the association between the Source Gadget and the Target Gadget, indicating the filtering attribute. In our case, the Source is the Gadget of the combo and the Target is the Gadget of bars, in both cases the filtering attribute is id:

We add by pressing

so that it appears between the existing connections:

We close and save the dashboard.

Test the Dashboard: Once the dashboard is finished, you can test it by accessing its url. To know your url, select in the menu Visualizations > My Dashboards, so that it appears in the table

And select the option

to know its url:

Query REST API

You will use the API Manager platform to create a REST API that allows to know which stations have bicycles available below a threshold that is passed by parameter. To do this, create an operation that queries using an SQL statement in the BicingBCN_Status ontology, which stations are below the threshold

Query Rest API

You will use the API Manager platform to create a REST API that allows to know which stations have bicycles available below a threshold passed by parameter. To do this, create an operation that queries using an SQL statement in the BicingBCN_Status ontology, which stations are below the threshold

Steps:
Access the API Manager from the menu of the Tools > My Apis platform and select Create:

In the form, specify the API data: Identification, category, description, meta-inf, and select that it is an Ontology as a REST API, and that the exposed ontology is BicingBCN_Status:

Next, indicate that you are going to create an operation of type Custom Query:

And in the form that opens, complete the operation identifier, the parameterized SQL statement, the data type of the parameter and the description of the operation:

When saving, you will return to the main screen and you will have the new operation:

If you save the API, it will be created and the newly created operation can be invoked. To do that:
Retrieve the user's token: From the menu option, Tools > My Apis and selecting the User Tokens tab:

Test the API: From the Tools > My Apis menu option, find the API in the list and select the SWAGGER option:

On the next screen, make sure that the scheme is HTTPS:

Deploy the operation and test it with Try it out:

Insert the previously recovered token, the parameter by which you want to filter, and execute: