SYNTHESIS_SPARQL_QUERY_ONTOP

### ADAPTED FOR ONTOP 


PREFIX : <http://opendata.inra.fr/anaeeOnto#> 
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> 
PREFIX oboe-core: <http://ecoinformatics.org/oboe/oboe.1.2/oboe-core.owl#> 
PREFIX oboe-standard: <http://ecoinformatics.org/oboe/oboe.1.2/oboe-standards.owl#>
PREFIX oboe-temporal: <http://ecoinformatics.org/oboe/oboe.1.2/oboe-temporal.owl#>
PREFIX oboe-characteristics: <http://ecoinformatics.org/oboe/oboe.1.2/oboe-characteristics.owl#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>


CONSTRUCT { 

      ?idVariableSynthesis    a                     :Variable           .
      ?idVariableSynthesis   :ofVariable            ?variable           .
      ?idVariableSynthesis   :hasCategory           ?category           .
      ?variable              :hasAnaeeVariableName  ?anaeeVariableName  .
      ?idVariableSynthesis   :hasLocalVariableName  ?localVariableName  .
      ?idVariableSynthesis   :hasUnit               ?unit              .
      ?unit                  :hasAnaeeUnitName      ?anaeeUnitName      .
      ?category              :hasCategoryName       ?categoryName       .
      ?idVariableSynthesis   :hasSite               ?site               .
      ?site                  :hasLocalSiteName      ?localSiteName      . 
      ?site                  :hasAnaeeSiteName      ?anaeeSiteName      .           
      ?site                  :hasSiteType           ?ecoType            .
      ?site                  :hasSiteTypeName       ?ecoTypeName        .
      ?site                  :hasNetwork            ?network            .
      ?network               :hasNetworkName        ?networkName        .
      ?idVariableSynthesis   :hasNbData             ?nbData             .
      ?idVariableSynthesis   :hasYear               ?year               .
      ?idVariableSynthesis   :hasISName             ?ISName             .
      ?idVariableSynthesis   :hasGraphClassName     ?graphClassName     .
}

 WHERE {
      
   SELECT ?network
          ?networkName
          ?idVariableSynthesis
          ?ISName
          ?graphClassName
          ?site  
          ?anaeeSiteName
          ?localSiteName	
          ?ecoType
          ?ecoTypeName
          ?category
          ?categoryName 
          ?variable 
          ?anaeeVariableName
          ?localVariableName
          ?unit
          ?anaeeUnitName
          ?year 
          (COUNT(*) as ?nbData)
    
   WHERE  {
       
          # hint:Query hint:analytic "true" .
           
          # hint:Query hint:optimizer "Runtime" .
          # hint:Query hint:constructDistinctSPO "true" .
          # hint:Query hint:normalizeFilterExpressions "true" .
          # hint:Query hint:maxParallel 5 .
          # hint:Query hint:pipelinedHashJoin "true" .
        
        
           ?obs_var_1 a oboe-core:Observation ; 
                        oboe-core:ofEntity ?anyVariable ; 
                        oboe-core:hasMeasurement ?measu_2 ; 
                        :hasVariableContext ?obs_variable_4 ;
                        oboe-core:hasContext ?obs_timeInstant_50 ;
                        oboe-core:hasContext ?obs_site_60 .              

           ?measu_2 a oboe-core:Measurement ; 
                      oboe-core:usesStandard ?unit .
      
           OPTIONAL { ?unit rdfs:label ?_anaeeUnitName . 
            	FILTER (lang(?_anaeeUnitName) = "en") } .
            BIND ( IF (BOUND (?_anaeeUnitName), ?_anaeeUnitName, "NULL_UNIT_LABEL"@en  ) AS ?anaeeUnitName) .
             
           ?obs_variable_4 a oboe-core:Observation ; 
                             oboe-core:ofEntity :Variable ; 
                             oboe-core:hasMeasurement ?measu_5 ; 
                             oboe-core:hasMeasurement ?measu_6 ; 
                             oboe-core:hasContext ?obs_categ_7 ;
                             :informationSystemName ?ISName ;
                             :graphClassName ?graphClassName  .

           ?obs_categ_7 a oboe-core:Observation ; 
                          oboe-core:ofEntity :VariableCategory ; 
                          oboe-core:hasMeasurement ?measu_8 .  
             
           ?measu_8 a oboe-core:Measurement ; 
                      oboe-core:usesStandard :Anaee-franceVariableCategoryNamingStandard ; 
                      oboe-core:hasValue ?category .
                 
           OPTIONAL { ?category rdfs:label ?_categoryName .
                      FILTER (lang(?_categoryName ) = "en") } .
           BIND ( IF (BOUND (?_categoryName), ?_categoryName , "NULL_CATEGORY_LABEL_NAME"@en  ) AS ?categoryName ) .
          
             
           ?measu_5 a oboe-core:Measurement ; 
                      oboe-core:usesStandard :Text ; 
                      oboe-core:hasValue ?localVariableName .
       
           ?measu_6 a oboe-core:Measurement ; 
                      oboe-core:usesStandard :Anaee-franceVariableNamingStandard ; 
                      oboe-core:hasValue ?variable .
           
           OPTIONAL { ?variable rdfs:label ?_anaeeVariableName .
                      FILTER (lang(?_anaeeVariableName ) = "en") } .
           BIND ( IF (BOUND (?_anaeeVariableName), ?_anaeeVariableName, "NULL_anaeeVariableName"@en ) AS ?anaeeVariableName ) .

           ?obs_timeInstant_50 a oboe-core:Observation ; 
                                 oboe-core:ofEntity oboe-temporal:TimeInstant ; 
                                 oboe-core:hasMeasurement ?measu_51 .     

           ?measu_51 a oboe-core:Measurement ;
                       oboe-core:ofCharacteristic :Date ;
                       oboe-core:usesStandard :DateTime ;
                       oboe-core:hasValue ?date .

     OPTIONAL { 
           ?obs_site_60 a oboe-core:Observation ; 
                          oboe-core:ofEntity :ExperimentalSite ; 
                          oboe-core:hasMeasurement  ?measu_61 , ?measu_62 ;
                          oboe-core:hasContext ?obs_expNetwork_63 ;  
                          oboe-core:hasContext ?obs_ecoType_65  .

           ?measu_61 a oboe-core:Measurement ;
                       oboe-core:ofCharacteristic oboe-characteristics:Name ;
                       oboe-core:usesStandard :Text ;
                       oboe-core:hasValue ?localSiteName .

           ?measu_62 a oboe-core:Measurement ;
                       oboe-core:ofCharacteristic oboe-characteristics:Name ;
                       oboe-core:usesStandard :Anaee-franceExperimentalSiteNamingStandard ;
                       oboe-core:hasValue ?site .

            OPTIONAL { ?site rdfs:label ?_anaeeSiteName .
                       FILTER (lang( ?_anaeeSiteName ) = "en") } .
            BIND ( IF (BOUND (?_anaeeSiteName), ?_anaeeSiteName, "NULL_anaeeSiteName"@en ) AS ?anaeeSiteName) .

           ?obs_expNetwork_63  a oboe-core:Observation ;
                                 oboe-core:ofEntity :ExperimentalNetwork ; 
                                 oboe-core:hasMeasurement  ?measu_64 .

           ?measu_64 a oboe-core:Measurement ;
                       oboe-core:ofCharacteristic oboe-characteristics:Name ;
                       oboe-core:usesStandard :Anaee-franceExperimentalNetworkNamingStandard ;
                       oboe-core:hasValue ?network .

            OPTIONAL { ?network rdfs:label ?_networkName .
                       FILTER (lang(?_networkName ) = "en") } .
            BIND ( IF (BOUND (?_networkName), ?_networkName, "NULL_LABEL_INFRA"@en )  AS ?networkName ) .

           ?obs_ecoType_65 a oboe-core:Observation ; 
                             oboe-core:ofEntity :EcosystemType ;
                             oboe-core:hasMeasurement ?measu_66 .
                             
           ?measu_66 a oboe-core:Measurement ; 
                      oboe-core:usesStandard <http://opendata.inra.fr/anaeeThes/c2_114> ; 
                      oboe-core:hasValue ?ecoType .

           OPTIONAL { 
                    ?ecoType skos:prefLabel ?_ecoTypeName .
                    FILTER (lang(?_ecoTypeName ) = "en" ) 
		} .
           BIND ( IF (BOUND (?_ecoTypeName), ?_ecoTypeName, "NULL_LABEL_ECOTYPE"@en)  AS ?ecoTypeName ) .

         # FILTER ( NOT EXISTS { ?obs_ecoType_65 oboe-core:ofEntity :ExperimentalNetwork  }) .

          BIND(YEAR(?date) AS ?year) .   
    }
          BIND (URI( REPLACE ( CONCAT ("http://anaee-fr#foret/"  ,
                                       ?anaeeSiteName     , "_"  , 
                                       ?categoryName      , "_"  , 
                                       ?anaeeVariableName , "_"  ,
                                       str(?year) )              , 
                               " ", "_") ) AS ?idVariableSynthesis ) .          
  }

  GROUP BY ?network  ?networkName   ?idVariableSynthesis ?site              ?anaeeSiteName 
           ?localSiteName       ?ecoType             ?ecoTypeName       ?category 
           ?categoryName        ?variable            ?anaeeVariableName ?ISName     ?graphClassName
           ?localVariableName   ?unit                ?anaeeUnitName     ?year
}