INSPIRE Hack – Linked Data Generation Update

This is an update of the linked data publication (OLU, OTM, SPOI and others) team – Dimitri (CZ), Raitis (LV), Sam (PL) and Raul (PL).

The first task for this group was to convert to RDF form OLU and OTM datasets from a PostgreSQL relational database. Because of OLU dataset depends on objects from other datasets (Corine, Urban Atlas, Cadastral Parcels) it also practically meant to convert all those objects to RDF form as well.

For the transformation, first ontologies for each of these datasets were generated. The ontologies generated are (available from https://github.com/FOODIE-cloud/ontology ):

The next step was to carry out the transformation itself. For this process, we used the D2RQ Platform (http://d2rq.org/), which uses mapping files (in RDF) to execute the transformation.

As we are dealing with huge pan-European datasets, not all the data was converted. We have decided to convert the following data:

  • Full Hilucs classification
  • Corine and Urban Atlas data for EU agriculture related lands (hilucs_code<200) & for main cities in Czech Republic (centers of NUTS3 regions), Poland (agglomeration areas from Urban Atlas) and Spain (agglomeration areas from Urban Atlas)
  • OLU data for agriculture related lands (hilucs_code<200) in CZ, PL, ES & for main cities in Czech Republic (centers of NUTS3 regions), Poland (agglomeration areas from Urban Atlas) and Spain (agglomeration areas from Urban Atlas)
  • OTM data for CZ, ES, PL; but RoadLinks only for FunctionalRoadClassValue of type: (‘mainRoad’,’firstClass’,’secondClass’,’thirdClass’,’fourthClass’) (see http://opentransportmap.info/OSMtoOTM.html );

After the data was transformed, we loaded it to our triplestore in Virtuoso:

Additionally, we loaded the following datasets:

  • SPOI (provided by WRLS)
  • NUTS (open dataset)
  • Eurovoc (open dataset)
  • Emergel (provided by CTIC)

The current statistics (total # of triples in FOODIE triplestore:  701,286,392) are:

Dataset Name Graph in FOODIE endpoint Source Triples
OLU http://w3id.org/foodie/olu# Transformed from PostgreSQL 127,925,971
SPOI http://www.sdi4apps.eu/poi.rdf Provided by WRLS (also available in FOODIE endpoint) 381,393,555
NUTS http://nuts.geovocab.org/ Open Source (available in FOODIE endpoint) 316,238
OTM http://w3id.org/foodie/otm# Transformed from PostgreSQL 154,340,611

 

Dataset Name Graph in FOODIE endpoint Source Triples
Hilucs classification http://w3id.org/foodie/hilucs# Transformed from PostgreSQL 397
Urban Atlas http://w3id.org/foodie/atlas# Transformed from PostgreSQL 19,606,025
Corine http://w3id.org/foodie/corine# Transformed from PostgreSQL 16,777,533
Eurovoc http://foodie-cloud.org/eurovoc Open Source (available in FOODIE endpoint) 425,667
Emergel http://foodie-cloud.org/emergel CTIC (available in FOODIE endpoint) 256,239

Regarding the links, the datasets were generated already with some links. For instance, OLU has links to Urban, Corine and Czech cadaster. SPOI has links to different datasets including dbpedia and others

Hence the next step was to think about possible queries that could show the RDF data and their connections.

Regarding the links, the datasets were generated already with some links. For instance, OLU has links to Urban, Corine and Czech cadaster. SPOI has links to different datasets including dbpedia and others

Hence the next step was to think about possible queries that could show the RDF data and their connections.

Description Query
get info of POI instances in given polygon

 

PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
PREFIX virtrdf:    <http://www.openlinksw.com/schemas/virtrdf#>
PREFIX poi: <http://www.openvoc.eu/poi#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
SELECT *
FROM <http://www.sdi4apps.eu/poi.rdf>
WHERE {
  ?Resource rdfs:label ?Label .
  ?Resource poi:class ?POI_Class .
  ?Resource geo:asWKT ?Coordinates .
  FILTER(bif:st_intersects (?Coordinates, bif:st_geomFromText("POLYGON
  ((6.11553983198 54.438016608357, 6.95050076948 47.230985358357, 13.36651639448
   47.626493170857, 14.99249295698 54.701688483357, 6.11553983198 54.438016608357)
   )"))) .
}
get spois for given OLU
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
PREFIX virtrdf:    <http://www.openlinksw.com/schemas/virtrdf#>
PREFIX poi: <http://www.openvoc.eu/poi#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX olu-instance: <http://w3id.org/foodie/olu/LandUse/22165431>

SELECT *
FROM <http://www.sdi4apps.eu/poi.rdf>
WHERE {
  ?Resource rdfs:label ?Label .
  ?Resource poi:class ?POI_Class .
  ?Resource geo:asWKT ?Coordinates .
  FILTER(bif:st_intersects (?Coordinates, bif:st_geomFromText(?coordinates))) .
  {
      SELECT bif:st_astext(?x) as ?coordinates
      FROM <http://w3id.org/foodie/olu#>
      WHERE {
             olu-instance: geo:hasGeometry ?geometry.
             ?geometry geo:asWKT ?x
      }
  }
}
get number of SPOIs for given OLU
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
PREFIX virtrdf:    <http://www.openlinksw.com/schemas/virtrdf#>
PREFIX poi: <http://www.openvoc.eu/poi#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX olu-instance: <http://w3id.org/foodie/olu/LandUse/22165431>
SELECT count (*)
FROM <http://www.sdi4apps.eu/poi.rdf>
WHERE {
  ?Resource rdfs:label ?Label .
  ?Resource geo:asWKT ?Coordinates .
  FILTER(bif:st_intersects (?Coordinates, bif:st_geomFromText(?coordinates))) .
  {
      SELECT bif:st_astext(?x) as ?coordinates
      FROM <http://w3id.org/foodie/olu#>
      WHERE {
          olu-instance: geo:hasGeometry ?geometry.
          ?geometry geo:asWKT ?x
      }
  }
}
get OLU ids for given polygon
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
PREFIX virtrdf:    <http://www.openlinksw.com/schemas/virtrdf#>
PREFIX poi: <http://www.openvoc.eu/poi#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?olu
FROM <http://w3id.org/foodie/olu#>
WHERE {
  ?olu a <http://w3id.org/foodie/olu#LandUse> .
  ?olu geo:hasGeometry ?geometry .
  ?geometry geo:asWKT ?x .
  FILTER(bif:st_intersects (?x, bif:st_geomFromText("POLYGON((15.88623046875
  50.66817794949724,17.05078125 50.66817794949724,17.05078125
  49.74711237803693,15.88623046875 49.74711237803693,15.88623046875
  50.66817794949724))"))) .
}
POIs of type hotel near the road of type first-class in a particular polygon
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
PREFIX virtrdf:    <http://www.openlinksw.com/schemas/virtrdf#>
PREFIX poi: <http://www.openvoc.eu/poi#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX otm: <http://w3id.org/foodie/otm#>

SELECT DISTINCT ?Resource, ?Label, bif:st_astext(?coordinatesPOIa) as ?coordinatesPOI
FROM <http://www.sdi4apps.eu/poi.rdf>
WHERE {
  ?Resource rdfs:label ?Label .
  ?Resource poi:class <http://gis.zcu.cz/SPOI/Ontology#lodging> .
  ?Resource geo:asWKT ?coordinatesPOIa .
  FILTER(bif:st_within(?coordinatesPOIa,bif:st_geomFromText(?coordinatesOTM),0.00045)) .
  {
    SELECT bif:st_astext(?x) as ?coordinatesOTM
    FROM <http://w3id.org/foodie/otm#>
    WHERE {
      ?roadlink a otm:RoadLink .
      ?roadlink otm:roadName ?name.
      ?roadlink otm:functionalRoadClass ?class.
      ?roadlink otm:centerLineGeometry ?geometry .
      ?geometry geo:asWKT ?x .
      FILTER(bif:st_intersects (?x, bif:st_geomFromText("POLYGON((14.426647
      50.0751251,14.426647 50.07685089,14.43054696 50.07685089,14.43054696
      50.0751251,14.426647 50.0751251))"))) .
      FILTER(STRSTARTS(STR(?class),"firstClass") ) .
    }
  }
}
 

Show me all the land parcels (OLU) that have hotels (SPOI) and that lie not more than 50 meters away from the major highway (OTM)?

 

(the following query returns land parcels objects that satisfy above mentioned criteria in part of Prague city center around IP Pavlova metro station.)

 

PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
PREFIX virtrdf:    <http://www.openlinksw.com/schemas/virtrdf#>
PREFIX poi: <http://www.openvoc.eu/poi#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX otm: <http://w3id.org/foodie/otm#>
PREFIX olu: <http://w3id.org/foodie/olu#>

SELECT DISTINCT ?olu ?hilucs ?source ?municode ?specificLandUse
FROM <http://w3id.org/foodie/olu#>

WHERE {
  ?olu a olu:LandUse .
  ?olu geo:hasGeometry ?geometry .
  ?olu olu:hilucsLandUse ?hilucs .
  ?olu olu:geometrySource    ?source .
          OPTIONAL {?olu olu:municipalCode      ?municode} .
          OPTIONAL {?olu olu:specificLandUse    ?specificLandUse} . 
  ?geometry geo:asWKT ?coordinatesOLU .
  FILTER(bif:st_within(bif:st_geomFromText(?coordinatesPOI),?coordinatesOLU)).
  {
    SELECT DISTINCT ?Resource, ?Label, bif:st_astext(?coordinatesPOIa) as ?coordinatesPOI
    FROM <http://www.sdi4apps.eu/poi.rdf>
    WHERE {
       ?Resource rdfs:label ?Label .
       ?Resource poi:class <http://gis.zcu.cz/SPOI/Ontology#lodging> .
       ?Resource geo:asWKT ?coordinatesPOIa .
       FILTER(bif:st_within(?coordinatesPOIa,bif:st_geomFromText(?coordinatesOTM),0.00045)) .
       {
          SELECT bif:st_astext(?x) as ?coordinatesOTM
          FROM <http://w3id.org/foodie/otm#>
          WHERE {
             ?roadlink a otm:RoadLink .
             ?roadlink otm:roadName ?name.
             ?roadlink otm:functionalRoadClass ?class.
             ?roadlink otm:centerLineGeometry ?geometry .
             ?geometry geo:asWKT ?x .
             FILTER(bif:st_intersects (?x, bif:st_geomFromText("POLYGON((14.426647
             50.0751251,14.426647 50.07685089,14.43054696 50.07685089,14.43054696
             50.0751251,14.426647 50.0751251))"))) .
             FILTER(STRSTARTS(STR(?class),"firstClass") ) .
         }
       }
    }
  }
}

Finally, the final task is to visualize the interlinked data on the map. For instance, here is a map, that shows the OLU polygons colored by the number of SPOI that lie inside them:
http://ng.hslayers.org/examples/olu_spoi/?hs_panel=info&hs_x=1607799.902082933&hs_y=6462976.717926565&hs_z=16&visible_layers=Base%20layer;Land%20use%20parcels

When any OLU polygon is clicked – the information about the polygon and all the SPOI points that lie inside is displayed in a pop-up window.  The pop-up window is browsable i.e. when some object in it is clicked it expands and one can see information about that clicked object.