Destination tutorial

This tutorial is an adaption of the Ontop destination tutorial (opens new window) to Ontopic Studio.

For Docker-compose installations only

This tutorial assumes that Ontopic Studio has been installed with Docker-compose. If you installed Ontopic Studio with another method, the hostname of the database is likely to change. Please note that Docker is needed to run the tutorial database.

Preparation

Install the tutorial database

The tutorial database is normally installed as part of the recommended procedure for Docker-Compose. It should be available with destination-tutorial-db as hostname.

If it is not the case:

1. Go to the studio git repository
2. Run the command docker compose tutorial/docker-compose-tutorial.yml up

Create a project

2. Connect to Ontopic Studio (by default http://localhost:8081 (opens new window)), login with your user account.
3. Create a project DestTutorial
4. Open the project
5. Go to the Settings page
6. Provide the credentials for connecting to the database
   • Database: PostgreSQL
   • Host: destination-tutorial-db
   • Database name: postgres
   • Username: postgres
   • Password: postgres2
7. Save the credentials and go to the Lenses page. You see there, on the left, the list of database tables and views.

Dataset

The dataset is composed of the following tables:

• source1.municipalities

The table source1.municipalities contains the ID (m_id), name (en, it, de), Italian statistical institute code (istat) , population, geospatial coordinates (latitude, longitude, altitude) and geometrical point of municipalities.

m_id	istat	name_en	name_it	name_de	population	latitude	longitude	altitude	geometrypoint
A7CA017FF0424503 827BCD0E552F4648	021069	Proves/Proveis	Proves	Proveis	266	46.4781	11.023	1420.0	POINT Z(11.023 46.4781 1420)
BB0043517A574986 83B2F997B7B68D5F	021065	Ponte Gardena/Waidbruck	Ponte Gardena	Waidbruck	203	46.598	11.5317	470.0	POINT Z(11.5317 46.598 470)
516EF5F9F7794997 B874828DBE157E6E	021036	Glorenza/Glurns	Glorenza	Glurns	894	46.6711	10.5565	907.0	POINT Z(10.5565 46.6711 907)

The column m_id is the primary key.

• source1.hospitality

The table source1.hospitality contains the ID (h_id), name (en ,it ,de), telephone number, email, type (kind), geospatial coordinates (latitude, longitude, altitude), geometrical points and the associated municipality ID of lodging businesses.

h_id	name_en	name_it	name_de	telephone	email	kind	latitude	longitude	altitude	category	geometrypoint	m_id
ACE81868364111D4 9F0000105AF76E96	Apartments Monica	Appartamenti Monica	Appartements Monica	+39 380 4243160	info@appartements-monica.com	BedBreakfast	46.9386	11.4444	1098.0	2suns	POINT Z(11.4444 46.9386 1098)	2B138D40992744BD BD38F56B73F45183
EFF0FACBA54C11D1 AD760020AFF92740	Residence Tuberis	Residence Tuberis	Residence Tuberis	+39 0474 678488	info@tubris.com	HotelPension	46.9191	11.9547	865.0	3stars	POINT Z(11.9547 46.9191 865)	6A5FF36917FA48D2 B1996B76C7AA8BC6
5F74DCC404AAA52B 318A12507A1F27F7	Camping Gisser Vitha Hotels	Camping Gisser Vitha Hotels	Camping Gisser Vitha Hotels	+39 0474 569605	reception@hotelgisser.it	Camping	46.807976	11.812105	778.0	3stars	POINT Z(11.812105 46.807976 778)	1E84922B82234EE6 82A341531E1D1925

The column kind is populated with the following values: Hotel, Hostel, Camping, BedBreakfast.

The column h_id is the primary key.

• source1.rooms

Similarly, the table source1.rooms contains the ID, name, number of units, maximal number of guests, multilingual descriptions and lodging business ID of accommodations (e.g. rooms and apartments).

r_id	name_en	name_it	name_de	room_units	type	guest_nb	description_de	description_it	h_id
E4B47698C5A67758 4245B6F4E13CCB69	Appartement "PANORMASUITE"	Apartment "Panoramasuite"	Appartamento "PANORMASUITE"	1	apartment	4	Natursuite mit Schlafempore...	Suite con area notte al piano superiore...	32001C4FAA1311D1 926F00805A150B0B
AF57632D700A11D3 962F00104B421FA8	"Crab apple"	"Zierapfel"	"Mela selvatica"	1	apartment	6	Wohnraum mit Kochnische...	Stanza di soggiorno con cucina...	E650C0C33DC111D2 9536004F56000ECA
65F7D5D3182D4300 A42E23D60F836F61	apartment Sella for 5-6 people	Ferienwohnung Sella für 5-6 Personen	Appartamento Sella per 5-6 persone	1	apartment	6	Ferienwohnung 5 Personen	Appartamento 5 persone	8DA75A1A0AE743B4 89948BA98ECA30A9

The column type is also populated with the following datatypes: Room, Appartement, Pitch, Youth.

The column r_id is the primary key.

• source2.hotels

The table source2.hotels has similar content to source1.hospitality, but with a different structure. It contains the ID, multilingual names (english, italian, german), type (htype), geospatial coordinates (lat, long, alt) rating value (cat), municipality ISTAT code (mun) and geometrical point (geom) of lodging businesses.

id	english	italian	german	htype	lat	long	alt	cat	mun	geom
001AE4C0FA0781A2C DD3750811DBDAEB	Apartment Haideblick	Appartamento Haideblick App.	Ferienwohnung Haideblick	1	46.766831	10.533657	1470.0	2suns	21027	POINT Z(10.533657 46.766831 1470)
0614E1C5699E11D7 82540020AF71A63E	House Rosi	Cafe/Casa Rosi	Cafe Ferienhaus Rosi	1	46.615587	10.699991	868.0	3suns	21042	POINT Z(10.699991 46.615587 868)
05287B29094E4B03 AD97A5B4B3E66345	Sonnenhof	Sonnenhof	Sonnenhof	3	46.706881	10.473704	1730.0	1flower	21046	POINT Z(10.473704 46.706881 1730)

The column htype is populated with magic numbers:

• 1 -> BedAndBreakfast
• 2 -> Hotel
• 3 -> Hostel
• 4 -> Campground

The column id is the primary key.

• source2.accommodation

The table source2.accommodation has similar content to source1.rooms, but with a different structure. An important difference is that it does not contain the number of units, which is implicitly equal to 1.

id	english_title	german_title	italian_title	acco_type	guest_nb	german_description	italian_description	hotel
73F2B6C02A6152C2 86BFF186D1572DBC	Apartment Zerminiger	Ferienwohnung Zerminiger	Appartamento Zerminiger	2	4	Mit einer Wohnfläche von 59 m² bietet...	Con una superficie abitabile di 59 mq...	0A99E8B00EBA5795 6959949D017055FB
7F90B92F9CAA2F65 3B0F4DAEF5476A67	Appartment for 2-6 persons	Ferienwohnung für 2-6 Personen	Appartamento per 2-6 persone	2	5	Großzügig ausgestatete geräumige Wohnung...	Ben aredate spazio appartamento...	25548AEDD4682E0D 809086AD1B28E6F2
D3B0D75E132711D2 91A60040055FA744	Family room	Doppelzimmer	Süd A mit Balkon & Talblick Camera famigliare	1	4	WC, Dusche, Balkon, Talblick	WC, doccia, balcone	F63F948FEE3E11D1 91A60040055FA744

The column acco_type is populated with magic numbers:

• 1 -> Room
• 2 -> Apartment
• 3 -> Pitch
• 4 -> Youth

The column id is the primary key.

Modelling

In this tutorial, the ontology is already provided. We will focus on the mapping and the lenses. Our goal is to reproduce a fragment of the following diagram (the rest will be given as an exercise).

Ontology

1. Go to the Ontology page
2. Download and unzip the archive containing the ontology files (opens new window) (available in the ontologies directory).
3. Import the file schema.owl
4. Import the file geosparql.owl

Lodging businesses in source 1

Lens

1. Go to the Lenses page
2. Among the Source Tables (on the left), select the schema source1 to filter the tables
3. Select the table source1.hospitality and click on Generate mirror lens
4. Click on lenses.source1.hospitality on the right to open the newly created lens.
5. Click on Constraints and observe that a unique constraint is defined on the h_id column, as well as foreign key on the m_id column.
6. The column geometrypoint is in a binary format and needs to be converted into the Well-Known Text format (opens new window). Click on the menu of the column geometrypoint and click on Edit.
7. At the bottom, select the transformation Geo To Well-Known Text and click on the button +. Click on Preview to preview the results. Then, click on Update column.

First class mapping entry

1. Go to the Mapping page and click on the lens lenses.source1.hospitality.
2. Let's map all the rows to a class. Click on the button C+ for creating a mapping entry.
3. We need first to specify the subject as a template. Make sure the option Template is selected. Open the template menu and click on Create new template. Ontopic Studio automatically proposes the template data:source1-hospitality/{h_id} out of the lens name and the unique constraint on the column h_id. Accept it by clicking on Create.
4. Let's now assign a class. Make sure Constant is selected for the class. In the drop-down menu below select the class schema:LodgingBusiness. Click and save and you are having your first mapping entry!

First data property mapping entry

5. Let's now map the name in English to the lodging business. Let's create a property mapping entry for the same subject by clicking on the button P+ on the right of the IRI template data:source1-hospitality/{h_id}.
6. You should see that subject section is already filled with the same values as before. If it is not the case, you probably clicked on the other P+ button at the top of the page. In that case, go back and click on the correct P+ button.
7. Select the property schema:name in the property drop-down menu.
8. For the object, we want to direct map the value from the column name_en. Select Literal and the type Column and then the name_en in the column drop-down menu.
9. Notice that the datatype field get automatically filled after selecting the column as xsd:string. This is because the column type is text and, according to R2RML (opens new window) character string types are mapped to that RDF datatype. However here we want to assign a language tag (en), so as to distinguish from the names in German and Italian. Select the value @en in the Datatype/Language drop-down menu and save the mapping entry.

First object property mapping entry

10. Let's now map the default geometry of the lodging business. As previously, click on the button P+ on the right of the IRI template data:source1-hospitality/{h_id}.
11. Select the property geo:defaultGeometry.
12. For object, you can see that the pre-selected kind value is not Literal as before but IRI/BNode. This is because the property geo:defaultGeometry is declared in the ontology as an object property, so it expects an IRI or a BNode as value. The type Template is also pre-selected because it is the most common mechanism for building an IRI as object. Let's keep it.
13. We need to provide an IRI template for geometry objects. Here we have no plan to share the same geometries between lodging businesses, so let's create one geometry object per lodging business, using the unique constraint of the lens (h_id). In the template drop-down menu, click on Create new template. This time we need to modify the default value suggested by the platform. Replace the text entry data:source1-hospitality/ by data:geo/source1-hospitality/ and click on Create. Save the mapping entry.

Geometry literal

14. Let's now map the geometry value to the new IRI template. Since the subject is not the same as in the previous mapping entries, click on the P+ button at the top the page.
15. For the subject, select the template data:geo/source1-hospitality/{h_id}.
16. For the property, select geo:asWKT.
17. For the object, keep Literal as kind and select the column geometrypoint. For its datatype, select geo:wktLiteral. Save the mapping entry.

Sub-classes of lodging businesses

18. We now want to map to sub-classes of schema:LodgingBusiness depending on the values found in the column h_type. Let's create a new mapping entry. Click on C+ on the right of the template data:source1-hospitality/{h_id}.
19. For the class, we don't want to assign the same value to each lodging business found in the lens, so we cannot select the type Constant. Select instead the type Dictionary.
20. For the column, select h_type.
21. Let's now fill the first line of the dictionary. On the left field, insert the string Camping as found in the h_type column of the lens. In the right field, select the class schema:Campground. Everytime the value Camping is found in the column h_type, the lodging business will be declared instance of the class schema:Campground.
22. Click on + for adding a new line. Map this time the value HotelPension to the class schema:Hotel.
23. Map the value BedBreakfast to schema:BedAndBreakfast. Save the mapping entry.

We have produced enough mapping entries for this lens for the moment. We leave the rest as a homework exercise. Please refer to the VKG diagram to see which properties are missing.

Snapshot and mapping export

1. Go on the Dashboard page and click on Snapshot. Technically speaking, it will create a Git commit with all the important files of the project.
2. Open the Repository page to see the following snapshotted/committed files:
   • lenses.json: definition of the lenses in the Ontop format.
   • mapping.ttl: R2RML mapping file defined over the lenses.
   • ontology.ttl: RDFS/OWL2QL ontology serialized in Turtle.
   • YOUR_PROJECT.ontopicprj: internal representation of your project. Can be imported in Ontopic Studio to override the mapping, lenses and the ontology.
3. Go back to the Ontopic Studio tab. Go the Mapping page. Click on Export mapping and then on Without lenses to download the mapping in R2RML that directly refers to tables in the source instead of the lenses. This file is interoperable with any R2RML processor.

Test

Let's test our mapping by deploying Ontop directly in Ontopic Studio and issue our first SPARQL query.

1. Go to the Query page. Click on Start at the top right.
2. Execute the following SPARQL query:

PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX schema: <http://schema.org/>
SELECT * WHERE {
  ?h a schema:Campground ;
    schema:name ?name .
  FILTER (lang(?name) = 'en')
  OPTIONAL {
    ?h geo:defaultGeometry / geo:asWKT ?geometry .
  }
}

3. Click on View SQL to see the generated SQL query.

Lodging businesses in source 2

1. Go to the Lenses page and create a mirror lens for the table source2.hotels. Open the newly created lens.
2. We can see it has similar kind of information but with a different column names and special values. Let's keep the lens as it is for the moment and start mapping it. For that, click at bottom right on 0 mapping entries to reach the mapping editing page for this lens.
3. Create a class mapping entry with a new IRI template to map all the rows of the lens to the class schema:LodgingBusiness.
4. Map the name in English of the lodging businesses.
5. Map the following values of the column htype:
   • 1 to schema:BedAndBreakfast
   • 2 to schema:Hotel
   • 4 to schema:Campground.
6. Snapshot the changes.
7. Go to the Query page and update the SPARQL endpoint.
8. Run the previous SPARQL and observe that it now returns more results. If you look at SQL query, you will see that is now contains a UNION.

Municipalities

1. Create a mirror lens from the table source1.municipalities. Open the newly created lens.
2. Click on Constraints and observe that this lens contains 2 unique constraints: one on m_id, one on istat. The first column is an internal identifier that is specific to source1. The second column is the official code of the municipality assigned by the Italian Institute of Statistics (Istat).
3. Go to the page about the lens lenses.source1.hospitality and notice it has a column m_id referring to the internal code of the municipality.
4. Go to the page about the lens lenses.source2.hotels and observe it has a column mun referring to the Istat code.
5. As we would to use the same municipality identifiers for the lodging businesses from the 2 sources, let's use the Istat code for in the IRI template of municipalities and not the internal one.
6. Go to the mapping page of the lens lenses.source1.municipalities. Click on C+ for creating a class mapping entry.
7. Click on Create IRI template. By default Ontopic Studio generates a template using the first unique constraint (m_id). Replace the binding value m_id by istat. Create the template.
8. schema.org does have the class for municipalities so let's create one. In the class drop-down menu, click on Create new class. Insert Municipality as class name and select schema:AdministrativeArea as parent. Click on Create. Save the mapping entry.
9. Map the English name of the municipality.
10. Snapshot the changes.

Prefix update

1. Go to the Dashboard page. Click on Manage project prefixes.
2. Change the value associated to voc to http://tutorial.example.org/voc#. Click on Save and reply Yes to the question. We now changed the IRI of voc:Municipality.
3. Snapshot the changes.

Linking lodging businesses to municipalities

Source 1

For creating a mapping entry linking lodging businesses from source 1 to their municipality, we need to obtain their local identifier columns (respectively h_id and istat) in the same lens. The lenses lenses.source1.hospitality and lenses.source1.municipalities only contain one of the two. Therefore we need to create a join lens to obtain the two columns at the same time.

1. Go the Lenses page. On the left, select the tables source1.hospitality and source1.municipalities and click on Create join lens. Insert the name source1.hospitality-municipalities-join.
2. For source1.hospitality, provide the prefix h_ and for source1.municipalities the prefix m_. All the columns coming from the first table will prefixed by h_ while the ones from the second table by m_.
3. Add an equality between h_m_id and m_m_id. Click on Create join lens.
4. Open lenses.source1.hospitality-municipalities-join and click on 0 mapping entries.
5. Click on P+ to create a property mapping entry.
6. Select data:source1-hospitality/{h_id} as template for the subject. For the binding of the placeholder h_id in the template, select the column h_h_id (it could not be pre-selected because there is no column h_id in this lens).
7. Select the property schema:containedInPlace.
8. Select IRI/BNode for the object, data:source1-municipalities/{istat} as template and the column m_istat as binding for the placeholder istat. Save the mapping entry.

Source 2

First, open the lens lenses.source2.hotels. Observe that the mun column has an integer datatype. However in lenses.source1.municipalities, the istat column is a string and starts with the character 0. So here we first have to modify the value before we can create IRIs for the municipalities that match the ones of lenses.source1.municipalities:

1. Click Create column.
2. As "Column name", put istat.
3. As "Input column", select mun.
4. Next to "Add a transformation", select "Concatenation" and then press Add.
5. In the new selection box, change "Input" to "Constant", and then put 0 as its value.
6. Press the + button below and select "Input" in the new selection box.
7. Press Save the new column. Your new istat column is now added.

After completing this process, we can now start mapping:

1. Go the mapping page of the lens lenses.source2.hotels.
2. Click on P+ on the right of data:source2-hotels/{id}. Select the property schema:containedInPlace.
3. Select IRI/BNode for the object. Select the mapping template data:source1-municipalities/{istat} created earlier. Save the mapping entry.
4. Snapshot the changes.
5. Update the SPARQL endpoint and run the following query:

PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX schema: <http://schema.org/>
PREFIX voc: <http://tutorial.example.org/voc#>
SELECT * WHERE {
  ?h a schema:Campground ;
    schema:name ?name ;
    schema:containedInPlace ?municipality .
  ?municipality a voc:Municipality ;
    schema:name ?municipalityName .
  FILTER (lang(?name) = 'en' && lang(?municipalityName) = 'en')
  OPTIONAL {
    ?h geo:defaultGeometry / geo:asWKT ?geometry .
  }
}

Optimization

We can further add some metadata to this lens to allow Ontop to perform more powerful optimizations through the newly added column.

For this, let's add a Foreign Key constraint to the lens, pointing to source1.municipalities:

1. Open the lens lenses.source2.hotels.
2. Press the button Constraints.
3. Press the + button under "Foreign Keys".
4. In the "Relation" box, select source1.municipalities.
5. Select istat as both the "From Column" and the "To Column".
6. Press Save.

We have now added the foreign key constraint. While this will not change the results obtained from any query, it will help Ontop to simplify the generated SQL query in complex cases.

Inverse property

Let's enrich the ontology by defining schema:containedInPlace as inversed property of schema:containsPlace.

1. Go to the Ontology page. In the Properties tab, select the property schema:containedInPlace. Since schema.org is not an OWL ontology, it does not classify its properties as data or object properties. Select Object as type for this property.
2. A new button appears: Inverse Property. Click on it and select schema:containsPlace as inverse property. Click on Save.
3. Go to the property schema:containsPlace. Declare it as an object property.
4. Snapshot the changes.
5. Try the following SPARQL query:

PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX schema: <http://schema.org/>
PREFIX voc: <http://tutorial.example.org/voc#>
SELECT * WHERE {
  ?h a schema:Campground ;
    schema:name ?name  .
  ?municipality a voc:Municipality ;
    schema:name ?municipalityName ;
    schema:containsPlace ?h .
  FILTER (lang(?name) = 'en' && lang(?municipalityName) = 'en')
  OPTIONAL {
    ?h geo:defaultGeometry / geo:asWKT ?geometry .
  }
}

6. Notice it returns the same results as before, thanks to the Ontop inference capabilities.

Search

1. Go the Search page. You can see on the right all the mapping entries created.
2. On the left, select the class schema:LodgingBusiness. You now see only entries where the subject is instance of schema:LodgingBusiness.
3. On the Templates tab, select the template data:source2-hotels/{id}. Results are further filtered and only mapping entries from lenses.source2.hotels.
4. Unselect the class schema:LodgingBusiness and observe that the mapping entries for the subclasses schema:Hotel etc. reappear.